Date Range

Question

Post reply

Date Range

forum member

Ten Centuries

Points: 1290
More actions
January 30, 2010 at 12:53 am

#386208

Hi ,
I have start date and finish date range inputted as a parameter into the report,how can i extract each month start and finish date range from the given
For Example:
start: 1/10/2009
finish:31/12/2009
The above values are inputted by user for start finish
I need to extract each month start and finish in similar format
start: 1/10/2009, finish:31/10/2009
start: 1/11/2009,finish:30/11/2009
start: 1/12/2009,finish:30/12/2009
I am looking for sought of monthly trend to provide the data
Please suggest
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

declare @from datetime,@to datetime

set @from = '2009-01-10'

set @to = '2010-10-20'

SELECT STUFF(CONVERT(CHAR(11),DATEADD(mm, n-1, @from),100),4,3,'') AS MonthName ,

dateadd(mm, datediff(mm, 0, (DATEADD(mm, n-1, @from)) ), 0) startdate,

(DATEADD(MONTH, 1, (dateadd(mm, datediff(mm, 0, (DATEADD(mm, n-1, @from)) ), 0) )) - 1 )lastdate

FROM tally /*Ref Jeff article for this numbers table*/

WHERE n <= DATEDIFF(mm,@from,@to)+1

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

forum member (1/30/2010)
Hi ,
I have start date and finish date range inputted as a parameter into the report,how can i extract each month start and finish date range from the given
For Example:
start: 1/10/2009
finish:31/12/2009
The above values are inputted by user for start finish
I need to extract each month start and finish in similar format
start: 1/10/2009, finish:31/10/2009
start: 1/11/2009,finish:30/11/2009
start: 1/12/2009,finish:30/12/2009
I am looking for sought of monthly trend to provide the data
Please suggest
Thanks

Hi whoever you are. 🙂

Arun is correct... you can easily do this with a Tally table although most folks would recommend the construction of a "Calendar" table at this point which isn't a bad idea at all.

There's an article that explains what the Tally table is and how it works to replace certain types of loops at the following link...

http://www.sqlservercentral.com/articles/T-SQL/62867/

To get exactly what you ask for in your post, build a Tally table (and, hopefully, read the article so you know how it works) and then run this code...

DECLARE @pStartDate DATETIME,

@pEndDate DATETIME

SELECT @pStartDate = '2009-10-01',

@pEndDate = '2009-12-31'

SELECT DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N-1),0) AS MonthStart,

DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N ),0) -1 AS MonthEnd

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@pStartDate,@pEndDate)+1

I normally don't use the month end day because if times are in the data, you can easily miss the whole last day of each month. Here's what I normally do...

DECLARE @pStartDate DATETIME,

@pEndDate DATETIME

SELECT @pStartDate = '2009-10-01',

@pEndDate = '2009-12-31'

SELECT DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N-1),0) AS MonthStart,

DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N ),0) AS NextMonthStart

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@pStartDate,@pEndDate)+1

Then I use WHERE clauses that look something like this...

WHERE somedatecolumn >= MonthStart

AND somedatecolumn < NextMonthStart

I use that WHERE method even on tables that are supposedly "whole date only" just to make the queries "bullet proof" if the "whole date only" thing should ever change.

Either query above would make a dandy "inline table valued" User Defined Function. If the bit of cached IO to use the Tally table bothers you, you could always use the technique similar to what showed up in Itzik's good book... the following meets your original requirements with virtually no reads...

DECLARE @pStartDate DATETIME,

@pEndDate DATETIME

;

SELECT @pStartDate = '2009-10-01',

@pEndDate = '2009-12-31'

;

WITH

E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1), --10

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E8) --Add row numbers

SELECT DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N-1),0) AS MonthStart,

DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N ),0) -1 AS MonthEnd

FROM cteTally t

WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@pStartDate,@pEndDate)+1

Again, that could easily be turned into a high-speed inline table valued function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

forum member Ten Centuries Points: 1290 More actions · Answer 3

Hi Arun,Hi Jeff

Thank you very much for the reply.Replies are very helpful,learnt new things

Thanks once again

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.zhang Newbie Points: 9 More actions · Answer 5

Hi,

I have a further question for this: I need to display the date list ranged on the input dates.

For example, if input dates are 23/10/2009 and 29/2/2010

Then the list should be

Start 23/10/2009, finish 31/10/2009

Start 1/11/2009, finish 30/11/2009

Start 1/12/2009, finish 31/12/2009

Start 1/1/2010, finish 31/1/2010

Start 1/2/2010, finish 29/2/2010

If input dates are 23/10/2009 and 29/10/2009

Then the list should be just

Start 23/10/2009, finish 29/10/2009

Please any suggestion will be very much appreciated

Thanks in advance

John

arun.sas SSChampion Points: 11831 More actions · Answer 6

SELECT

(case when n = 1 then @pStartDate

else DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N-1),0)end) AS MonthStart,

(case when n= DATEDIFF(mm,@pStartDate,@pEndDate)+1 then @pEndDate

else DATEADD(mm,DATEDIFF(mm,0,@pStartDate) + (t.N ),0) -1 end)AS MonthEnd

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@pStartDate,@pEndDate)+1

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

There will be no such animal as 2010-02-29. 😉

Arun... you beat me to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arun.sas SSChampion Points: 11831 More actions · Answer 8

Hi Jeff,

You know about what the newbie’s doing wrong, even you ask such question.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Sorry Arun... language barrier thing is interfering a bit. I didn't ask a question so I'm not sure what you mean.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arun.sas SSChampion Points: 11831 More actions · Answer 10

Hi Jeff,

Nothing wrong, the praise from one’s percent mood, so reflect you’re in good disposition!!!

jonathan.gregg SSC Journeyman Points: 75 More actions · Answer 11

Hello all,

I'm really (I mean really) new to this so thanks for the info, I've learned a ton about Tally tables. However, I still don't understand how I get say a specific column of dates from a table into the Tally table?

As an example:

If I wanted to get the month begin/end from somecolumnname.tablename into the Tally table?? I guess I'm a little lost at where the Tally table gets it's data from initially

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

jonathan.gregg (4/12/2010)
Hello all,
I'm really (I mean really) new to this so thanks for the info, I've learned a ton about Tally tables. However, I still don't understand how I get say a specific column of dates from a table into the Tally table?
As an example:
If I wanted to get the month begin/end from somecolumnname.tablename into the Tally table?? I guess I'm a little lost at where the Tally table gets it's data from initially

Nothing get's put "into" the Tally Table. The Tally Table is just used to replace a counter/While Loop combination.

If you could provide a bit of an example for the above new problem, we can show you how to solve it. See the first link in my signature line below for how to correctly post data in a readily consumable format, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonathan.gregg SSC Journeyman Points: 75 More actions · Answer 13

I'm trying to pull data based on date criteria, right now it's each month.

I'm would like to pull monthly ranges from the BOOKDATE column (below). When I started to search for info on this about pulling date ranges I came across the Tally table posts, which (I thought) would be just right as I could store the data in this table and for reporting purposes be able to search a date range based on data collected in that table. This would also allow me to add columns to this table if the report needed to grow, but right now I just need the single column. I've tried this with another table I created and to join the tables for this purpose (but haven't gotten that to work) like this:

SELECT BOOKDATE

FROM HOST0120

LEFT JOIN Metrics

ON HOST0120.BOOKDATE=Metrics.BookDate

I keep getting "Ambiguous column name 'BookDate' "

I guess I'm back to using another table since it doesn't sound like the Tally table is what I'm after then. The table I'm trying to pull info into is:

CREATE TABLE [Metrics] (

[BookDate] datetime NULL)

The table I'm pulling from is below:

CREATE TABLE [HOST0120] (

[MTGKEY] int IDENTITY(1, 1) NOT NULL,

[ROOMKEY] int NULL,

[WAITINGSETKEY] int NULL,

[CHAINLENGTH] smallint NULL,

[MTGDATE] datetime NULL,

[STARTMINS] smallint NULL,

[ENDMINS] smallint NULL,

[OWNERKEY] int NULL,

[AGENTKEY] int NULL,

[RECORDERKEY] int NULL,

[CLIENTCODE] char(32) NULL,

[COSTCODE] char(32) NULL,

[MARKUPRATE] float NULL,

[LAYOUT] char(16) NULL,

[ATTENDEES] smallint NULL,

[MTGSTATE] char(1) NOT NULL,

[MTGTYPE] char(1) NOT NULL,

[PRINTFLAG] bit NOT NULL,

[OFFICEFLAG] bit NOT NULL,

[FLAGA] bit NOT NULL,

[FLAGB] bit NOT NULL,

[FLAGC] bit NOT NULL,

[FLAGD] bit NOT NULL,

[FLAGE] bit NOT NULL,

[FLAGF] bit NOT NULL,

[FLAGG] bit NOT NULL,

[FLAGH] bit NOT NULL,

[BOOKDATE] datetime NULL,

[CHANGEDATE] datetime NULL,

[CANCELSTATE] char(1) NOT NULL,

[CANCELDATE] datetime NULL,

[CANCELMINS] smallint NULL,

[CANCELAGENTKEY] int NULL,

[CANCELRECORDERKEY] int NULL,

[BLOCKKEY] int NULL,

[ANNOTATION] char(200) NULL,

[SPARETEXT] char(80) NULL,

[CHANGEREC] int NULL,

[COSTCODEKEY] int NULL,

[CLIENTCODEKEY] int NULL,

[NOTES] text NULL,

[HIPKEY] int NULL,

[HIPTYPE] int NULL,

[LAYOUTKEY] int NULL)

ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY];

GO