Calculating moving moving averages for different ranges in one query

  • I have this query, which is gives result I want (may be not, i have not checked extensively yet), i feel like it could be written in more effective way.

    I have to calculate moving average, for 7 , 90, 365 days, and for 3 and 12 month, for every one of them I have written separate query. For Monthly i have grouped by month, and converted as first day of month.

    Limitations that  I have:

    1. this is a part of the query, which is inside cte, so I can not write another cte (could be my knowledge limitation because i suck at cte's :)).

    2. And query is build for ssrs report which accepts, From to Todate parameters, normally one month range, and calculations need to go beyond that to find correct average. i.e 90 days moving avg etc.

    and last its required to calculate by calendar days, 90 Days past from FromDate. Days with no sales does not appear in the result which is good, that it doesnt skew averages, but if i put between 89 days and current row, it will search 90 Sales day, but what I need is 90 calendar days of data. (90 days is example for 90 days avg  calculation, other averages should follow their own limits).

    Do you think its possible to achieve this in better way? possibly with one query?

    declare @FromDate as datetime2 ='2024-02-01'
    declare @ToDate as datetime2 ='2024-02-29'




    --Avg 90 Days
    select *
    from (
    select
    null OrderCount,null GuestsTransaction,null TerminalName,alldata.RevenueCenterName,alldata.Unit,alldata.Name,
    null Guests, null NofPos,null items, null AvgCover, null TipAmount, null TotalAmount,null NetAmount, null CashCredit, null GrossAmount,
    null MovingAverageDay,
    avg(sum(alldata.NetAmount)) over (partition by alldata.RevenueCenterName, alldata.Unit, alldata.Name order by alldata.OrderDate rows between 7 preceding and current row) MovingAverage7Day,
    avg(sum(alldata.NetAmount)) over (partition by alldata.RevenueCenterName, alldata.Unit, alldata.Name order by alldata.OrderDate rows between unbounded preceding and current row) as MovingAverage90Day,
    null as MovingAverage365Day,null MovingAverage3month, null MovingAverage12month,
    alldata.OrderDate,null Cash, null Credit, null Food, null Beverage, null Other,'00:00-01:00' as HourOfDay,
    null Discount, null DiscountAmount,null DiscountQuantity,null CheckCount,null CustomerCount
    from (
    select
    o.id,
    o.RevenueCenterName,
    u.Unit,
    u.Name,
    o.NetAmount,
    convert(date, o.CreatedOn at time zone 'UTC' at time zone u.TimeZone) as OrderDate,
    o.CreatedOn,
    u.TimeZone
    from dbo.[Order] o
    join dbo.Unit u on o.Unit = u.Unit
    where o.CreatedOn >= dateadd(day, -90, @ToDate)
    and o.CreatedOn <= @ToDate

    ) as alldata
    group by
    alldata.RevenueCenterName,
    alldata.Unit,
    alldata.Name,
    alldata.OrderDate
    ) as movingaverages
    where movingaverages.OrderDate >= @FromDate and movingaverages.OrderDate <= @ToDate
    --Avg 365 Days
    union all
    select *
    from (
    select
    null OrderCount,null GuestsTransaction,null TerminalName,alldata.RevenueCenterName,alldata.Unit,alldata.Name,
    null Guests, null NofPos,null items, null AvgCover, null TipAmount, null TotalAmount,null NetAmount, null CashCredit, null GrossAmount,
    null MovingAverageDay,null MovingAverage7Day,null as MovingAverage90Day,
    avg(sum(alldata.NetAmount)) over (partition by alldata.RevenueCenterName, alldata.Unit, alldata.Name order by alldata.OrderDate rows between unbounded preceding and current row) as MovingAverage365Day,
    null MovingAverage3month, null MovingAverage12month,
    alldata.OrderDate,null Cash, null Credit, null Food, null Beverage, null Other,'00:00-01:00' as HourOfDay,
    null Discount, null DiscountAmount,null DiscountQuantity,null CheckCount,null CustomerCount
    from (
    select
    o.id,
    o.RevenueCenterName,
    u.Unit,
    u.Name,
    o.NetAmount,
    convert(date, o.CreatedOn at time zone 'UTC' at time zone u.TimeZone) as OrderDate,
    o.CreatedOn,
    u.TimeZone
    from dbo.[Order] o
    join dbo.Unit u on o.Unit = u.Unit
    where o.CreatedOn >= dateadd(day, -365, @ToDate)
    and o.CreatedOn <= @ToDate

    ) as alldata
    group by
    alldata.RevenueCenterName,
    alldata.Unit,
    alldata.Name,
    alldata.OrderDate
    ) as movingaverages
    where movingaverages.OrderDate >= @FromDate and movingaverages.OrderDate <= @ToDate

    Sample data:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Order](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SourceOrderId] [nvarchar](50) NOT NULL,
    [ReferenceOrderId] [nvarchar](50) NULL,
    [Transformation] [nvarchar](10) NOT NULL,
    [Order] [nvarchar](50) NOT NULL,
    [Unit] [nvarchar](10) NULL,
    [UnitName] [nvarchar](200) NOT NULL,
    [RevenueCenter] [nvarchar](50) NOT NULL,
    [RevenueCenterName] [nvarchar](128) NOT NULL,
    [Customer] [nvarchar](24) NULL,
    [CustomerName] [nvarchar](200) NULL,
    [Guests] [int] NOT NULL,
    [SubtotalAmount] [decimal](18, 2) NOT NULL,
    [TaxAmount] [decimal](18, 2) NOT NULL,
    [DiscountAmount] [decimal](18, 2) NOT NULL,
    [TipAmount] [decimal](18, 2) NOT NULL,
    [TotalAmount] [decimal](18, 2) NOT NULL,
    [NetAmount] [decimal](18, 2) NOT NULL,
    [GrossAmount] [decimal](18, 2) NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [PaidOn] [datetime2](7) NULL,
    [ClosedOn] [datetime2](7) NULL
    ) ON [PRIMARY]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Unit](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Unit] [nvarchar](20) NOT NULL,
    [Operation] [nvarchar](10) NOT NULL,
    [UnitCode] [nvarchar](20) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [ShortName] [nvarchar](30) NULL,
    [TimeZone] [nvarchar](100) NOT NULL
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[Order] ON
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601729, N'004450e4-e596-47f7-8931-d8e657f8570a', NULL, N'Hadi', N'7111100', N'0093205001', N'National', N'0093205001_002', N'Expresso', NULL, NULL, 1, CAST(28.45 AS Decimal(18, 2)), CAST(2.85 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(31.30 AS Decimal(18, 2)), CAST(28.45 AS Decimal(18, 2)), CAST(31.30 AS Decimal(18, 2)), CAST(N'2024-02-12T15:21:53.9240000' AS DateTime2), CAST(N'2024-02-12T15:22:40.9980000' AS DateTime2), CAST(N'2024-02-12T15:22:41.0020000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601733, N'00457c08-89ba-4d91-88fa-5b7ca65bd027', NULL, N'Hadi', N'937327', N'0093205001', N'National', N'0093205001_003', N'Gardens', NULL, NULL, 1, CAST(22.15 AS Decimal(18, 2)), CAST(2.22 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.44 AS Decimal(18, 2)), CAST(24.37 AS Decimal(18, 2)), CAST(22.15 AS Decimal(18, 2)), CAST(24.37 AS Decimal(18, 2)), CAST(N'2024-02-18T16:16:38.4870000' AS DateTime2), CAST(N'2024-02-18T16:17:32.8330000' AS DateTime2), CAST(N'2024-02-18T16:17:32.8430000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601737, N'00462429-f788-4e2a-b8a4-98606839b5d9', NULL, N'Hadi', N'7109890', N'0093205001', N'National', N'0093205001_002', N'Expresso', NULL, NULL, 1, CAST(3.50 AS Decimal(18, 2)), CAST(0.25 AS Decimal(18, 2)), CAST(1.05 AS Decimal(18, 2)), CAST(0.54 AS Decimal(18, 2)), CAST(2.70 AS Decimal(18, 2)), CAST(2.45 AS Decimal(18, 2)), CAST(3.75 AS Decimal(18, 2)), CAST(N'2024-02-06T20:40:56.0850000' AS DateTime2), CAST(N'2024-02-06T20:41:15.5220000' AS DateTime2), CAST(N'2024-02-06T20:41:15.5280000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601738, N'004657ab-2682-4900-ad68-7fe261679be9', NULL, N'Hadi', N'591270', N'0093205001', N'National', N'0093205001_003', N'Gardens', NULL, NULL, 1, CAST(12.00 AS Decimal(18, 2)), CAST(1.20 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.98 AS Decimal(18, 2)), CAST(13.20 AS Decimal(18, 2)), CAST(12.00 AS Decimal(18, 2)), CAST(13.20 AS Decimal(18, 2)), CAST(N'2024-02-25T20:10:29.2120000' AS DateTime2), CAST(N'2024-02-25T20:11:10.4630000' AS DateTime2), CAST(N'2024-02-25T20:11:10.4700000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601752, N'004b7682-d925-473b-a331-6c7968351d44', NULL, N'Hadi', N'7109963', N'0093205001', N'National', N'0093205001_002', N'Expresso', NULL, NULL, 1, CAST(6.50 AS Decimal(18, 2)), CAST(0.65 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.15 AS Decimal(18, 2)), CAST(6.50 AS Decimal(18, 2)), CAST(7.15 AS Decimal(18, 2)), CAST(N'2024-02-07T17:33:41.7370000' AS DateTime2), CAST(N'2024-02-07T17:33:56.2860000' AS DateTime2), CAST(N'2024-02-07T17:33:56.2890000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601756, N'004d2b45-4a56-45b0-8ac7-b212d0c24294', NULL, N'Hadi', N'477903', N'0093205001', N'National', N'0093205001_001', N'Gascade', NULL, NULL, 1, CAST(10.15 AS Decimal(18, 2)), CAST(0.71 AS Decimal(18, 2)), CAST(3.05 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.81 AS Decimal(18, 2)), CAST(7.10 AS Decimal(18, 2)), CAST(10.86 AS Decimal(18, 2)), CAST(N'2024-02-27T13:43:53.3310000' AS DateTime2), CAST(N'2024-02-27T13:44:14.2690000' AS DateTime2), CAST(N'2024-02-27T13:44:14.2870000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601761, N'004e9ca3-7d8d-4930-8393-2d3f82313c1f', NULL, N'Hadi', N'590296', N'0093205001', N'National', N'0093205001_003', N'Gardens', NULL, NULL, 1, CAST(9.25 AS Decimal(18, 2)), CAST(0.93 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(10.18 AS Decimal(18, 2)), CAST(9.25 AS Decimal(18, 2)), CAST(10.18 AS Decimal(18, 2)), CAST(N'2024-02-19T21:16:36.5060000' AS DateTime2), CAST(N'2024-02-19T21:16:50.4170000' AS DateTime2), CAST(N'2024-02-19T21:16:50.4260000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601767, N'0050c68c-053e-4b8a-8bce-c673baafd375', NULL, N'Hadi', N'7110700', N'0093205001', N'National', N'0093205001_002', N'Expresso', NULL, NULL, 1, CAST(25.65 AS Decimal(18, 2)), CAST(2.57 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(28.22 AS Decimal(18, 2)), CAST(25.65 AS Decimal(18, 2)), CAST(28.22 AS Decimal(18, 2)), CAST(N'2024-02-10T19:19:54.3910000' AS DateTime2), CAST(N'2024-02-10T19:21:09.9110000' AS DateTime2), CAST(N'2024-02-10T19:21:09.9150000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601772, N'0053c6c3-96b6-4be7-9a06-e1e21f590273', NULL, N'Hadi', N'7110639', N'0093205001', N'National', N'0093205001_002', N'Expresso', NULL, NULL, 1, CAST(5.00 AS Decimal(18, 2)), CAST(0.50 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.50 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(5.50 AS Decimal(18, 2)), CAST(N'2024-02-10T18:13:39.6270000' AS DateTime2), CAST(N'2024-02-10T18:14:07.2560000' AS DateTime2), CAST(N'2024-02-10T18:14:07.2620000' AS DateTime2))
    GO
    INSERT [dbo].[Order] ([Id], [SourceOrderId], [ReferenceOrderId], [Transformation], [Order], [Unit], [UnitName], [RevenueCenter], [RevenueCenterName], [Customer], [CustomerName], [Guests], [SubtotalAmount], [TaxAmount], [DiscountAmount], [TipAmount], [TotalAmount], [NetAmount], [GrossAmount], [CreatedOn], [PaidOn], [ClosedOn]) VALUES (601773, N'00543bbf-8411-4bde-a9a7-4065eb412d5f', NULL, N'Hadi', N'590580', N'0093205001', N'National', N'0093205001_003', N'Gardens', NULL, NULL, 1, CAST(2.65 AS Decimal(18, 2)), CAST(0.19 AS Decimal(18, 2)), CAST(0.80 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.04 AS Decimal(18, 2)), CAST(1.85 AS Decimal(18, 2)), CAST(2.84 AS Decimal(18, 2)), CAST(N'2024-02-22T14:58:34.4840000' AS DateTime2), CAST(N'2024-02-22T14:58:59.9800000' AS DateTime2), CAST(N'2024-02-22T14:58:59.9880000' AS DateTime2))
    GO
    SET IDENTITY_INSERT [dbo].[Order] OFF
    GO
    SET IDENTITY_INSERT [dbo].[Unit] ON
    GO
    INSERT [dbo].[Unit] ([Id], [Unit], [Operation], [UnitCode], [Name], [ShortName], [TimeZone]) VALUES (1, N'0093205001', N'0093205001', N'100_76371', N'National', NULL, N'Eastern Standard Time')
    GO
    SET IDENTITY_INSERT [dbo].[Unit] OFF
    GO
  • Keep in mind you windowing functions work on the result set itself !

    Since you want to have time series reporting, you need to make sure your rows cover occurrences for the whole time frame !

    Add a tally or dates table to start with building your series of data.

    Have a look at Jeff's great script: Create a Tally Function (fnTally)

    e.g. ( quick and dirty result set - I added "older" rows to your sample data )

    Declare @DtRef datetime = dateadd(dd, datediff(dd, 0, getdate()), 0) 
    ;with cteTally as (
    Select Dateadd(dd, -1 * n, @DtRef) DD
    from master.dbo.fn_DBA_Tally2(1,366) x
    )
    select *
    from (
    SELECT --NULL AS OrderCount
    --, NULL AS GuestsTransaction
    --, NULL AS TerminalName
    alldata.RevenueCenterName
    , alldata.Unit
    , alldata.Name
    --, NULL AS Guests
    --, NULL AS NofPos
    --, NULL AS items
    --, NULL AS AvgCover
    --, NULL AS TipAmount
    --, NULL AS TotalAmount
    --, NULL AS NetAmount
    --, NULL AS CashCredit
    --, NULL AS GrossAmount
    --, NULL AS MovingAverageDay
    --, NULL AS MovingAverage7Day
    --, NULL AS MovingAverage90Day
    , AVG(SUM(alldata.NetAmount)) OVER(PARTITION BY alldata.RevenueCenterName
    , alldata.Unit
    , alldata.Name
    ORDER BY alldata.OrderDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS MovingAverage7Day
    , AVG(SUM(alldata.NetAmount)) OVER(PARTITION BY alldata.RevenueCenterName
    , alldata.Unit
    , alldata.Name
    ORDER BY alldata.OrderDate ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) AS MovingAverage90Day

    , avg(sum(alldata.NetAmount)) OVER(PARTITION BY alldata.RevenueCenterName
    , alldata.Unit
    , alldata.Name
    order by alldata.OrderDate rows between 365 PRECEDING and CURRENT ROW) as MovingAverage365Day
    --, NULL AS MovingAverage3month
    --, NULL AS MovingAverage12month
    , alldata.OrderDate
    --, NULL AS Cash
    --, NULL AS Credit
    --, NULL AS Food
    --, NULL AS Beverage
    --, NULL AS Other
    , '00:00-01:00' AS HourOfDay
    --, NULL AS Discount
    --, NULL AS DiscountAmount
    --, NULL AS DiscountQuantity
    --, NULL AS CheckCount
    --, NULL AS CustomerCount
    from (
    SELECT o.id
    , o.RevenueCenterName
    , u.Unit
    , u.Name
    , o.NetAmount
    , convert(date, isnull(o.CreatedOn at time zone 'UTC' at time zone u.TimeZone, T.DD)) as OrderDate
    , o.CreatedOn
    , u.TimeZone
    from cteTally T
    left join dbo.[Order] o
    join dbo.Unit u on o.Unit = u.Unit
    on T.dd = convert(date, o.CreatedOn at time zone 'UTC' at time zone u.TimeZone)
    where isnull( o.CreatedOn, T.DD) >= dateadd(day, -365, @ToDate)
    and isnull(o.CreatedOn, T.DD) <= @ToDate

    ) as alldata
    group by
    alldata.RevenueCenterName,
    alldata.Unit,
    alldata.Name,
    alldata.OrderDate
    ) as movingaverages
    where movingaverages.OrderDate >= @FromDate and movingaverages.OrderDate <= @ToDate
    order by OrderDate ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This is great improvment compared my code, thank you for that.

    I only have read access to this database, will talk to the devs about createing function, hopefully they agree.

    The averages are mostly accurate, but for missing days, either this is wrong or mine, or both :), I'll need sometime to check edge cases.

    Again, this is part of the big code that I wrote, which already has CTE on top, and bunch of unions, to handle different granularity data, to feed one big Tablix in ssrs.  You can't design one tablix form multiple query, so I had to merge it all in one query.

    Is there any way to achieve same thing without using CTE? or should I post the rest of my code, on how to inlcude this in there?

     

  • I was able to do it without CTE:

    declare @FromDate as datetime2 = '2023-02-01';declare @ToDate as datetime2 = '2024-04-16';
    declare @DtRef datetime = Dateadd(dd, Datediff(dd, 0,@ToDate), 0);
    select *
    from ( select
    null OrderCount,null GuestsTransaction,null TerminalName,alldata.RevenueCenterName,alldata.Unit,alldata.Name,
    null Guests, null NofPos,null items, null AvgCover, null TipAmount, null TotalAmount,null NetAmount, null CashCredit, null GrossAmount,
    avg(Sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 1 preceding and current row) as movingaverageday,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 6 preceding and current row) as movingaverage7day,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 90 preceding and current row) as movingaverage90day,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 365 preceding and current row) as movingaverage365day,
    null MovingAverage3month, null MovingAverage12month,
    alldata.OrderDate,null Cash, null Credit, null Food, null Beverage, null Other,'00:00-01:00' as HourOfDay,
    null Discount, null DiscountAmount,null DiscountQuantity,null CheckCount,null CustomerCount
    from (
    select o.id,
    o.revenuecentername,
    u.unit,
    u.name,
    o.netamount,
    convert(date, isnull(o.createdon at time zone 'UTC' at time zone u.timezone, t.dd)) as orderdate,
    o.createdon,
    u.timezone
    from (
    select dateadd(dd, -1 * n, @DtRef) as dd
    from dm18.[dbo].[fntally](1, 366) x ) t
    left join dm.[order] o
    on t.dd = convert(date, o.createdon )
    join dm.unit u
    on o.unit = u.unit
    where isnull(o.createdon, t.dd) >= dateadd(day, -365, @ToDate)
    and isnull(o.createdon, t.dd) <= @ToDate ) as alldata
    group by alldata.revenuecentername,
    alldata.unit,
    alldata.name,
    alldata.orderdate ) as movingaverages
    where movingaverages.orderdate >= @FromDate
    and movingaverages.orderdate <= @ToDate

    i figured i need to write another one for monthly averages beacause of different granularity.  So far this has reduced my code to about 30 %, that is big already.

  • Sezam wrote:

    ... or should I post the rest of my code, on how to inlcude this in there?

    The code posted seems to have issues imo.  Can't be certain tho.  For example, in the partition ranges of the windowing functions you have:

                avg(Sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 1 preceding and  current row)   as movingaverageday,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 6 preceding and current row) as movingaverage7day,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 90 preceding and current row) as movingaverage90day,
    avg(sum(alldata.netamount)) over(partition by alldata.revenuecentername, alldata.unit, alldata.name order by alldata.orderdate rows between 365 preceding and current row) as movingaverage365day,

    1 preceding and current row = 2 rows

    6 preceding and current row = 7 rows

    90 preceding and current row = 91 rows

    365 preceding and current row = 366 rows

    All are correct?  Also, the function nests SUM within AVG OVER and uses GROUP BY  '..., orderdate' which seems to imply the dubious intention to sum the data before taking the average over those sums.  Can't be certain without the expected output.  Other issues too

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • You are right about date ranges, I'll fix them in final version, similar to 7 days range. Ideally, Daily averages should have calculated beyond the date limit, all time daily average, but I'm not sure I can do that with this query, but its not a big deal. Important is 7, 90, 365 days to work correct for now. Which I have not fully tested yet still how this works, if some days there were no sales..

    For the summing before the average its correct. In table data is order level, I cant display it right if data is calculating average for every order. But with current query I have one row per day per center, to display in report, and ssrs forces to use some sort of aggregation on incoming numbers, so displaying average with sum works because i have 1 row anyway.

     

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

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