none
Growth Factor on Year-to-Year, Month-to-Month Quarter-to-Quarter RRS feed

  • Question

  • Hi All,

    below is my data.

    Company_C C_YEAR C_Month C_Quarter
    6 2019 1 1
    14 2019 2 1
    13 2019 3 1
    8 2019 4 2
    22 2019 5 2
    5 2019 6 2
    12 2019 7 3
    12 2019 8 3
    3 2019 9 3
    12 2018 10 4
    20 2019 10 4
    14 2018 11 4
    40 2019 11 4
    15 2018 12 4


    struggling to get growth factor for year-year, Month-Month and Quarter-Quarter. 

    below is the query which i am not getting correct results, month is correct, but quarter and year is not correct.

    with xyz (Company_C,C_YEAR,C_Month,C_Quarter)
    as
    (
    select 
    count(Company_ID) Company_C, 
    datepart(Year,CREATED) C_YEAR,
    datepart(Month,CREATED) C_Month,
    datepart(Quarter,CREATED) C_Quarter
    FROM  GTP_COMPANY
    where datepart(Month,CREATED) is not null
    group by 
    datepart(Month,CREATED),
    datepart(Quarter,CREATED),
    datepart(Year,CREATED) 
    )
    select Company_C,C_YEAR, C_Quarter,C_Month,
    round(isnull(100*(Company_C - LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)) / (LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)),0),2) as MoM,
    isnull(100*(Company_C - LAG(Company_C, 4) over (ORDER BY C_YEAR ,C_Quarter)) / (LAG(Company_C, 4) over ( ORDER BY C_YEAR,C_Quarter)),0) as QoQ,
    isnull(100*(Company_C - LAG(Company_C, 12) over ( ORDER BY C_YEAR,C_Month)) / (LAG(Company_C, 12) over ( ORDER BY C_YEAR,C_Month)),0) as YoY
    from xyz 
    order by 3

    would someone help me on this.

    Regards,

    SKM

    Sunday, December 8, 2019 8:03 AM

Answers

  • Would you like this one ?

    ;with xyz (Company_C,C_YEAR,C_Month,C_Quarter)
    as
    (
    select 
    count(Company_ID) Company_C, 
    datepart(Year,CREATED) C_YEAR,
    datepart(Month,CREATED) C_Month,
    datepart(Quarter,CREATED) C_Quarter
    FROM  [Company]
    where datepart(Month,CREATED) is not null
    group by 
    datepart(Month,CREATED),
    datepart(Quarter,CREATED),
    datepart(Year,CREATED) 
    ),cte as (
    select *,
    sum(Company_C) over (partition by C_YEAR, C_Quarter) sum_C_Quarter,
    sum(Company_C) over (partition by C_YEAR) sum_C_YEAR
    from xyz
    )
    select Company_C,C_YEAR, C_Quarter,C_Month,
    round(isnull(100*(Company_C - LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)) / (LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)),0),2) as MoM,
    isnull(100*(sum_C_Quarter - LAG(sum_C_Quarter, 3) over (ORDER BY C_YEAR ,C_Quarter)) / (LAG(sum_C_Quarter, 3) over ( ORDER BY C_YEAR,C_Quarter)),0) as QoQ,
    isnull(100*(sum_C_YEAR - LAG(sum_C_YEAR, 12) over ( ORDER BY C_YEAR)) / (LAG(sum_C_YEAR, 12) over ( ORDER BY C_YEAR,C_Month)),0) as YoY
    from cte 
    order by 3
    /*
    Company_C   C_YEAR      C_Quarter   C_Month     MoM         QoQ         YoY
    ----------- ----------- ----------- ----------- ----------- ----------- -----------
    6           2019        1           1           -60         -21         0
    14          2019        1           2           133         -21         0
    13          2019        1           3           -7          -21         0
    8           2019        2           4           -38         6           0
    22          2019        2           5           175         6           0
    5           2019        2           6           -77         6           0
    12          2019        3           7           140         -22         0
    12          2019        3           8           0           -22         0
    3           2019        3           9           -75         -22         0
    20          2019        4           10          566         122         269
    40          2019        4           11          100         122         269
    13          2018        4           10          0           0           0
    14          2018        4           11          7           0           0
    15          2018        4           12          7           0           0
    */

    Best Regards,

    Rachel 


    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.

    • Marked as answer by skmnaik Monday, December 9, 2019 2:35 PM
    Monday, December 9, 2019 9:28 AM

All replies

  • This is the formula?

    ((2019 sales - 2018 sales) / 2018 sales) * 100 = %


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 8, 2019 8:32 AM
    Answerer
  • It would have helped if you had provided CREATE TABLE statements for your tables as well as INSERT statements with sample data and the desired result given the sample. That would permit us to copy the script into a query window to develop a tested solution. Now we only have the sample data, but we may not feel like to transform that into CREATE TABLE + INSERT. And we don't what result you want.

    An obvious problem with the query above is that you will run into problems if there is a month with no sales at all, but if the volume is big enough, you may choose to ignore that.

    Another problem, I think, is that the growth per quarter you get is really the growth from Jan to Apr, from Feb to May etc. Not really the growth from Q1 to Q2. But since I don't see your expected results, I don't know what your definition of growth is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 8, 2019 10:27 AM
  • Try this expression too:

       isnull(round(cast(COMPANY_C - pm as real) / pm * 100, 2), 0) as MoM

    where ‘pm’ is your ‘lag(COMPANY_C) over (order by C_YEAR, C_Month)’.



    • Edited by Viorel_MVP Sunday, December 8, 2019 10:32 AM
    Sunday, December 8, 2019 10:30 AM
  • Hi skmnaik,

    Per your description , I do a simple example and get following result . Please check. 

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test 
    (
    Company_C int, 	
    C_YEAR	int,
    C_Month	int,
    C_Quarter int
    )
    insert into test values 
    (6	,2019	,1	,1),
    (14	,2019	,2	,1),
    (13	,2019	,3	,1),
    (8	,2019	,4	,2),
    (22	,2019	,5	,2),
    (5	,2019	,6	,2),
    (12	,2019	,7	,3),
    (12	,2019	,8	,3),
    (3	,2019	,9	,3),
    (12	,2018	,10	,4),
    (20	,2019	,10	,4),
    (14	,2018	,11	,4),
    (40	,2019	,11	,4),
    (15	,2018	,12	,4)
    select Company_C,C_YEAR, C_Quarter,C_Month,
    round(isnull(100*(Company_C - LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)) / (LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)),0),2) as MoM,
    isnull(100*(Company_C - LAG(Company_C, 3) over (ORDER BY C_YEAR ,C_Quarter)) / (LAG(Company_C, 3) over ( ORDER BY C_YEAR,C_Quarter)),0) as QoQ,
    isnull(100*(Company_C - LAG(Company_C, 12) over ( ORDER BY C_YEAR)) / (LAG(Company_C, 12) over ( ORDER BY C_YEAR)),0) as YoY
    from test 
    order by 3
    /*
    Company_C   C_YEAR      C_Quarter   C_Month     MoM         QoQ         YoY
    ----------- ----------- ----------- ----------- ----------- ----------- -----------
    6           2019        1           1           -60         -50         0
    14          2019        1           2           133         0           0
    13          2019        1           3           -7          -13         0
    8           2019        2           4           -38         33          0
    22          2019        2           5           175         57          0
    5           2019        2           6           -77         -61         0
    12          2019        3           7           140         50          0
    12          2019        3           8           0           -45         0
    3           2019        3           9           -75         -40         0
    20          2019        4           10          566         66          66
    40          2019        4           11          100         233         185
    12          2018        4           10          0           0           0
    14          2018        4           11          16          0           0
    15          2018        4           12          7           0           0
    */
    
    
    

    My understanding about 'Growth Factor on Year-to-Year, Month-to-Month Quarter-to-Quarter' might be following things . But I might be not understand what is your real requirement and have some questions about your logic, could you please explain for me ?

    1. Year-to-Year= (sum(the value of 2019)-sum(the value of 2019))/ sum(the value of 2019) ??

    1. Quarter-Quarter= (sum(the value of 2019 1 Quarter)-sum(the value of 2018 4 Quarter))/ sum(the value of 2018 4 Quarter) ??

    Could you please share us your logic and expected result more clearly? Thank you in advance.

    Best Regards,

    Rachel 


    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.

    Monday, December 9, 2019 6:36 AM
  • Hi Erland,

    CREATE TABLE [dbo].[Company]
    (
    [Created] [datetime2](6) NULL,
    [Company_id] [decimal](22, 0) NOT NULL
    )
    insert into  [dbo].[Company] values ('2018-10-17 23:23:43.836000',450)
    insert into  [dbo].[Company] values ('2019-10-16 16:24:48.097000',3250)
    insert into  [dbo].[Company] values ('2019-11-25 16:19:15.810000',3877)
    insert into  [dbo].[Company] values ('2018-10-17 23:45:31.710000',510)
    insert into  [dbo].[Company] values ('2019-10-10 18:53:06.392000',3190)
    insert into  [dbo].[Company] values ('2019-11-04 14:45:08.289000',3450)
    insert into  [dbo].[Company] values ('2018-10-17 23:56:48.968000',550)
    insert into  [dbo].[Company] values ('2019-10-13 20:52:08.320000',3210)
    insert into  [dbo].[Company] values ('2019-11-07 13:24:12.622000',3532)
    insert into  [dbo].[Company] values ('2018-10-17 23:29:35.346000',451)
    insert into  [dbo].[Company] values ('2019-10-21 19:18:48.338000',3310)
    insert into  [dbo].[Company] values ('2019-11-20 10:59:17.874000',3672)
    insert into  [dbo].[Company] values ('2018-10-18 00:02:38.115000',570)
    insert into  [dbo].[Company] values ('2018-11-28 12:52:12.205000',590)
    insert into  [dbo].[Company] values ('2019-11-20 11:19:38.349000',3674)
    insert into  [dbo].[Company] values ('2018-11-28 13:59:48.934000',611)
    insert into  [dbo].[Company] values ('2018-10-17 23:23:43.826000',490)
    insert into  [dbo].[Company] values ('2018-11-28 14:10:15.097000',630)
    insert into  [dbo].[Company] values ('2018-10-17 19:47:33.077000',430)
    insert into  [dbo].[Company] values ('2018-11-28 16:08:04.034000',670)
    insert into  [dbo].[Company] values ('2018-10-17 19:52:42.990000',431)
    insert into  [dbo].[Company] values ('2018-11-28 16:45:50.715000',710)
    insert into  [dbo].[Company] values ('2018-10-17 23:35:23.766000',470)
    insert into  [dbo].[Company] values ('2018-11-28 16:50:04.264000',711)
    insert into  [dbo].[Company] values ('2018-10-17 19:59:17.243000',432)
    insert into  [dbo].[Company] values ('2018-11-29 10:31:37.265000',730)
    insert into  [dbo].[Company] values ('2018-10-17 23:51:53.151000',530)
    insert into  [dbo].[Company] values ('2018-11-29 10:36:02.828000',731)
    insert into  [dbo].[Company] values ('2018-10-17 19:18:00.429000',410)
    insert into  [dbo].[Company] values ('2018-11-29 12:38:45.627000',750)
    insert into  [dbo].[Company] values ('2018-10-17 23:40:14.868000',490)
    insert into  [dbo].[Company] values ('2018-11-29 12:39:38.289000',751)
    insert into  [dbo].[Company] values ('2018-11-28 16:22:43.846000',690)
    insert into  [dbo].[Company] values ('2018-12-18 12:36:43.869000',970)
    insert into  [dbo].[Company] values ('2018-12-07 11:48:11.871000',930)
    insert into  [dbo].[Company] values ('2018-12-19 12:06:04.132000',990)
    insert into  [dbo].[Company] values ('2019-01-08 12:29:09.785000',1130)
    insert into  [dbo].[Company] values ('2018-12-20 15:55:19.574000',1010)
    insert into  [dbo].[Company] values ('2019-01-30 15:47:20.388000',1290)
    insert into  [dbo].[Company] values ('2019-02-12 19:12:29.582000',1370)
    insert into  [dbo].[Company] values ('2019-01-08 12:50:43.819000',1171)
    insert into  [dbo].[Company] values ('2019-02-18 13:55:22.024000',1470)
    insert into  [dbo].[Company] values ('2019-01-17 21:52:51.887000',1270)
    insert into  [dbo].[Company] values ('2019-04-29 14:46:59.265000',2090)
    insert into  [dbo].[Company] values ('2019-02-11 15:14:54.255000',1310)
    insert into  [dbo].[Company] values ('2019-02-27 14:48:54.068000',1590)
    insert into  [dbo].[Company] values ('2019-02-12 10:27:12.748000',1330)
    insert into  [dbo].[Company] values ('2019-02-27 15:37:45.533000',1610)
    insert into  [dbo].[Company] values ('2019-02-15 16:17:16.572000',1450)
    insert into  [dbo].[Company] values ('2019-05-09 14:01:21.259000',2111)
    insert into  [dbo].[Company] values ('2019-02-20 13:50:54.983000',1530)
    insert into  [dbo].[Company] values ('2019-05-13 09:03:50.417000',2130)
    insert into  [dbo].[Company] values ('2019-03-28 15:33:52.037000',1830)
    insert into  [dbo].[Company] values ('2019-05-13 13:32:23.757000',2150)
    insert into  [dbo].[Company] values ('2019-04-10 10:51:16.469000',1950)
    insert into  [dbo].[Company] values ('2019-05-20 13:00:19.799000',2270)
    insert into  [dbo].[Company] values ('2019-04-26 12:45:17.976000',2070)
    insert into  [dbo].[Company] values ('2019-05-20 13:07:37.239000',2290)
    insert into  [dbo].[Company] values ('2019-07-04 15:02:12.585000',2530)
    insert into  [dbo].[Company] values ('2019-05-27 13:04:36.830000',2390)
    insert into  [dbo].[Company] values ('2019-07-09 11:31:03.007000',2570)
    insert into  [dbo].[Company] values ('2019-05-28 00:03:10.164000',2411)
    insert into  [dbo].[Company] values ('2019-08-01 13:02:20.951000',2770)
    insert into  [dbo].[Company] values ('2019-05-28 11:02:16.530000',2412)
    insert into  [dbo].[Company] values ('2019-08-23 11:17:56.704000',2870)
    insert into  [dbo].[Company] values ('2019-05-28 11:16:16.616000',2413)
    insert into  [dbo].[Company] values ('2019-05-18 16:32:11.515000',2210)
    insert into  [dbo].[Company] values ('2019-07-09 11:39:01.134000',2571)
    insert into  [dbo].[Company] values ('2019-05-25 16:00:08.289000',2370)
    insert into  [dbo].[Company] values ('2019-07-11 13:10:57.050000',2630)
    insert into  [dbo].[Company] values ('2019-08-26 23:36:36.585000',2890)
    insert into  [dbo].[Company] values ('2019-07-16 15:41:33.395000',2670)
    insert into  [dbo].[Company] values ('2019-08-27 12:43:08.459000',2911)
    insert into  [dbo].[Company] values ('2019-07-24 21:23:16.969000',2690)
    insert into  [dbo].[Company] values ('2019-07-26 22:24:41.795000',2714)
    insert into  [dbo].[Company] values ('2019-07-29 15:11:39.550000',2730)
    insert into  [dbo].[Company] values ('2019-08-27 14:01:04.459000',2912)
    insert into  [dbo].[Company] values ('2019-08-07 15:55:56.738000',2790)
    insert into  [dbo].[Company] values ('2019-08-27 14:16:04.829000',2913)
    insert into  [dbo].[Company] values ('2019-08-08 13:55:24.182000',2810)
    insert into  [dbo].[Company] values ('2019-09-04 11:22:42.543000',2930)
    insert into  [dbo].[Company] values ('2019-10-24 12:56:06.983000',3392)
    insert into  [dbo].[Company] values ('2019-09-30 19:03:07.538000',2990)
    insert into  [dbo].[Company] values ('2019-11-05 11:11:16.186000',3470)
    insert into  [dbo].[Company] values ('2019-10-19 03:33:45.162000',3270)
    insert into  [dbo].[Company] values ('2019-11-06 14:44:42.798000',3510)
    insert into  [dbo].[Company] values ('2019-10-04 13:13:40.732000',3070)
    insert into  [dbo].[Company] values ('2019-11-25 12:14:05.909000',3850)
    insert into  [dbo].[Company] values ('2019-10-21 15:17:52.019000',3290)
    insert into  [dbo].[Company] values ('2019-11-25 16:36:52.112000',3882)
    insert into  [dbo].[Company] values ('2018-11-28 13:36:19.864000',610)
    insert into  [dbo].[Company] values ('2019-10-22 11:23:09.944000',3331)
    insert into  [dbo].[Company] values ('2018-11-28 14:17:15.440000',631)
    insert into  [dbo].[Company] values ('2019-11-12 11:10:45.762000',3570)
    insert into  [dbo].[Company] values ('2018-11-28 14:48:10.247000',650)
    insert into  [dbo].[Company] values ('2019-11-20 12:08:28.254000',3677)
    insert into  [dbo].[Company] values ('2018-12-03 14:37:01.351000',770)
    insert into  [dbo].[Company] values ('2019-11-20 19:49:58.925000',3690)
    insert into  [dbo].[Company] values ('2018-12-03 16:53:18.824000',790)
    insert into  [dbo].[Company] values ('2019-11-20 21:24:11.099000',3710)
    insert into  [dbo].[Company] values ('2018-12-04 10:52:45.992000',811)
    insert into  [dbo].[Company] values ('2019-11-22 15:14:13.831000',3830)
    insert into  [dbo].[Company] values ('2018-12-04 15:22:20.107000',830)
    insert into  [dbo].[Company] values ('2019-10-22 17:52:15.000000',3370)
    insert into  [dbo].[Company] values ('2019-01-07 16:56:01.307000',1090)
    insert into  [dbo].[Company] values ('2019-10-24 16:20:05.572000',3412)
    insert into  [dbo].[Company] values ('2019-02-13 10:26:21.959000',1390)
    insert into  [dbo].[Company] values ('2019-11-21 12:18:10.293000',3750)
    insert into  [dbo].[Company] values ('2019-02-15 12:31:02.407000',1410)
    insert into  [dbo].[Company] values ('2019-11-21 18:07:20.870000',3790)
    insert into  [dbo].[Company] values ('2019-03-01 17:55:24.116000',1650)
    insert into  [dbo].[Company] values ('2019-11-21 18:28:28.276000',3791)
    insert into  [dbo].[Company] values ('2019-02-19 17:17:04.102000',1510)
    insert into  [dbo].[Company] values ('2019-11-21 18:29:17.625000',3792)
    insert into  [dbo].[Company] values ('2019-03-08 15:19:09.105000',1670)
    insert into  [dbo].[Company] values ('2019-10-30 15:39:44.021000',3431)
    insert into  [dbo].[Company] values ('2019-03-14 17:49:15.505000',1690)
    insert into  [dbo].[Company] values ('2019-10-30 15:42:19.848000',3432)
    insert into  [dbo].[Company] values ('2019-03-14 18:18:44.744000',1691)
    insert into  [dbo].[Company] values ('2019-11-07 15:34:17.417000',3533)
    insert into  [dbo].[Company] values ('2019-03-15 16:24:53.651000',1710)
    insert into  [dbo].[Company] values ('2019-11-11 18:35:09.719000',3553)
    insert into  [dbo].[Company] values ('2019-03-19 12:27:42.506000',1730)
    insert into  [dbo].[Company] values ('2019-11-19 18:09:03.078000',3650)
    insert into  [dbo].[Company] values ('2019-03-20 15:07:36.053000',1750)
    insert into  [dbo].[Company] values ('2019-11-21 13:03:12.274000',3770)
    insert into  [dbo].[Company] values ('2019-03-20 16:58:58.347000',1770)
    insert into  [dbo].[Company] values ('2019-11-21 13:18:50.594000',3771)
    insert into  [dbo].[Company] values ('2019-03-29 18:30:48.875000',1850)
    insert into  [dbo].[Company] values ('2019-10-22 15:16:54.795000',3350)
    insert into  [dbo].[Company] values ('2019-04-01 15:17:37.492000',1870)
    insert into  [dbo].[Company] values ('2019-10-24 13:41:16.183000',3396)
    insert into  [dbo].[Company] values ('2019-04-11 14:15:27.699000',1970)
    insert into  [dbo].[Company] values ('2019-11-04 15:21:12.428000',3451)
    insert into  [dbo].[Company] values ('2019-05-09 16:22:09.332000',2112)
    insert into  [dbo].[Company] values ('2019-11-11 17:26:27.399000',3550)
    insert into  [dbo].[Company] values ('2019-05-14 12:09:18.974000',2170)
    insert into  [dbo].[Company] values ('2019-11-14 12:48:37.384000',3590)
    insert into  [dbo].[Company] values ('2019-05-24 19:08:31.565000',2350)
    insert into  [dbo].[Company] values ('2019-11-19 15:15:02.554000',3631)
    insert into  [dbo].[Company] values ('2019-05-28 12:42:05.972000',2414)
    insert into  [dbo].[Company] values ('2019-11-19 15:31:28.920000',3632)
    insert into  [dbo].[Company] values ('2019-05-18 16:44:10.820000',2230)
    insert into  [dbo].[Company] values ('2019-11-19 15:43:59.573000',3633)
    insert into  [dbo].[Company] values ('2019-06-10 18:04:30.522000',2430)
    insert into  [dbo].[Company] values ('2019-11-19 16:10:54.362000',3635)
    insert into  [dbo].[Company] values ('2019-06-11 10:27:25.910000',2450)
    insert into  [dbo].[Company] values ('2019-11-19 16:12:44.798000',3636)
    insert into  [dbo].[Company] values ('2019-06-18 18:20:23.224000',2470)
    insert into  [dbo].[Company] values ('2019-11-21 11:43:08.248000',3730)
    insert into  [dbo].[Company] values ('2019-06-19 18:08:38.959000',2491)
    insert into  [dbo].[Company] values ('2019-11-05 13:49:51.731000',3491)
    insert into  [dbo].[Company] values ('2019-06-25 15:01:56.558000',2510)
    insert into  [dbo].[Company] values ('2019-11-07 16:46:10.677000',3540)
    insert into  [dbo].[Company] values ('2019-07-09 18:29:53.904000',2590)
    insert into  [dbo].[Company] values ('2019-11-11 17:33:07.175000',3551)
    insert into  [dbo].[Company] values ('2019-07-12 14:30:37.783000',2650)
    insert into  [dbo].[Company] values ('2019-11-20 11:42:51.586000',3676)
    insert into  [dbo].[Company] values ('2018-12-04 10:43:43.332000',810)
    insert into  [dbo].[Company] values ('2019-11-22 11:59:55.316000',3810)
    insert into  [dbo].[Company] values ('2018-12-04 17:06:06.300000',850)
    insert into  [dbo].[Company] values ('2019-11-25 17:45:36.871000',3910)
    insert into  [dbo].[Company] values ('2018-12-05 18:17:39.662000',870)
    insert into  [dbo].[Company] values ('2019-11-15 17:00:26.763000',3610)
    insert into  [dbo].[Company] values ('2018-12-06 13:12:18.003000',890)
    insert into  [dbo].[Company] values ('2019-11-25 15:41:43.309000',3870)
    insert into  [dbo].[Company] values ('2018-12-06 17:36:56.285000',910)
    insert into  [dbo].[Company] values ('2018-12-12 10:33:37.120000',950)
    insert into  [dbo].[Company] values ('2018-12-21 11:56:54.054000',1030)
    insert into  [dbo].[Company] values ('2019-02-12 16:05:05.455000',1350)
    insert into  [dbo].[Company] values ('2019-01-10 14:29:51.744000',1210)
    insert into  [dbo].[Company] values ('2019-03-28 13:19:02.287000',1810)
    insert into  [dbo].[Company] values ('2019-02-21 14:49:34.324000',1550)
    insert into  [dbo].[Company] values ('2019-02-27 16:08:04.471000',1630)
    insert into  [dbo].[Company] values ('2019-03-20 18:57:16.930000',1790)
    insert into  [dbo].[Company] values ('2019-03-28 16:56:53.700000',1831)
    insert into  [dbo].[Company] values ('2019-04-01 16:53:44.081000',1890)
    insert into  [dbo].[Company] values ('2019-10-10 15:00:54.964000',3110)
    insert into  [dbo].[Company] values ('2019-04-15 13:11:23.703000',1990)
    insert into  [dbo].[Company] values ('2019-04-24 12:31:55.680000',2050)
    insert into  [dbo].[Company] values ('2019-05-09 14:00:34.036000',2110)
    insert into  [dbo].[Company] values ('2019-05-15 12:58:44.032000',2190)
    insert into  [dbo].[Company] values ('2019-05-20 13:03:48.112000',2271)
    insert into  [dbo].[Company] values ('2019-05-22 11:09:24.418000',2310)
    insert into  [dbo].[Company] values ('2019-05-27 16:19:08.339000',2410)
    insert into  [dbo].[Company] values ('2019-07-05 08:24:31.196000',2550)
    insert into  [dbo].[Company] values ('2019-05-20 12:21:21.133000',2250)
    insert into  [dbo].[Company] values ('2019-07-26 21:42:23.755000',2710)
    insert into  [dbo].[Company] values ('2019-08-13 15:05:20.448000',2830)
    insert into  [dbo].[Company] values ('2019-08-22 12:32:25.619000',2850)
    insert into  [dbo].[Company] values ('2019-08-27 12:37:03.556000',2910)
    insert into  [dbo].[Company] values ('2019-08-27 14:46:37.483000',2914)
    insert into  [dbo].[Company] values ('2019-10-04 18:21:34.173000',3090)
    insert into  [dbo].[Company] values ('2019-09-30 22:37:38.715000',3010)
    insert into  [dbo].[Company] values ('2019-10-10 15:37:59.253000',3130)
    insert into  [dbo].[Company] values ('2019-10-01 15:11:18.532000',3030)
    insert into  [dbo].[Company] values ('2019-10-10 16:32:07.696000',3150)

    above is the script for data.

    select 
    count(Company_ID) Company_C, 
    datepart(Year,CREATED) C_YEAR,
    datepart(Month,CREATED) C_Month,
    datepart(Quarter,CREATED) C_Quarter
    FROM  COMPANY
    where datepart(Month,CREATED) is not null
    group by 
    datepart(Month,CREATED),
    datepart(Quarter,CREATED),
    datepart(Year,CREATED

    we want growth factor for year, Quarter and Month. with my first query i am able to get month growth factor but not quarter and year.

    quarter formula : Q2-Q1/Q1*100,  ....same for Month and year.

    we need columns like company_id, Year, Month, Quarter, Growth Quarter, Growth Month, Growth year.

    hope got all requirements with data. please help me.

    Thanks,

    SKM

    Monday, December 9, 2019 7:01 AM
  • Hi Rachel,

    your understanding on quarter is correct, same for year and month.

    Quarter-Quarter= (sum(the value of 2019 1 Quarter)-sum(the value of 2018 4 Quarter))/ sum(the value of 2018 4 Quarter)

    Company_C C_YEAR C_Quarter C_Month QoQ
    12 2018 4 10 0
    15 2018 4 12 0
    14 2018 4 11 0
    13 2019 1 3 8
    6 2019 1 1 -60
    14 2019 1 2 0
    22 2019 2 5 69
    8 2019 2 4 33
    5 2019 2 6 -64

    in above output quarter 2 has 3 columns, expected output is all quarter should sum up and and give one result. 

    eg; quarter 2, column 1 and column 2 should be null/0 and result should be column 3.

    please let me know any more understanding.

    Thanks,

    SKM

    Monday, December 9, 2019 7:13 AM
  • Would you like this one ?

    ;with xyz (Company_C,C_YEAR,C_Month,C_Quarter)
    as
    (
    select 
    count(Company_ID) Company_C, 
    datepart(Year,CREATED) C_YEAR,
    datepart(Month,CREATED) C_Month,
    datepart(Quarter,CREATED) C_Quarter
    FROM  [Company]
    where datepart(Month,CREATED) is not null
    group by 
    datepart(Month,CREATED),
    datepart(Quarter,CREATED),
    datepart(Year,CREATED) 
    ),cte as (
    select *,
    sum(Company_C) over (partition by C_YEAR, C_Quarter) sum_C_Quarter,
    sum(Company_C) over (partition by C_YEAR) sum_C_YEAR
    from xyz
    )
    select Company_C,C_YEAR, C_Quarter,C_Month,
    round(isnull(100*(Company_C - LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)) / (LAG(Company_C, 1) over (ORDER BY C_YEAR,C_Month)),0),2) as MoM,
    isnull(100*(sum_C_Quarter - LAG(sum_C_Quarter, 3) over (ORDER BY C_YEAR ,C_Quarter)) / (LAG(sum_C_Quarter, 3) over ( ORDER BY C_YEAR,C_Quarter)),0) as QoQ,
    isnull(100*(sum_C_YEAR - LAG(sum_C_YEAR, 12) over ( ORDER BY C_YEAR)) / (LAG(sum_C_YEAR, 12) over ( ORDER BY C_YEAR,C_Month)),0) as YoY
    from cte 
    order by 3
    /*
    Company_C   C_YEAR      C_Quarter   C_Month     MoM         QoQ         YoY
    ----------- ----------- ----------- ----------- ----------- ----------- -----------
    6           2019        1           1           -60         -21         0
    14          2019        1           2           133         -21         0
    13          2019        1           3           -7          -21         0
    8           2019        2           4           -38         6           0
    22          2019        2           5           175         6           0
    5           2019        2           6           -77         6           0
    12          2019        3           7           140         -22         0
    12          2019        3           8           0           -22         0
    3           2019        3           9           -75         -22         0
    20          2019        4           10          566         122         269
    40          2019        4           11          100         122         269
    13          2018        4           10          0           0           0
    14          2018        4           11          7           0           0
    15          2018        4           12          7           0           0
    */

    Best Regards,

    Rachel 


    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.

    • Marked as answer by skmnaik Monday, December 9, 2019 2:35 PM
    Monday, December 9, 2019 9:28 AM
  • Hi Rachel,

    Thanks a lot, solution is working fine, small change required is: YoY we are getting values repeated in months(10,11), calculation is correct.  but we have to show only one value in last month of year. if possible try. else solution is working as expected.

    Thanks,

    SKM

    Monday, December 9, 2019 2:41 PM