SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/01/2013 :  02:29:08  Show Profile  Reply with Quote
I need help with this query. I have a table named FactTransaction where I am pulling the records from, I am pulling RecordingDate, ProjectID, CurrencyID(There are total 7 currencyids for different countries, CurrencyID = 1 means USD, 2 - INR so on..) and Amount. My aim is for each projectid, there are 7 different currencyids and I want amount to be displayed for all 7 different currencies.

Since I put having Sum(F.Amount) > 1 condition, I am not getting all the CurrencyIDs for each project.

Eg: If the USD Amount for a project is 0.5, the INR amount will be 31 and I am missing CurrencyID = 1 amount for that project and I am left with only 6 currency values for the project

Instead I want all the projects whose USD(CurrencyID = 1 to be greater than 10). This list should generate all the currencyids for each project


SELECT getdate() as RecordingDate
,D.ProjectID
,F.CurrencyID
,SUM(F. Amount) as Amount
FROM factTransaction F
LEFT JOIN DimAccount D
ON D.CompanyID = F.CompanyID
AND D.accountIndex = F.AccountIndex
WHERE CAST(D.AccountSegment5 as INT) > 1
AND F.Calyear = 2013
AND F.AccountNumber Like '%1989%'
GROUP by D.ProjectID
,F.CurrencyID
, F.AccountNumber
HAVING SUM(F.Amount) > 1

Could you please help me modify this query

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/01/2013 :  03:56:49  Show Profile  Reply with Quote
sounds like this


...
HAVING SUM(CASE WHEN CurrencyID = 1 THEN  F.Amount END) > 10


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/01/2013 :  04:01:04  Show Profile  Reply with Quote
don't quite understand what you really want

quote:
Since I put having Sum(F.Amount) > 1 condition, I am not getting all the CurrencyIDs for each project.

Eg: If the USD Amount for a project is 0.5, the INR amount will be 31 and I am missing CurrencyID = 1 amount for that project and I am left with only 6 currency values for the project

So what do you want here ?

quote:
Instead I want all the projects whose USD(CurrencyID = 1 to be greater than 10). This list should generate all the currencyids for each project

like this ?

HAVING SUM ( CASE WHEN CurrencyID = 1 THEN F.Amount END) > 10



KH
Time is always against us

Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/01/2013 :  17:28:08  Show Profile  Reply with Quote
Hi Visakh,

When I put this condition

HAVING SUM(CASE WHEN CurrencyID = 1 THEN Amount END) > 10

I am getting data only for CurrencyID = 1, I am not getting data for other currencyids. For each project, I want all the currencyids and amount associated with each currencyid whose US dollar(CurrencyID = 1) greater than 10.

Could you please help tweak the previous query
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/01/2013 :  20:29:20  Show Profile  Reply with Quote
can you please post some sample data and expected result ?


KH
Time is always against us

Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/01/2013 :  23:59:20  Show Profile  Reply with Quote
Here is the sample data,

Date ProjectID CurrencyID Amount
12/1/2013 21234 1 (USD) 5
12/1/2013 21234 2 491.20
12/1/2013 21234 3 3.11501
12/1/2013 21234 4 304.977
12/1/2013 21234 5 30.6294
12/1/2013 21234 6 6.19
12/1/2013 21234 7 12.09
12/1/2013 35634 1 (USD) 25
12/1/2013 35634 2 1500.90
12/1/2013 35634 3 190.87
12/1/2013 35634 4 2900
12/1/2013 35634 5 30.6294
12/1/2013 35634 6 6.19
12/1/2013 35634 7 12.09


Here is what I want

12/1/2013 35634 1 (USD) 25
12/1/2013 35634 2 1500.90
12/1/2013 35634 3 190.87
12/1/2013 35634 4 2900
12/1/2013 35634 5 30.6294
12/1/2013 35634 6 6.19
12/1/2013 35634 7 12.09

I need the projects whose CurrencyID = 1 is greater than 10, for those projects I want data for all the currencies

Thanks,
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/02/2013 :  00:12:58  Show Profile  Reply with Quote

select *
from   yourtable t
where  exists
       (
           select *
           from   yourtable x
           where  x.ProjectID  = t. ProjectID 
           and    x.CurrencyID = 1
           and    x.Amount     > 10
       )



KH
Time is always against us

Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/02/2013 :  00:59:53  Show Profile  Reply with Quote
Hi,

Thanks for your reply but using this query. I am missing record for currencyid=6. I am getting only these records

12/1/2013 35634 1 (USD) 25
12/1/2013 35634 2 1500.90
12/1/2013 35634 3 190.87
12/1/2013 35634 4 2900
12/1/2013 35634 5 30.6294
12/1/2013 35634 7 12.09
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/02/2013 :  01:42:20  Show Profile  Reply with Quote
can you post your query ?


KH
Time is always against us

Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/02/2013 :  02:23:36  Show Profile  Reply with Quote
I initially run this query,

SELECT getdate() as RecordingDate
,D.ProjectID
,F.CurrencyID
,SUM(F. Amount) as Amount
INTO Mytable
FROM factTransaction F
LEFT JOIN DimAccount D
ON D.CompanyID = F.CompanyID
AND D.accountIndex = F.AccountIndex
WHERE CAST(D.AccountSegment5 as INT) > 1
AND F.Calyear = 2013
AND F.AccountNumber Like '%1989%'
GROUP by D.ProjectID
,F.CurrencyID
, F.AccountNumber
HAVING SUM(F.Amount) > 0

After this I want to select all the projects who USD Amount(CurrencyID = 1) is greater than 10. For this purpose, I am using your query

select *
from Mytable t
where exists
(
select *
from Mytable x
where x.ProjectID = t. ProjectID
and x.CurrencyID = 1
and x.Amount > 10
)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/02/2013 :  02:55:11  Show Profile  Reply with Quote
check that the CurrencyID = 6 is in the result of your first query


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 12/02/2013 :  02:57:22  Show Profile  Reply with Quote
you don't need to store the result in the intermediate table, you can use CTE to achieve this

; with Mytable as
(
	SELECT 	getdate() as RecordingDate
		,D.ProjectID
		,F.CurrencyID
		,SUM(F. Amount) as Amount
	FROM 	factTransaction F
		LEFT JOIN DimAccount D	ON 	D.CompanyID 	= F.CompanyID
					AND 	D.accountIndex 	= F.AccountIndex
	WHERE 	CAST(D.AccountSegment5 as INT) > 1
	AND 	F.Calyear = 2013
	AND 	F.AccountNumber Like '%1989%'
	GROUP by D.ProjectID
		,F.CurrencyID
		, F.AccountNumber
	HAVING SUM(F.Amount) > 0
)
select 	*
from 	Mytable t
where 	exists
(
	select *
	from 	Mytable x
	where 	x.ProjectID 	= t.ProjectID
	and 	x.CurrencyID 	= 1
	and 	x.Amount 	> 10
)



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/02/2013 :  04:38:15  Show Profile  Reply with Quote
quote:
Originally posted by agastyamahi

Here is the sample data,

Date ProjectID CurrencyID Amount
12/1/2013 21234 1 (USD) 5
12/1/2013 21234 2 491.20
12/1/2013 21234 3 3.11501
12/1/2013 21234 4 304.977
12/1/2013 21234 5 30.6294
12/1/2013 21234 6 6.19
12/1/2013 21234 7 12.09
12/1/2013 35634 1 (USD) 25
12/1/2013 35634 2 1500.90
12/1/2013 35634 3 190.87
12/1/2013 35634 4 2900
12/1/2013 35634 5 30.6294
12/1/2013 35634 6 6.19
12/1/2013 35634 7 12.09


Here is what I want

12/1/2013 35634 1 (USD) 25
12/1/2013 35634 2 1500.90
12/1/2013 35634 3 190.87
12/1/2013 35634 4 2900
12/1/2013 35634 5 30.6294
12/1/2013 35634 6 6.19
12/1/2013 35634 7 12.09

I need the projects whose CurrencyID = 1 is greater than 10, for those projects I want data for all the currencies

Thanks,



SELECT Date, ProjectID, CurrencyID, Amount
FROM
(
SELECT SUM(CASE WHEN CurrencyID = 1 AND Amount < 10 THEN 1 ELSE 0 END) OVER(PARTITION BY  Date, ProjectID) AS Cnt,*
FROM Table
)t
WHERE cnt = 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 12/02/2013 :  13:52:08  Show Profile  Reply with Quote
Yes, currencyid = 6 is in the result of my first query

quote:
Originally posted by khtan

check that the CurrencyID = 6 is in the result of your first query


KH
Time is always against us



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000