|
parthhetal
Starting Member
Australia
2 Posts |
 Posted - 03/13/2013 : 17:47:16
|
Hi folks,
i have following select query with where clause which should return current date and current year total for MTD and YTD
SELECT
OES.YR as YEAR
, SUM(OES.SALESAMTF) OVER (PARTITION BY OES.YR) AS YTDTotalSales
, DATENAME(month, DATEADD(month, OES.PERIOD, 0) - 1) AS MONTH
, SUM(OES.SALESAMTF) OVER (PARTITION BY OES.PERIOD) AS MTDTotalsales
, OES1.OE_IDCUST
, OES1.OE_statsid
FROM SAMINC.dbo.OESTATS OES
FULL OUTER JOIN dbo.OESTATS1 OES1 ON OES.LINVCUST = OES1.OE_IDCUST COLLATE Latin1_General_BIN
where OES.YR = YEAR(getdate()) AND OES.PERIOD = MONTH(getdate())
which returns me rows below:
YEAR YTDTotalSales MONTH MTDTotalsales
2013 1512.500 January 575.000
2013 1512.500 March 937.500
the above is fine - what i want to achieve is only the second row from above that current YTD sales and current month to date sales
YEAR YTDTotalSales MONTH MTDTotalsales
2013 1512.500 March 937.500
please advise.
|
|
|
UnemployedInOz
Yak Posting Veteran
Australia
52 Posts |
Posted - 03/13/2013 : 20:37:45
|
Some how the OES.Period in DATENAME(month, DATEADD(month, OES.PERIOD, 0) - 1)
is a 1 which cannot be if OES.PERIOD = MONTH(getdate()) in the WHERE.
I suggest you add OES.Period as an output in your select and have a look to see if it is actually returning a 3 or 1 and take it from there. |
 |
|
Returning correct tsql results for MTD and YTD
