# 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 Daysselect *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 movingaverageswhere 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 movingaverageswhere movingaverages.OrderDate >= @FromDate and movingaverages.OrderDate <= @ToDate`

Sample data:

`SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET IDENTITY_INSERT [dbo].[Order] ON GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOINSERT [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))GOSET IDENTITY_INSERT [dbo].[Order] OFFGOSET IDENTITY_INSERT [dbo].[Unit] ON GOINSERT [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')GOSET IDENTITY_INSERT [dbo].[Unit] OFFGO`
• 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) DDfrom 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.NameORDER BY alldata.OrderDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS MovingAverage7Day , AVG(SUM(alldata.NetAmount)) OVER(PARTITION BY alldata.RevenueCenterName   , alldata.Unit   , alldata.NameORDER 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.Uniton 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 movingaverageswhere movingaverages.OrderDate >= @FromDate and movingaverages.OrderDate <= @ToDateorder 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]

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 movingaverageswhere    movingaverages.orderdate >= @FromDateand      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