Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

YrMo Sql

Posted on 2013-11-19
3
Medium Priority
?
366 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 66

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

670 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