September 26, 2013 at 7:52 am
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
September 26, 2013 at 8:15 am
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.
September 26, 2013 at 9:17 am
that was the query I came up with. but I am stuck on the CTE part.
September 26, 2013 at 9:25 am
ok great. Will read it and come up with the answer.
September 30, 2013 at 4:26 am
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
September 30, 2013 at 11:18 am
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;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply