Date range to also compute previous date per no. of days

Question

Date range to also compute previous date per no. of days

clint_pow

Say Hey Kid

Points: 690
More actions
October 4, 2008 at 2:15 am

#382941

Hi,
I want to get the date range example from 2/1/08 to 2/30/08 with corresponding data, I want also to get the datediff dd of 2/1/08 to 2/30/08
then from that datediff result no. of days. will compute also the value previous days which resulted to 1/1/08 to 1/30/08 with corresponding data.
Is this possible ? Because I want to get the beginning and ending balance.
Thank you very much and enlighten me.
Regards,
Clint

Viewing 15 posts - 1 through 15 (of 18 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

Hmm can you put more info here about your problem ...I'm sure if you post the table structure and some sample data we will find the solution for you ...be little bit clear about your problem here!

🙂

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

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

Heh... 2/30/... that's a good one.

Like Dugi said, it would be helpful if we had more information. If you want a really good answer, read and heed the article found at the link in my signature below.

Other than that, we could post a bunch of formulas for getting start and end dates for given months, but I think you're looking for a bit more.

--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)

Dugi SSCoach Points: 17998 More actions · Answer 3

Jeff Moden (10/4/2008)

...If you want a really good answer, read and heed the article found at the link in my signature below.

quote]

Yes Jeff your link in your sig really no need any comment ...!

Sorry for little chat here!

😎

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

clint_pow Say Hey Kid Points: 690 More actions · Answer 4

Hi Dugi,

Sorry for the short details. Here's to elaborate :

Assuming the date range is from 2/1/08 to 3/1/08

Date DocNum Price

2/1/08 123 1050

2/2/08 124 1151

2/3/08 125 1252

.

3/1/08 200 2001

The result must be :

Date Total Price

2/1/08 to 3/1/08 5454

I want also to compute the datediff(day) values between 2/1/08 to 3/1/08 (or depending on the date entered), so that it will become 1/1/08 to 2/1/08

Date DocNum Price

1/1/08 213 2134

1/2/08 214 2235

1/3/08 215 2336

.

2/1/08 300 3001

The result must be :

Date Total Price

1/1/08 to 2/1/08 9706

The final result must be :

Date Ending Price Date Beginning Price

2/1/08 to 3/1/08 5454 1/1/08 to 2/1/08 9706

Note :

The input date range may vary!

Hoping it will clarify all.

Thank you very much.

regards to all,

Clint

clint_pow Say Hey Kid Points: 690 More actions · Answer 5

Hi Jeff,

Sorry for the incomplete details. I have already posted a much clearer example hope that will be understand.

Thank you very much.

Regards,

Clint

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

clint_pow (10/5/2008)
Note :
The input date range may vary!

Will those ranges always be for whole months that start on the first of a month and end on the last of the month? And, for the example given, what inputs would be provided?

--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)

clint_pow Say Hey Kid Points: 690 More actions · Answer 7

Hi,

Sorry I make things complicated,

Its not allways the end of the month it depends on the date range being inputed example:

if date entered is 2/12/08 to 2/16/08 then the result beginning date will be:

2/08/09 from 2/12/08, will compute for the datediff of days between the two dates.

more...

3/15/08 to 3/20/08 the reuslt will be 3/10//08 to 3/15/08.

Thats what i mean vary in inputed dated range.

Please just comment if not well explained.

Thank you very much

Regards,

Clint

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

Ok... got it. Since you didn't post the data in an easy to consume format and I just can't bring myself to post untested code, you'll have to put up with my test data... 😀

--===== Create and populate a 1,000,000 row test table.

-- Column "Date" has a range of >=01/01/2000 and <01/01/2010 non-unique whole dates

-- Column "DocNum" has a range of 1 to 1,000,000 unique numbers

-- Column "Price" has a range of 1000 to 3000 non-unique numbers

-- Jeff Moden

SELECT Date,

ISNULL(ROW_NUMBER() OVER (ORDER BY Date),0) AS DocNum,

Price

INTO dbo.JBMTest

FROM (

SELECT TOP 1000000

Date = CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME),

Price = ABS(CHECKSUM(NEWID()))%2000+1000

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2)d --Lack of join criteria makes this a CROSS-JOIN

CREATE NONCLUSTERED INDEX [_dta_index_JBMTest_9_1010102639__K1_3] ON [dbo].[JBMTest]

(

[Date] ASC

)

INCLUDE ( [Price])

... and, here's the solution...

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @PrevStartDate DATETIME

DECLARE @PrevEndDate DATETIME

SELECT @StartDate = '20080212',

@EndDate = '20080216',

@PrevEndDate = @StartDate - 1,

@PrevStartDate = @StartDate - DATEDIFF(dd,@StartDate,@EndDate)

SELECT CONVERT(CHAR(8),@StartDate,1) + ' thru ' + CONVERT(CHAR(8),@EndDate,1) AS Date,

SUM(CASE WHEN Date BETWEEN @StartDate AND @EndDate THEN Price ELSE 0 END) AS EndingPrice,

CONVERT(CHAR(8),@PrevStartDate,1) + ' to ' + CONVERT(CHAR(8),@StartDate,1) AS Date,

SUM(CASE WHEN Date BETWEEN @PrevStartDate AND @PrevEndDate THEN Price ELSE 0 END) AS BeginningPrice

FROM dbo.JBMTest

WHERE Date BETWEEN @PrevStartDate AND @EndDate

We'll probably get a lecture from someone about how this type of formatting should be done in some GUI somewhere... 😛

--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)

clint_pow Say Hey Kid Points: 690 More actions · Answer 9

Hi,

Thank you very much for the fast reply, It seems I cant generate the sample table you gave it has an error.

Im new to this setup, Im purely an SQL guy is this T-SQL already?

Is this possible purely query only like basic commands?

thank you very much sorry for being a typical newbie!

Regards,

Clint

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

This forum is a bit odd... it sometimes turns left parenthesis into smiley faces. There was one in the test code generator. I've repaired that. Also, to successfully copy code with carriage returns intact, place your cursor just one line above the purple code box, then click and drag to one line below the code box. Then, do a copy and paste into SQL Server 2005.

And yes... because this is an SQL Server 2005 forum, I used ROW_NUMBER which is only available in 2005 and up. If you're using SQL Server 2000, I'll need to do something a bit different, but it's all T-SQL.

Lemme know if your really using 2005 or not and if you are, please post the error so I have a chance of figuring out what's wrong on your end.

--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)

clint_pow Say Hey Kid Points: 690 More actions · Answer 11

HI,

Yes im using SQL 2005, I dont have problem with code, my problem is the using Declare in the main query. I want just SELECT Statement in my query, is this possible? without using any T-SQL? just a simple SELECT statement to get all the data?

Thank you,

Clint

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

T-SQL isn't just "Declares"... it also includes all those really handy date functions. 🙂 And, yes... everything is possible...

SELECT CONVERT(CHAR(8),d1.StartDate,1) + ' thru ' + CONVERT(CHAR(8),d1.EndDate,1) AS Date,

SUM(CASE WHEN t.Date BETWEEN d1.StartDate AND d1.EndDate THEN t.Price ELSE 0 END) AS EndingPrice,

CONVERT(CHAR(8),d1.PrevStartDate,1) + ' to ' + CONVERT(CHAR(8),d1.StartDate,1) AS Date,

SUM(CASE WHEN t.Date BETWEEN d1.PrevStartDate AND d1.PrevEndDate THEN t.Price ELSE 0 END) AS BeginningPrice

FROM dbo.JBMTest t

INNER JOIN

(SELECT d.StartDate,

d.EndDate,

d.StartDate-1 AS PrevEndDate,

d.StartDate-DATEDIFF(dd,d.StartDate,d.EndDate) AS PrevStartDate

FROM (SELECT CAST('20080212' AS DATETIME) AS StartDate, CAST('20080216' AS DATETIME) AS EndDate)d)d1

ON t.Date BETWEEN d1.PrevStartDate AND d1.EndDate

GROUP BY d1.StartDate, d1.EndDate, d1.PrevStartDate, d1.PrevEndDate

... but whether it should be done in such a fashion remains to be seen and depends on how you're actually going to use the script.

--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)

clint_pow Say Hey Kid Points: 690 More actions · Answer 13

Hi,

Its really amazing how it goes, I really appreciated it at last I can incorporated it with some of my query. How I wish I have a knowledge of yours, it helps people like me.

Thank you very much.

Regards,

Clint

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 14

clint_pow (10/6/2008)
Hi,
Its really amazing how it goes, I really appreciated it at last I can incorporated it with some of my query. How I wish I have a knowledge of yours, it helps people like me.
Thank you very much.
Regards,
Clint

I'm humbled by your awesome compliment :blush:, but you give me too much credit. All I did was a simple substitution using the exact same query as before. The substitution is nothing more than using a "derived table" of constants instead of the declaration of variables.

Anyway, I'm glad I could help. Thanks for the opportunity.

--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)