View to show Month to date

Transact-SQL
1

Hi There

I'm trying to come up with a query for some work I have to do for a month to date view.

We have a base table that lists all date varialbes, like full date, month parts, day parts etc. and that is where most of the date and time views source from.

For this view I am trying to out put this data set below as an example:

Month Key      Month      Month Key MTD     Month
---------      -----     --------------     -----
100            Apr-17    100                Apr-17
101            May-17    100                Apr-17
101            May-17    101                May-17
102            Jun-17    100                Apr-17
102            Jun-17    101                May-17
102            Jun-17    102                Jun-17

.......etc.

Is it possible to create this using just a view ?

Thanks

G

2

Problem I perceive with that is that it won't sort chonologically

We store "Month" as a Date (or DateTime even) "Rounded" to 1st-of-the-month, so we can sort on that and get everything for 01-Nov-2017 followed by everything for 01-Dec-2017

If want to display "Nov-2017" and "Dec-2017", on the user report, we format the date to just show Month/Year. We do this as close to the user as possible - i.e. if we can do that in the Reporting Tool that means that the report, also, receives the "month" as a full date, "01-Nov-2017", and then any local column-sort etc. can still work chronologically, the bit that does the "presentation for the user" displays it as "Nov-2017"

3

Sorry for misunderstanding that was just an example of date not what I was actually going to use.

4

I don't understand what those are then if not "2017-11-30", "November", "30th"

If you want to "group" by month then round the date to "months" (in effect "the first of the month") or "years", "quarters", whatever, and group on that. Then in the presentation layer display that value as appropriate - e.g. display "2017-01-01" as "Jan-2017" or "2017Q1", or just "2017" if that is what you want, but I recommend that you retain a valid, complete, date during the processing phase - rather than converting it to Text String, i.e. a formatted representation of the date, and then trying to group on it - and leave the formatting to the Presentation layer