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