Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with a query

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 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

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-01 : 04:01:04
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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) > 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)

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]

Go to Top of Page

agastyamahi
Starting Member

33 Posts

Posted - 2013-12-01 : 23:59:20
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)

17689 Posts

Posted - 2013-12-02 : 00:12:58
[code]
select *
from yourtable t
where 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]

Go to Top of Page

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 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)

17689 Posts

Posted - 2013-12-02 : 01:42:20
can you post your query ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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 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)

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]

Go to Top of Page

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 t
where 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]

Go to Top of Page

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 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

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]



Go to Top of Page
   

- Advertisement -