Post reply

Seperating quarter years

  • September 23, 2005 at 9:55 am

    #91324

    SELECT TYPE,vendor,discontinueddate, datepart(quarter,[discontinueddate]) AS 'Quarter'

    from dbo.EOL

    WHERE DISCONTINUEDDATE > DateAdd(M,-24,GETDATE()) and DISCONTINUEDDATE < DATEADD(D, 1, DATEDIFF(D, 0, GetDate()))

    Order by Discontinueddate

    Works good but I need to seperate Quarters with Years. Sorting all quarters in 2005 / 2006 etc. 

     

    Thanks

     

    Mike

  • September 23, 2005 at 10:57 am

    #592667

    I think your DISCONTINUEDDATE < DATEADD(D, 1, DATEDIFF(D, 0, GetDate())) constriant is not correct.  Basically it gives you tomorrow, hence, 2006 records will not be included. 

    I threw this together for testing. 

    DECLARE @EOL TABLE( Type char(3), 

                                    Vendor varchar(25), 

                                    DiscountDueDate smalldatetime)

    INSERT INTO @EOL

    SELECT 'A', 'VendorA', '01/01/2005' UNION ALL

    SELECT 'A', 'VendorA', '07/15/2005' UNION ALL

    SELECT 'A', 'VendorA', '12/30/2005' UNION ALL

    SELECT 'A', 'VendorA', '02/01/2006' UNION ALL

    SELECT 'A', 'VendorA', '08/01/2006' UNION ALL

    SELECT 'A', 'VendorA', '11/15/2006'

    SELECT Type, Vendor, DiscountDueDate, DATEPART( quarter, DiscountDueDate) AS 'Quarter'

    FROM @EOL

    WHERE DiscountDueDate > DATEADD( m, -24, GETDATE())

    --  AND DiscountDueDate < DATEADD( d, 1, DATEDIFF( d, 0, GETDATE())) -- this is 09/24/2005 so it will not capture 2006 records...

      AND DiscountDueDate < DATEADD( m, 24, GETDATE()) -- this is 09/24/2005 so it will not capture 2006 records...

    ORDER BY DiscountDueDate

    I wasn't born stupid - I had to study.

  • September 23, 2005 at 11:31 am

    #592680

    Why not add DATEPART(YEAR, DiscoDate) AS 'YrDiscod'

    And then ORDER BY YrDiscod, [Quarter]?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 23, 2005 at 11:42 am

    #592691

    That will work too.  But since he already displaying DiscoDate, it seems to me to six of one, half a dozen of the other...

    I wasn't born stupid - I had to study.

  • September 23, 2005 at 12:32 pm

    #592717

    SELECT Type, Vendor, discontinueddate, DATEPART( YEAR, discontinueddate) AS 'Quarter Year',datepart(quarter,[discontinueddate]) AS 'Quarter'

    FROM EOL  

    WHERE DISCONTINUEDDATE > DateAdd(M,-0,GETDATE())

    Order by Discontinueddate

     

    Seems to work ok here.

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

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