Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 listing of months for a particular year Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, September 26, 2013 7:52 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:09 AM Points: 6, Visits: 27
 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 OrdersPoNum Date P001 2013-01-01 P002 2013-02-01 P003 2013-02-10P004 2013-03-01Table2: list items for each PoNumPoNum ItemRef QtyP001 I0001 10P001 I0002 5P002 I0003 15P003 I0003 20P004 I0003 5is it possible to have something like that?Year 2013Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec I000110 0 0 0 0 0 0 0 0 0 0 0I0025 0 0 0 0 0 0 0 0 0 0 0I0030 35 5 0 0 0 0 0 0 0 0 0Basically, 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
Post #1498857
 Posted Thursday, September 26, 2013 8:15 AM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 7:23 AM Points: 1,404, Visits: 3,170
 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 PJOIN Items I on I.PoNum = P.PoNumgroup by datepart(month,Date), itemRefput 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.
Post #1498876
 Posted Thursday, September 26, 2013 9:17 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:09 AM Points: 6, Visits: 27
 that was the query I came up with. but I am stuck on the CTE part.
Post #1498914
 Posted Thursday, September 26, 2013 9:22 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 9:16 PM Points: 1,890, Visits: 4,175
 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 ColumnsCross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1498919
 Posted Thursday, September 26, 2013 9:25 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:09 AM Points: 6, Visits: 27
Post #1498921
 Posted Monday, September 30, 2013 4:26 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:09 AM Points: 6, Visits: 27
 Hi guys,Finally came up with something. Please let me know if there is anything I can improveselect 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 decfrom (select Item, sum(qty) as sqty, left(DATENAME(MONTH,Store_PO.Date),3) as [Month] from Store_PO_Details join Store_PO on Store_PO.date = Store_PO_Details.date group by Item, left(DATENAME(MONTH,Store_PO.Date),3))as sPIVOT(sum(sqty)FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))as pivotTable;Thanks,Ashley
Post #1499919
 Posted Monday, September 30, 2013 11:18 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, November 20, 2013 2:50 PM Points: 31, Visits: 341
 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 PJOIN Two I on I.PoNum = P.PoNum) AS SourceTablePIVOT(SUM(Qty)FOR NewDate IN ( [Jan], [Feb], [Mar])) AS PivotTable;
Post #1500122

 Permissions