Months as columns - SQL Server Forums

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

Months as columns

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-14 : 04:57:58

Hi i need a sql stored procedure below, this returns 2 columns of data, what i now need is to display the months as columns

ie.
TotalQty - OL_St_Code - Jan - Feb - March

then each month column shows a TotalQty for that month, do i need a query of query? if so how would i go about this

select count(OL_Qty)as TotalQty, OL_St_Code
from dbo.X_TBL_ORDER_LINEWEB LW

LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID

WHERE MONTH(O_Date) Between '11' AND '12'
group by Month(O_Date), OL_St_Code

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-14 : 05:00:11

[code]select
OL_St_Code,
sum(case when MONTH(O_Date) = 1 then OL_Qty else 0 end) as Jan,
sum(case when MONTH(O_Date) = 2 then OL_Qty else 0 end) as Feb,
...
from dbo.X_TBL_ORDER_LINEWEB LW
LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID
WHERE MONTH(O_Date) Between '11' AND '12'
group OL_St_Code[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-14 : 07:19:26

Hi many thanks

just one thing i will this show a count of OL_Qty for the month?

sum(case when MONTH(O_Date) = 1 then OL_Qty else 0 end) as Jan,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 07:24:37

quote:
Originally posted by craigmacca

Hi many thanks

just one thing i will this show a count of OL_Qty for the month?

sum(case when MONTH(O_Date) = 1 then OL_Qty else 0 end) as Jan,

sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Jan,

Madhivanan

Failing to plan is Planning to fail

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-14 : 07:39:35

sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Jan,

then 1 ???

i need then count(OL_Qty) for that month

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:08:09

quote:
Originally posted by craigmacca

sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Jan,

then 1 ???

i need then count(OL_Qty) for that month

Yes. Run and see

Madhivanan

Failing to plan is Planning to fail

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-14 : 08:17:21

ok that works the same but i am getting wrong results,

i have a totalQty column which shows the total qty for each OL_St_Code, but the figures in the months are larger than my total??

select
LW.OL_St_Code, count(OL_Qty)as TotalQty,
sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Jan,
sum(case when MONTH(O_Date) = 2 then 2 else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 then 3 else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 then 4 else 0 end) as Apr,
sum(case when MONTH(O_Date) = 5 then 5 else 0 end) as May,
sum(case when MONTH(O_Date) = 6 then 6 else 0 end) as June,
sum(case when MONTH(O_Date) = 7 then 7 else 0 end) as July,
sum(case when MONTH(O_Date) = 8 then 8 else 0 end) as Aug,
sum(case when MONTH(O_Date) = 9 then 9 else 0 end) as Sept,
sum(case when MONTH(O_Date) = 10 then 10 else 0 end) as Oct,
sum(case when MONTH(O_Date) = 1 then 11 else 0 end) as Nov,
sum(case when MONTH(O_Date) = 12 then 12 else 0 end) as Dec

from dbo.X_TBL_ORDER_LINEWEB LW
LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID
WHERE MONTH(O_Date) Between '01' AND '12' and YEAR(OW.O_Date) = '2007'
GROUP BY LW.OL_St_Code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:21:00

[code]sum(case when MONTH(O_Date) = 2 then 2 1 else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 then 3 1 else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 then 4 1 else 0 end) as Apr,[/code]

[edit]sorry for the confusion[/edit]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:25:46

quote:

Originally posted by khtan

sum(case when MONTH(O_Date) = 2 1 then 2 else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 1 then 3 else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 1 then 4 else 0 end) as Apr,

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

I think you wrongly strike out

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-14 : 09:15:16

craigmacca -- look really carefully at the SUM(CASE ... ) expression. Does it make sense to you? If not, then ask specific questions about it. Don't use or try code that you don't understand. All it is doing is returning either 1 or 0, depending on the month() of the current row. Then, we add those up. Adding 1+1+1+ .. etc is the same as counting, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-14 : 09:40:45

ok i have tried

sum(case when MONTH(O_Date) = 1 then 9 else 0 end) as Sept,
sum(case when MONTH(O_Date) = 1 then 10 else 0 end) as Oct,
sum(case when MONTH(O_Date) = 1 then 11 else 0 end) as Nov,

but then only a value of 0 appears in the column

i need to display a count of the column OL_Qty for each month

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 09:49:55

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

sum(case when MONTH(O_Date) = 2 1 then 2 else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 1 then 3 else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 1 then 4 else 0 end) as Apr,

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

I think you wrongly strike out

Madhivanan

Failing to plan is Planning to fail

Yah you are absolutely correct

Edited my post.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 09:59:38

quote:
Originally posted by craigmacca

ok i have tried

sum(case when MONTH(O_Date) = 1 then 9 else 0 end) as Sept,
sum(case when MONTH(O_Date) = 1 then 10 else 0 end) as Oct,
sum(case when MONTH(O_Date) = 1 then 11 else 0 end) as Nov,

but then only a value of 0 appears in the column

i need to display a count of the column OL_Qty for each month

I am not sure if you follow the thread

See if you get what you wanted

select
LW.OL_St_Code, count(OL_Qty)as TotalQty,
sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Jan,
sum(case when MONTH(O_Date) = 2 then 1 else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 then 1 else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 then 1 else 0 end) as Apr,
sum(case when MONTH(O_Date) = 5 then 1 else 0 end) as May,
sum(case when MONTH(O_Date) = 6 then 1 else 0 end) as June,
sum(case when MONTH(O_Date) = 7 then 1 else 0 end) as July,
sum(case when MONTH(O_Date) = 8 then 1 else 0 end) as Aug,
sum(case when MONTH(O_Date) = 9 then 1 else 0 end) as Sept,
sum(case when MONTH(O_Date) = 10 then 1 else 0 end) as Oct,
sum(case when MONTH(O_Date) = 1 then 1 else 0 end) as Nov,
sum(case when MONTH(O_Date) = 12 then 1 else 0 end) as Dec
from dbo.X_TBL_ORDER_LINEWEB LW
LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID
WHERE MONTH(O_Date) Between '01' AND '12' and YEAR(OW.O_Date) = '2007'
GROUP BY LW.OL_St_Code

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 10:01:14

Also you where condition should be

WHERE O_date>='20070101' and O_date<'20080101'

Madhivanan

Failing to plan is Planning to fail

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-21 : 08:05:27

ok thanks for that, it works well.

now i just need to do this:

i currently get columns jan, feb, mar etc

what i need to do now is if the 2 parameters
@month1 = 8
@month2 = 11

then i only want to display columns aug, sept, oct, nov

not sure how i can do this?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-12-21 : 14:57:23

why has nobody pointed out that [superman narrator voice] "This is a job for CRYSTAL REPORTS!!!"

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-24 : 03:18:20

ok thanks i can not use crsytal reports, i need to do this in my code, any ideas how do do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-24 : 18:40:04

See madhivanan's suggestion posted 12/14/2007 : 09:59:38
and replace the where clause with the one madhivanan posted 12/14/2007 : 10:01:14

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-31 : 04:13:15

ok many thanks i have it working now!!

just need to alter one thing i currently have

sum(case when MONTH(O_Date) = 12 then 1 else 0 end) as Dec,

what i need is if O_Date = month then show a sum of the qty i have tryed the code below but get an aggregate function error, any ideas?

sum(case when MONTH(O_Date) = 12 then sum(LW.OL_Qty) else 0 end) as Dec

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:24:47

You dont need the sum inside
use like this:-

sum(case when MONTH(O_Date) = 12 then LW.OL_Qty else 0 end) as Dec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources