YEAR TO DATE DATA

Question

Post reply

YEAR TO DATE DATA

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

smer SSCommitted Points: 1684 More actions · Answer 1

Many thanks great work ....

The results displaying fiscal to month, please modify the code fiscal to date that will display only one row result.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

smer (5/18/2016)
Many thanks great work ....
The results displaying fiscal to month, please modify the code fiscal to date that will display only one row result.

I'm thinking that you should figure that out. A hint, look at the dateadd function used in the WHERE clause to determine the start of the fiscal year. That is the base of the changes you need in the GROUP By and ORDER BY, you just need to figure out what else needs to change.

Show us what you come up with.

smer SSCommitted Points: 1684 More actions · Answer 3

May be like that !

declare @ProcessDate datetime = '1/15/2015'; -- spaces added to allow code to be posted

select

--- dateadd(month, datediff(month, 0, mt.START_DATETIME), 0) as StartMonth,

dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, @ProcessDate)), 0)),

sum(mt.SALES) as TotalSales

FROM MY_TABLE mt

where

mt.START_DATETIME >= dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, @ProcessDate)), 0)) and

mt.START_DATETIME < dateadd(day, 1, @ProcessDate) -- changed to capture processed date, originally would exclude

group by

---dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)

dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, START_DATETIME)), 0))

order by

--- dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)

dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, START_DATETIME)), 0)) ;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

smer (5/18/2016)
May be like that !
declare @ProcessDate datetime = '1/15/2015'; -- spaces added to allow code to be posted
select
--- dateadd(month, datediff(month, 0, mt.START_DATETIME), 0) as StartMonth,
dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, @ProcessDate)), 0)),
sum(mt.SALES) as TotalSales
FROM MY_TABLE mt
where
mt.START_DATETIME >= dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, @ProcessDate)), 0)) and
mt.START_DATETIME < dateadd(day, 1, @ProcessDate) -- changed to capture processed date, originally would exclude
group by
---dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)
dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, START_DATETIME)), 0))
order by
--- dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)
dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, START_DATETIME)), 0)) ;

Looks like what I would do. Now, before you go any further, you also need to be able to explain what is happening in the dateadd functions. If you don't understand what they are doing, how will you support the code if there needs to be changes?

Take the time to work with them individually, from the inside out. See what is happening and why it works. One thing I will start with is the 0 value. This represents the 0 day or 1900-01-01.