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.
| Author |
Topic |
|
mk77
Starting Member
4 Posts |
 Posted - 2014-09-02 : 11:48:01
|
| Hi All,i need to use case statement to produce many month records group by pid creating issue with dt field[date datatype] as the query below select pid, case when ( table3.Dt between '2009/01/01' and '2009/01/31') then count(pid) end as jan, case when ( table3.Dt between '2009/02/01' and '2009/01/29') then count(pid) end as feb, case when ( table3.Dt between '2009/03/01' and '2009/01/31') then count(pid) end as march from table3 group by pidit force me to add Dt field at group by where it's produce wrong result. i have to group by only by Pid.how to i group by only with Pid using this case statment,looking for your valuable reply.it's been very urgent ..Regards,mk77 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-09-02 : 11:55:53
|
| [code]select pid, sum(case when table3.Dt >= '20090101' and table3.dt < '20090201' then 1 else 0 end) end as jan, sum(case when table3.Dt >= '20090201' and table3.dt < '20090301' then 1 else 0 end) end as feb, sum(case when table3.Dt >= '20090301' and table3.dt < '20090401' then 1 else 0 end) end as mar from table3 group by pid[/code] |
 |
|
|
mk77
Starting Member
4 Posts |
Posted - 2014-09-02 : 12:35:13
|
| Hi Scott,Yes. i have found before see your anwser as slimier to your valuable reply.select pid, count(case when table3.Dt >= '20090101' and table3.dt < '20090201' then 1 else 0 end) end as jan, count(case when table3.Dt >= '20090201' and table3.dt < '20090301' then 1 else 0 end) end as feb, count(case when table3.Dt >= '20090301' and table3.dt < '20090401' then 1 else 0 end) end as mar from table3 group by pidAnyhow thanks a lot for your reply and support. |
|
|
|
|
|
|
|
|
Case statement with Group BY