Author |
Topic |
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-01 : 02:29:08
|
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 projectInstead 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) > 1Could you please help me modify this query Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-01 : 03:56:49
|
sounds like this...HAVING SUM(CASE WHEN CurrencyID = 1 THEN F.Amount END) > 10 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-01 : 04:01:04
|
don't quite understand what you really wantquote: 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[spoiler]Time is always against us[/spoiler] |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-01 : 17:28:08
|
Hi Visakh,When I put this condition HAVING SUM(CASE WHEN CurrencyID = 1 THEN Amount END) > 10I 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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-01 : 20:29:20
|
can you please post some sample data and expected result ? KH[spoiler]Time is always against us[/spoiler] |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-01 : 23:59:20
|
Here is the sample data, Date ProjectID CurrencyID Amount12/1/2013 21234 1 (USD) 512/1/2013 21234 2 491.2012/1/2013 21234 3 3.1150112/1/2013 21234 4 304.97712/1/2013 21234 5 30.629412/1/2013 21234 6 6.1912/1/2013 21234 7 12.0912/1/2013 35634 1 (USD) 2512/1/2013 35634 2 1500.9012/1/2013 35634 3 190.8712/1/2013 35634 4 290012/1/2013 35634 5 30.629412/1/2013 35634 6 6.1912/1/2013 35634 7 12.09Here is what I want12/1/2013 35634 1 (USD) 2512/1/2013 35634 2 1500.9012/1/2013 35634 3 190.8712/1/2013 35634 4 290012/1/2013 35634 5 30.629412/1/2013 35634 6 6.1912/1/2013 35634 7 12.09I need the projects whose CurrencyID = 1 is greater than 10, for those projects I want data for all the currenciesThanks, |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-02 : 00:12:58
|
[code]select *from yourtable twhere exists ( select * from yourtable x where x.ProjectID = t. ProjectID and x.CurrencyID = 1 and x.Amount > 10 )[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-02 : 00:59:53
|
Hi, Thanks for your reply but using this query. I am missing record for currencyid=6. I am getting only these records12/1/2013 35634 1 (USD) 2512/1/2013 35634 2 1500.9012/1/2013 35634 3 190.8712/1/2013 35634 4 290012/1/2013 35634 5 30.629412/1/2013 35634 7 12.09 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-02 : 01:42:20
|
can you post your query ? KH[spoiler]Time is always against us[/spoiler] |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-02 : 02:23:36
|
I initially run this query, SELECT getdate() as RecordingDate ,D.ProjectID,F.CurrencyID,SUM(F. Amount) as Amount INTO MytableFROM factTransaction FLEFT JOIN DimAccount DON D.CompanyID = F.CompanyID AND D.accountIndex = F.AccountIndexWHERE CAST(D.AccountSegment5 as INT) > 1 AND F.Calyear = 2013AND F.AccountNumber Like '%1989%'GROUP by D.ProjectID,F.CurrencyID, F.AccountNumberHAVING SUM(F.Amount) > 0After this I want to select all the projects who USD Amount(CurrencyID = 1) is greater than 10. For this purpose, I am using your queryselect *from Mytable twhere exists ( select * from Mytable x where x.ProjectID = t. ProjectID and x.CurrencyID = 1 and x.Amount > 10 ) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-02 : 02:55:11
|
check that the CurrencyID = 6 is in the result of your first query KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-02 : 02:57:22
|
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 twhere exists( select * from Mytable x where x.ProjectID = t.ProjectID and x.CurrencyID = 1 and x.Amount > 10) KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-02 : 04:38:15
|
quote: Originally posted by agastyamahi Here is the sample data, Date ProjectID CurrencyID Amount12/1/2013 21234 1 (USD) 512/1/2013 21234 2 491.2012/1/2013 21234 3 3.1150112/1/2013 21234 4 304.97712/1/2013 21234 5 30.629412/1/2013 21234 6 6.1912/1/2013 21234 7 12.0912/1/2013 35634 1 (USD) 2512/1/2013 35634 2 1500.9012/1/2013 35634 3 190.8712/1/2013 35634 4 290012/1/2013 35634 5 30.629412/1/2013 35634 6 6.1912/1/2013 35634 7 12.09Here is what I want12/1/2013 35634 1 (USD) 2512/1/2013 35634 2 1500.9012/1/2013 35634 3 190.8712/1/2013 35634 4 290012/1/2013 35634 5 30.629412/1/2013 35634 6 6.1912/1/2013 35634 7 12.09I need the projects whose CurrencyID = 1 is greater than 10, for those projects I want data for all the currenciesThanks,
SELECT Date, ProjectID, CurrencyID, AmountFROM(SELECT SUM(CASE WHEN CurrencyID = 1 AND Amount < 10 THEN 1 ELSE 0 END) OVER(PARTITION BY Date, ProjectID) AS Cnt,*FROM Table)tWHERE cnt = 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-02 : 13:52:08
|
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[spoiler]Time is always against us[/spoiler]
|
|
|
|