May 23, 2024 at 8:50 am
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
May 23, 2024 at 1:23 pm
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
May 24, 2024 at 10:19 am
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?
May 27, 2024 at 8:38 am
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.
May 27, 2024 at 2:09 pm
... 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
May 28, 2024 at 3:24 am
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