September 20, 2022 at 10:25 pm
Hello, If someone can help me out with the below problem I'm having I would really appreciate it. Thank you!
I have one order table and one calendar table;
Orders Table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Orders] Script Date: 20/09/2022 22:43:51 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [nchar](5) NULL,
[EmployeeID] [int] NULL,
[OrderDate] [datetime] NULL,
[RequiredDate] [datetime] NULL,
[ShippedDate] [datetime] NULL,
[ShipVia] [int] NULL,
[Freight] [money] NULL,
[ShipName] [nvarchar](40) NULL,
[ShipAddress] [nvarchar](60) NULL,
[ShipCity] [nvarchar](15) NULL,
[ShipRegion] [nvarchar](15) NULL,
[ShipPostalCode] [nvarchar](10) NULL,
[ShipCountry] [nvarchar](15) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Freight] DEFAULT ((0)) FOR [Freight]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([ShipVia])
REFERENCES [dbo].[Shippers] ([ShipperID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Shippers]
GO
Calendar Table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Calendar] Script Date: 20/09/2022 22:45:19 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Calendar](
[CalendarDate] [datetime] NULL
) ON [PRIMARY]
GO
Example of data attached as image.
For this exercise I'm only interested in the OrderDate and Freight columns from the Orders Table and the CalendarDate column from the Calendar table (I only want to use the calendar table to get dates that are missing from the OrderDate column).
What I'm trying to do is get the weekly average based on the previous 12 months and as I understand it this would be calculated by the following:
To be clear my objective is to calculate the above for each and every row.
The issue I'm having is I don't know how to put all these steps together. Below is as far as I managed to get on my own, however needless to say it is incorrect as it only takes into account the previous 6 rows and current row.
SELECT
*,
SUM(DailyAmount) OVER (ORDER BY orderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WeeklyAverageAmount
FROM (
SELECT
orderDate,
SUM(freight) AS DailyAmount
FROM [Northwind].[dbo].[Orders]
GROUP BY
orderdate
) ByDay;
September 20, 2022 at 10:43 pm
Where are you doing your reporting? If you're doing it in Excel or PowerBI, use DAX, not T-SQL to calculate it.
September 20, 2022 at 10:48 pm
I'm using SSRS
September 20, 2022 at 11:21 pm
If you're going to consume this in SSRS, I'd write it in a stored procedure.
Look at windowing functions. You can expand the time window....
SELECT DimDate.Date, AVG([Amount]) OVER (PARTITION BY Date ORDER BY Date ROWS BETWEEN 365 PRECEDING AND CURRENT ROW)
or something similar.
September 21, 2022 at 7:43 pm
I think you need to start over. Google why we never use MONEY. Normalize this mess. neither table has a key at all. A calendar has an obvious key -- the date. Please learn ISO-8601 Standard dates. In particular the weekly option is perfect for this. The usual display is yyyy-mm-dd and it is the only one in the ANSI/ISO SQL Standards. The Nordic countries also use yyyyWnn-d, which is the year, W, the week within the year (01 to 52 or 53), dash and day number within the week ( 1 to 7). You can download this as a second unique column in your Calendar table off of the internet. Store it as a strlng and cut it up as needed.
Why do you think a shipment is attribute? It is a relationship among an order, a customer and a shipper. You do not think in RDBMS yet, so this posting is a paper form wrtten in bad SQL and not a database at all.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 21, 2022 at 8:13 pm
I think you need to start over. Google why we never use MONEY. Normalize this mess. neither table has a key at all. A calendar has an obvious key -- the date. Please learn ISO-8601 Standard dates. In particular the weekly option is perfect for this. The usual display is yyyy-mm-dd and it is the only one in the ANSI/ISO SQL Standards. The Nordic countries also use yyyyWnn-d, which is the year, W, the week within the year (01 to 52 or 53), dash and day number within the week ( 1 to 7). You can download this as a second unique column in your Calendar table off of the internet. Store it as a strlng and cut it up as needed.
Why do you think a shipment is attribute? It is a relationship among an order, a customer and a shipper. You do not think in RDBMS yet, so this posting is a paper form wrtten in bad SQL and not a database at all.
Since you're the expert on that, show us the code that would use that standard to solve this issue in TG-SQL, please.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply