May 23, 2014 at 2:07 pm
I am trying to calculate data for 30 and 90 Days .
When I do a select SUM(Charges) Charges, Count(PName) Person from Prod
where Pname like 'Mike%'
and Days< 90
It gives me a count of 128
However , I need to do the same for 30 days so I am using a case where I say
Select PName
,Case when Days< = 30 Then SUM(Charges) End as Charges30
,Case when Days < = 30 Then Count(Persons) End as Persons30
,Case when Days< = 90 Then SUM(Charges) End as Charges90
,Case when Days< = 90 Then Count(Persons) End as Persons90
From Prod
Group By.....
But it doesn't work and gives me a count of 50 twice
May 23, 2014 at 2:26 pm
sharonsql2013 (5/23/2014)
I am trying to calculate data for 30 and 90 Days .When I do a select SUM(Charges) Charges, Count(PName) Person from Prod
where Pname like 'Mike%'
and Days< 90
It gives me a count of 128
However , I need to do the same for 30 days so I am using a case where I say
Select PName
,Case when Days< = 30 Then SUM(Charges) End as Charges30
,Case when Days < = 30 Then Count(Persons) End as Persons30
,Case when Days< = 90 Then SUM(Charges) End as Charges90
,Case when Days< = 90 Then Count(Persons) End as Persons90
From Prod
Group By.....
But it doesn't work and gives me a count of 50 twice
This would be so much easier if you could post ddl and sample data. This may get you what you need...untested of course.
SUM(Case when Days< = 30 Then Charges else 0 End) as Charges30
...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2014 at 2:42 pm
That helped. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply