Solved

YrMo Sql

Posted on 2013-11-19
3
347 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 42
Need help with a query 3 37
T-SQL Import data in Microsoft SQL Server Management Studio using INSERT 3 22
Change this SQL to get all nodes 3 37
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

763 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