listing of months for a particular year

Question

Post reply

listing of months for a particular year

ashley.shookhye

Mr or Mrs. 500

Points: 597
More actions
September 26, 2013 at 7:52 am

#283774

Dear All,
Hope you are doing fine. What am I trying to do is get a stock count for each item per month for a particular year. I am trying to do a report on that.
Table1: list all Purchase Orders
PoNum Date
P001 2013-01-01
P002 2013-02-01
P003 2013-02-10
P004 2013-03-01
Table2: list items for each PoNum
PoNum ItemRef Qty
P001 I0001 10
P001 I0002 5
P002 I0003 15
P003 I0003 20
P004 I0003 5
is it possible to have something like that?
Year 2013
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
I0001
10 0 0 0 0 0 0 0 0 0 0 0
I002
5 0 0 0 0 0 0 0 0 0 0 0
I003
0 35 5 0 0 0 0 0 0 0 0 0
Basically, I will need each item's qty per month for a year.
I came up with something. But I have the item's qty for the month the items are in only. How do I get it 0 for the other month?
I was reading on creating a CTE to list the months of the year. But I am stuck.
Please advise.
Thanks,
Ashley

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

The first thing you need to do is sum up the items - group by each month:

select mm=datepart(month,Date), itemRef, QTyTot=sum(Qty)

from Purchaseorders P

JOIN Items I on I.PoNum = P.PoNum

group by datepart(month,Date), itemRef

put results into a temp table or use a CTE. After that you can pivot the table across the each month which should be pretty easy.

This sounds like homework though so I leave the details to you.

The probability of survival is inversely proportional to the angle of arrival.

ashley.shookhye Mr or Mrs. 500 Points: 597 More actions · Answer 2

that was the query I came up with. but I am stuck on the CTE part.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

For a more complete answer, please take a look at the article linked on my signature.

For an explanation on the pivot query, check the following articles:

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ashley.shookhye Mr or Mrs. 500 Points: 597 More actions · Answer 4

ashley.shookhye

Mr or Mrs. 500

Points: 597

September 26, 2013 at 9:25 am

#1653629

ok great. Will read it and come up with the answer.

ashley.shookhye Mr or Mrs. 500 Points: 597 More actions · Answer 5

Hi guys,

Finally came up with something. Please let me know if there is anything I can improve

select pivotTable.Item, ISNULL(pivotTable.jan,0) as jan, ISNULL(pivotTable.feb,0) as feb, ISNULL(pivotTable.mar,0) as march,

ISNULL(pivotTable.apr,0) as april, ISNULL(pivotTable.may,0) as may,

ISNULL(pivotTable.jun,0) as jun, ISNULL(pivotTable.jul,0) as jul,

ISNULL(pivotTable.aug,0) as aug, ISNULL(pivotTable.sep,0) as sep,

ISNULL(pivotTable.oct,0) as oct, ISNULL(pivotTable.nov,0) as nov,

ISNULL(pivotTable.dec,0) as dec

from (

select Item, sum(qty) as sqty, left(DATENAME(MONTH,Store_PO.Date),3) as [Month]

from Store_PO_Detailsjoin Store_PO

on Store_PO.date = Store_PO_Details.date

group by Item, left(DATENAME(MONTH,Store_PO.Date),3))

as s

PIVOT

(

sum(sqty)

FOR [month] IN (jan, feb, mar, apr,

may, jun, jul, aug, sep, oct, nov, dec)

)as pivotTable;

Thanks,

Ashley

ccavaco Old Hand Points: 302 More actions · Answer 6

A Sraight PIVOT could work too!

CREATe TABLE One(PoNum varchar(7),

Date varchar (15))

INSERT INTo One (PoNum, Date)

VALUES('P001', '2013-01-01'),

('P002', '2013-02-01' ),

('P003', '2013-02-10'),

('P004', '2013-03-01')

CREATe TABLE Two(PoNum varchar(7),

IteMRef varchar (15),

Qty int)

INSERT INTo Two (PoNum, IteMRef, Qty)

VALUES('P001', 'I0001', '10'),

('P001', 'I0002', '5' ),

('P002', 'I0003', '15'),

('P003', 'I0003', '20'),

('P004', 'I0003', '5')

SELECT [Jan], [Feb], [Mar]

FROM

(select CASE datepart(month,P.Date) WHEN '1' THEN 'Jan' WHEN '2' THEN 'Feb' WHEN '3' THEN 'Mar' ELSE 'NA' END AS NewDate, I.Qty as Qty

from One P

JOIN Two I on I.PoNum = P.PoNum

) AS SourceTable

PIVOT

(

SUM(Qty)

FOR NewDate IN ( [Jan], [Feb], [Mar])

) AS PivotTable;