Post reply

Need Hiarachy of date

  • 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

  • April 8, 2011 at 11:21 am

    #1309313

    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

  • April 16, 2011 at 9:42 pm

    #1312484

    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)

  • April 16, 2011 at 9:46 pm

    #1312485

    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)

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy