Solved

YrMo Sql

Posted on 2013-11-19
3
324 Views
Last Modified: 2013-11-19
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] I
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
0
Comment
Question by:Thomask23
  • 2
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39659508
>I have created the below query to capture the 1st day of the month
>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.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39659517
SELECT Distinct
    CAST(CONVERT(char(6), Archived, 112) AS int) AS 'ArchivedYrMo'
FROM [RCSC].[dbo].[IAQ_Archive] I
WHERE
    Archived >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND
    Archived < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39659523
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] I
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)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now