Date problem getting data for Jan 2010 - Data required
My query below is to produce date from Jan 2010 to Feb 2011, i can get feb 2010 to feb 2011 but cannot get the figures for Jan 2010. Can someone advise as to how to pick up that months data? thanks M Stoker My query below is to produce date from Jan 2010 to Feb 2011, i can get feb 2010 to feb 2011 but cannot get the figures for Jan 2010. Can someone advise as to how to pick up that months data? thanks SELECT --Customer Address.AddressCode --Sales Order quantities per month ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(GetDate())) AND (Year(SalesOrders.TransactionDate) = Year(GetDate())) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Feb2011' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-1,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Jan2011' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-2,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Dec2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-3,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Nov2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-4,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Oct2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-5,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Sep2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-6,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Aug2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-7,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Jul2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-8,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Jun2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-9,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'May2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-10,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Apr2010' ,SUM(CASE WHEN (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-11,GetDate()))) THEN SalesOrders.TotalNett ELSE 0 END) AS 'Mar2010' ,SUM(CASE WHEN (Year(SalesOrders.TransactionDate) = Year(DateAdd(Year,-1,GetDate()))) AND (Month(SalesOrders.TransactionDate) = Month(GetDate()))THEN SalesOrders.TotalNett ELSE 0 END) AS 'Feb2010' ,SUM(CASE WHEN ((Year(SalesOrders.TransactionDate) = Year(DateAdd(Year,-1,GetDate()))) AND (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-1,GetDate()))))THEN SalesOrders.TotalNett ELSE 0 END) AS 'Jan2010' FROM Address --Customer Address INNER JOIN TransactionHeader ON Address.Id = TransactionHeader.AddressIdForPrimary --Sales Orders LEFT OUTER JOIN (SELECT TransactionFigures.TransactionHeaderId ,TransactionFigures.TotalNett ,th.TransactionDate FROM TransactionFigures INNER JOIN TransactionHeader as th ON th.Id = TransactionFigures.TransactionHeaderId WHERE (th.TransactionDate > DateAdd(Year,-1,GetDate()) and th.TransactionDate > DateAdd(MONTH,-1,GetDate())) AND TransactionFigures.CategoryIdForCurrency = 62 AND th.WorkflowStageId IN (Select WorkflowGroupStage.WorkflowStageId FROM WorkflowGroupStage WHERE WorkflowGroupStage.WorkflowGroupId = 14) --[Forecasting] Invoice Sales Transactions ) AS SalesOrders ON SalesOrders.TransactionHeaderId = TransactionHeader.Id WHERE Address.AddressTypeId = 1 GROUP BY Address.AddressCode ORDER BY Address.AddressCode
February 9th, 2011 4:30pm

Hello, I stuck about your WHERE condition: WHERE (th.TransactionDate > DateAdd(Year,-1,GetDate()) and th.TransactionDate > DateAdd(MONTH,-1,GetDate())) Base on today 2011-02-11 the part DateAdd(Year,-1,GetDate()) returns 2010-02-11 and DateAdd(MONTH,-1,GetDate())) returns 2011-01-11 because you reduce only by one month and with the AND logic you get data at least for 2011-1-11; remove the second condition. Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich tglich http://olafhelper.over-blog.de
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2011 7:18pm

If you want to calculate 1/1/2010 based on the current date... This should do the trick... DateAdd(yy, DateDiff(yy, 0, GetDate())- 1, 0) Jason Long
February 11th, 2011 8:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics