Need Hiarachy of date

Question

Post reply

Need Hiarachy of date

latitiacasta

SSCommitted

Points: 1982
More actions
March 30, 2011 at 12:56 am

#389171

Hi All,
I need sql query for date hiarachy. for eg. when i pass @startdate and @enddate , @startday is my day1 then one week have 7 days so after that i need week2... one month have max 5 week so after that i need month 2
i need result set like
period closed fixed open .......
day1 1 1 5
day2
........
day7
week2
week3
.......
week5
month2
..........
i have table
CREATE TABLE [dbo].[DATEQUERY](
[DATE] [datetime] NULL,
[STATUS] [nvarchar](70) NULL
) ON [PRIMARY]
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Fixed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Reopen')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Rejected')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Reopen')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 1 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 2 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 2 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 2 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 2 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 5 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 5 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 9 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 9 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 9 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 9 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 10 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 10 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 10 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 10 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 10 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 13 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 13 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 14 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 16 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 17 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 17 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 20 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 23 2005 12:00AM','Closed')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Sep 23 2005 12:00AM','Open')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Oct 24 2005 12:00AM','New')
INSERT INTO datequery ([DATE],[STATUS]) VALUES ('Oct 24 2005 12:00AM','New')
Appreciate your help

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ysaias Portes-QUI Group SSCrazy Points: 2127 More actions · Answer 1

Hi,

You can simply create a loop that can iterate through all the different dates.

You can find more documentation on MS website. http://msdn.microsoft.com/en-us/library/ms178642.aspx

Please let me know if that works for you.

DECLARE @startdate DATE, @enddate DATE

WHILE @startdate < @enddate

BEGIN

INSERT INTO datequery ([DATE],[STATUS])

VALUES (@startdate, 'Status'

SET @startdate = DATEADD(d,1,@startdate)

END

DbDefence: Transparent Database Encryption and SQL Protection.

QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

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

latitiacasta (3/30/2011)
Hi All,
I need sql query for date hiarachy. for eg. when i pass @startdate and @enddate , @startday is my day1 then one week have 7 days so after that i need week2... one month have max 5 week so after that i need month 2

Are you all set or is this still a problem?

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 3

Ysaias Portes-QUI Group (4/8/2011)
Hi,
You can simply create a loop that can iterate through all the different dates.
You can find more documentation on MS website. http://msdn.microsoft.com/en-us/library/ms178642.aspx
Please let me know if that works for you.
DECLARE @startdate DATE, @enddate DATE
WHILE @startdate < @enddate
BEGIN
INSERT INTO datequery ([DATE],[STATUS])
VALUES (@startdate, 'Status'
SET @startdate = DATEADD(d,1,@startdate)
END

No... you just don't need a loop for this. Please see the following article.... it'll change your SQL life.

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

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