none
Query to get Quarterly data RRS feed

  • Question

  • Hello Everyone,

    I need a help in getting quarterly data:

    If i declare a variable @month and @year and to this variables if I pass on @month='Q1' ,I should get data from 'January to march' for the respective year

    If I pass 'Q2' I should get April to June data for the respective year. And 'Q3'=July to September data for the respective year and 'Q4'=October to December data for the respective year.

    I have data in [dbo].[PATIENTS] table. Kindly please help me getting the data quarterly for respective Q1,Q2,Q3,Q4. Ask me for any questions, If I am not clear.

    Thanks,

    Sunny


    • Edited by sunny.sam Wednesday, December 6, 2017 11:26 PM
    Wednesday, December 6, 2017 11:24 PM

Answers

  •  declare @month varchar(10)
     declare @year int, @mno int
     set @month='Q4'
     set @year = 2017
     set @mno = case when @month='Q1' then 1
        when @month='Q2' then 2
        when @month='Q3' then 3
        when @month='Q4' then 4
        END

     select *  from [dbo].[PATIENTS]
     where DATEPART(QQ,<<datefield>>) = @mno and DATEPART(YYYY,<<datefield>>) = @year
    • Marked as answer by sunny.sam Thursday, December 7, 2017 4:07 AM
    Thursday, December 7, 2017 12:27 AM
  • Thanks Kumar for the reply. But when I am executing the stored procedure I am getting no data. 

    For example:

    EXEC [spGetQuartelyData] @month='Q1',@year='2015',@mno=1--and what is this @mno variable?

    If i give @month='Q1' or 'Q2'or 'Q3' or 'Q4' and the @year=2015 I should get the respective 3 months  data for the year.

    EXEC [spGetQuartelyData] @month='Q1',@year='2015'

    Hi sunny,

    According to Kumar's reply, you could rewrite the script of the stored procedure "[spGetQuartelyData]" like this.

    --Modify the script based on your original script of the stored procedure ALTER proc [spGetQuartelyData] @month varchar(10), @year int as begin declare @mno int set @mno = case when @month='Q1' then 1 when @month='Q2' then 2 when @month='Q3' then 3 when @month='Q4' then 4 END

    --other sql statements

    sql_statement [;] [ ... n ] select * from [dbo].[PATIENTS] where DATEPART(QQ,<<datefield>>) = @mno and DATEPART(YYYY,<<datefield>>) = @year end --Then try to execute the statement(The data type of the parameter @year is int ) --EXEC [spGetQuartelyData] @month='Q1',@year='2015' EXEC [spGetQuartelyData] @month='Q1',@year=2015

    If you still have questions about the procedure, please provide the script of the original procedure.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 2:32 AM
    Moderator

All replies

  •  declare @month varchar(10)
     declare @year int, @mno int
     set @month='Q4'
     set @year = 2017
     set @mno = case when @month='Q1' then 1
        when @month='Q2' then 2
        when @month='Q3' then 3
        when @month='Q4' then 4
        END

     select *  from [dbo].[PATIENTS]
     where DATEPART(QQ,<<datefield>>) = @mno and DATEPART(YYYY,<<datefield>>) = @year
    • Marked as answer by sunny.sam Thursday, December 7, 2017 4:07 AM
    Thursday, December 7, 2017 12:27 AM
  • Thanks Kumar for the reply. But when I am executing the stored procedure I am getting no data. 

    For example:

    EXEC [spGetQuartelyData] @month='Q1',@year='2015',@mno=1--and what is this @mno variable?

    If i give @month='Q1' or 'Q2'or 'Q3' or 'Q4' and the @year=2015 I should get the respective 3 months  data for the year.

    EXEC [spGetQuartelyData] @month='Q1',@year='2015'

    Thursday, December 7, 2017 12:44 AM
  • @mno is determined within the script, you don't need to set it 

    Have you confirmed there is data for that period based on the < <datefield> > column you're using within the script?

    • Marked as answer by sunny.sam Thursday, December 7, 2017 4:06 AM
    • Unmarked as answer by sunny.sam Thursday, December 7, 2017 4:07 AM
    Thursday, December 7, 2017 1:26 AM
  • Thanks Kumar for the reply. But when I am executing the stored procedure I am getting no data. 

    For example:

    EXEC [spGetQuartelyData] @month='Q1',@year='2015',@mno=1--and what is this @mno variable?

    If i give @month='Q1' or 'Q2'or 'Q3' or 'Q4' and the @year=2015 I should get the respective 3 months  data for the year.

    EXEC [spGetQuartelyData] @month='Q1',@year='2015'

    Hi sunny,

    According to Kumar's reply, you could rewrite the script of the stored procedure "[spGetQuartelyData]" like this.

    --Modify the script based on your original script of the stored procedure ALTER proc [spGetQuartelyData] @month varchar(10), @year int as begin declare @mno int set @mno = case when @month='Q1' then 1 when @month='Q2' then 2 when @month='Q3' then 3 when @month='Q4' then 4 END

    --other sql statements

    sql_statement [;] [ ... n ] select * from [dbo].[PATIENTS] where DATEPART(QQ,<<datefield>>) = @mno and DATEPART(YYYY,<<datefield>>) = @year end --Then try to execute the statement(The data type of the parameter @year is int ) --EXEC [spGetQuartelyData] @month='Q1',@year='2015' EXEC [spGetQuartelyData] @month='Q1',@year=2015

    If you still have questions about the procedure, please provide the script of the original procedure.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 2:32 AM
    Moderator
  • A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:  

    CREATE TABLE Report_Periods
    (report_name CHAR(10) NOT NULL PRIMARY KEY,
    report_start_date DATE NOT NULL,
    report_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (report_start_date <= report_end_date),
    ordinal_period INTEGER NOT NULL UNIQUE 
      CHECK(ordinal_period > 0)
    etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design report name column. For quartersm  you might use "[12][0-9][0-9][0-9]Q[1-4]" 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 7, 2017 3:18 AM
  • Thanks Kumar for the help it's working.
    Thursday, December 7, 2017 4:07 AM
  • Thanks Will Kong, thanks for the help.
    Thursday, December 7, 2017 4:08 AM