Thomask23
asked on
YrMo Sql
I have created the below query to capture the 1st day of the month for the data that i have archived.
sELECT Distinct CAST(CAST(YEAR(Archived) AS VARCHAR(4)) + RIGHT('0' + CAST(DATEPART(MM, Archived) AS VARCHAR(12)),2) AS INT) as 'ArchivedYrMo' FROM [RCSC].[dbo].[IAQ_Archive]
ORDER BY I.Archived DESC
ArchivedYrMo
201311
201310
201309
201308
201307
201306
However I also need to create a query pull the previous month. I would need the results to pull 201310 if i were running the query today and if i was to run the query in Jan the results would need to be 201312.
Any takers
sELECT Distinct CAST(CAST(YEAR(Archived) AS VARCHAR(4)) + RIGHT('0' + CAST(DATEPART(MM, Archived) AS VARCHAR(12)),2) AS INT) as 'ArchivedYrMo' FROM [RCSC].[dbo].[IAQ_Archive]
ORDER BY I.Archived DESC
ArchivedYrMo
201311
201310
201309
201308
201307
201306
However I also need to create a query pull the previous month. I would need the results to pull 201310 if i were running the query today and if i was to run the query in Jan the results would need to be 201312.
Any takers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to return the first of a month, the calc is:
DATEADD(MONTH, DATEDIFF(MONTH, 0, <date>, 0)
There is no I/O and no string conversion, so it's almost no overhead and lightening quick.
As an example, the last three months, grouped by month:
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0) AS ArchivedYrMo,
SUM(...),
COUNT(...)
FROM [RCSC].[dbo].[IAQ_Archive]
WHERE
Archived >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AND
Archived < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0)
DATEADD(MONTH, DATEDIFF(MONTH, 0, <date>, 0)
There is no I/O and no string conversion, so it's almost no overhead and lightening quick.
As an example, the last three months, grouped by month:
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0) AS ArchivedYrMo,
SUM(...),
COUNT(...)
FROM [RCSC].[dbo].[IAQ_Archive]
WHERE
Archived >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AND
Archived < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0)
>201311 .. 201310
The above returns year-months, and not days.
If it helps, I have an article out there titled Date Fun, Part One: Build your own SQL calendar table to perform complex date expressions that is a T-SQL demo on how to build your own calendar table, that can answer questions like 'what is the previous month' very easy.