How to show Quarters Dynamically in SQL

  • mandymvn

    Mr or Mrs. 500

    Points: 547

    Hi,

    At present am showing quarters from 2015-Q1 to Q4 , it should be like that, because report must show data for last 4 quarters only.

    Whenever i got data for "2016-Q1" then automatically report should change and display data for "2016-Q1" - "2015-Q4" - "2015-Q3" - "2015-Q2", it should be based on current quarter. same for upcoming quarters also.

    Is it possible??

    again this is for reporting purpose only... there is 4 sets of query and using UNION

    and also in the logic its hard coded as:

    '2015 01' AS Qtr from Table WHERE (YYYYMM = 201503)

    UNION

    '2015 02' AS Qtr from Table WHERE (YYYYMM = 201506)

    UNION

    '2015 03' AS Qtr from Table WHERE (YYYYMM = 201509)

    UNION

    '2015 04' AS Qtr from Table WHERE (YYYYMM = 201512).

    please let me know if anyone need more details.

  • pietlinden

    SSC Guru

    Points: 62715

    do it in your WHERE clause.

    WHERE MyTable.MyDate>DATEADD(yyyy,-1,GETDATE())

  • Jeff Moden

    SSC Guru

    Points: 995976

    mandymvn (3/3/2016)


    Hi,

    At present am showing quarters from 2015-Q1 to Q4 , it should be like that, because report must show data for last 4 quarters only.

    Whenever i got data for "2016-Q1" then automatically report should change and display data for "2016-Q1" - "2015-Q4" - "2015-Q3" - "2015-Q2", it should be based on current quarter. same for upcoming quarters also.

    Is it possible??

    again this is for reporting purpose only... there is 4 sets of query and using UNION

    and also in the logic its hard coded as:

    '2015 01' AS Qtr from Table WHERE (YYYYMM = 201503)

    UNION

    '2015 02' AS Qtr from Table WHERE (YYYYMM = 201506)

    UNION

    '2015 03' AS Qtr from Table WHERE (YYYYMM = 201509)

    UNION

    '2015 04' AS Qtr from Table WHERE (YYYYMM = 201512).

    please let me know if anyone need more details.

    Just making double sure... do you want the report headings to auto-magically change based on today's date (whatever it is)?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • mandymvn

    Mr or Mrs. 500

    Points: 547

    Hi

    pietlinden

    could you please elaborate a bit, I am not getting 🙁

  • mandymvn

    Mr or Mrs. 500

    Points: 547

    Hi Jeff,

    "do you want the report headings to auto-magically change based on today's date (whatever it is)?"

    is there a way to do it ? I am not sure If possible yeah pls give me some suggestions.

    Thanks

  • Jeff Moden

    SSC Guru

    Points: 995976

    mandymvn (3/3/2016)


    Hi Jeff,

    "do you want the report headings to auto-magically change based on today's date (whatever it is)?"

    is there a way to do it ? I am not sure If possible yeah pls give me some suggestions.

    Thanks

    Heh... of course there's a way to do it. 🙂

    First, we need some test data so that you can see that we're not bogging things down too by getting clever. This will create a million row test table in about 4 seconds (on my laptop, anyway). The details as to what the content and limits on each column are in the comments in the code. This is my de facto standard test table.

    -- DROP TABLE dbo.JBMTest

    ;

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

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

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2010 and <01/01/2020 non-unique date/times

    -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- Jeff Moden

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

    SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2010','2020')+CAST('2010' AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12)

    INTO dbo.JBMTest

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --===== Add the temporal Clustered Index for performance purposes.

    CREATE CLUSTERED INDEX IXC_JBMTest

    ON dbo.JBMTest (SomeDateTime)

    ;

    --===== Show the first 100 rows to see what the data looks like.

    SELECT TOP 100 * FROM dbo.JBMTest

    ;

    We could do an Indexed View but those have too many limitations so far as I'm concerned. So, here's a regular view that creates a pretty nifty vertically oriented report that automatically adjusts the reported quarters based on whatever the current date is. Again, the details are in the comments and there are some usage examples in the header of the code. There are also some references (the 2nd & 3rd "Ref:) that you should read to understand the GROUP BY WITH CUBE and the GROUPING() function.

    --DROP VIEW dbo.PreviousQuarters

    ;

    GO

    CREATE VIEW dbo.PreviousQuarters AS

    /**************************************************************************************************

    Purpose:

    Aggregate the last 3 quarters and the current quarter through all of today and produce a

    "vertical" report.

    It has all sorts of different sub-totals, looks kind of pretty, is nasty fast, and you never

    have to change a date because it figures out the quartes auto-magically.

    You can also do neat things like selecting just detail rows or just subtotal rows, etc.

    Examples (not all inclusive and sorts not guaranteed without an ORDER BY):

    --===== Produce the entire report

    SELECT *

    FROM dbo.PreviousQuarters

    ORDER BY SortOrder

    ;

    --===== Produce a report just for where SomeLetters 2 = 'JM' or 'QZ'

    -- and include a "Percent of Total" for each row where the "Total"

    -- is the total of SomeMoney between the two. Since subtotals are

    -- included in all that, we have to divide the total by 2.

    -- A "Percent of Grand Total" for all data in the quarters is included, as well.

    SELECT *

    ,PercentOfTotal = SomeMoney/((SUM(SomeMoney) OVER ())/2) * 100

    ,PercentofGrandTotal = SomeMoney*100.0/(SELECT SUM(SomeMoney) FROM dbo.PreviousQuarters)

    FROM dbo.PreviousQuarters

    WHERE SomeLetters2 IN ('JM','QZ')

    ORDER BY SortOrder

    ;

    --===== Produce a report just for quarter subtotals and the grand total

    SELECT *

    FROM dbo.PreviousQuarters

    WHERE RowDescription IN ('SubTotal Quarter','Grand Total')

    ORDER BY SortOrder

    ;

    --===== Produce a report just for SomeLetters2 subtotals and the grand total

    SELECT *

    FROM dbo.PreviousQuarters

    WHERE RowDescription IN ('SubTotal SomeLetters2','Grand Total')

    ORDER BY SortOrder

    ;

    Revision History:

    Rev 00 - 04 Mar 2016 - Jeff Moden

    - Initial creation and unit test

    - Ref: http://www.sqlservercentral.com/Forums/Topic1766578-3412-1.aspx#bm1766600

    - https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

    - https://technet.microsoft.com/en-us/library/ms178544(v=sql.105).aspx

    **************************************************************************************************/

    WITH cteQuarter AS

    (

    SELECT Qtr = DATENAME(yy,SomeDateTime)+' Q'+DATENAME(qq,SomeDateTime)

    ,SomeLetters2

    ,SomeMoney

    FROM dbo.JBMTest

    WHERE SomeDateTime >= DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) --Start of 3 quarters ago

    AND SomeDateTime < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0) --Through all of today

    )

    SELECT SortOrder =

    ROW_NUMBER() OVER (ORDER BY GROUPING(SomeLetters2), SomeLetters2

    ,GROUPING(Qtr), Qtr)

    ,RowDescription =

    CASE

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN 'Detail'

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN 'SubTotal SomeLetters2'

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN 'SubTotal Quarter'

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN 'Grand Total'

    ELSE 'ERROR'

    END

    ,Qtr =

    CASE

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN Qtr

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN ''

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN Qtr

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN ''

    ELSE 'ERROR'

    END

    ,SomeLetters2 =

    CASE

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN SomeLetters2

    WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN SomeLetters2

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN ''

    WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN ''

    ELSE 'ERROR'

    END

    ,SomeMoney = SUM(SomeMoney)

    FROM cteQuarter

    GROUP BY SomeLetters2, Qtr WITH CUBE

    ;

    If no one steps up, I'll show you how to do a dynamic CROSSTAB to make a "horizontal" report like most spreadsheet users would expect. Just not tonight. I've got to get some sleep.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • mandymvn

    Mr or Mrs. 500

    Points: 547

    Hi Jeff,

    used this in WHERE clause as:

    where Qtr >=DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) AND Qtr < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)

    i tried to convert but still getting the error

    and getting conversion error :Conversion failed when converting date and/or time from character string.

    i have a 'Qtr is a Varchar(10)' in a table

    any help ??

  • pietlinden

    SSC Guru

    Points: 62715

    DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0)

    ... returns a DATE value.

    '2015 01' is a STRING value, not a date. You would have to turn that into a date... maybe split off the year, and add 0 - 3 quarters to it.

  • Jeff Moden

    SSC Guru

    Points: 995976

    mandymvn (3/4/2016)


    Hi Jeff,

    used this in WHERE clause as:

    where Qtr >=DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) AND Qtr < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)

    i tried to convert but still getting the error

    and getting conversion error :Conversion failed when converting date and/or time from character string.

    i have a 'Qtr is a Varchar(10)' in a table

    any help ??

    Do you also have some sort of DATETIME column?

    Also, please see the article at the first link under "Helpful Links" in my signature line below before you post again. It'll help save a lot on this type of confusion in the future and you'll have a pat answer much more quickly. 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 9 posts - 1 through 9 (of 9 total)

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