Solved

YrMo Sql

Posted on 2013-11-19
3
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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:Scott Pletcher
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 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