Reputation: 63
Pivoting Data in a meaningful way
SQL Server 2016
I was given an interesting table structure and am being asked to make some meaningful reporting off of it to see growth change and need help in how to go about pivoting the data for a result set that will be easier to work with in SSRS.
Table Structure:
CREATE TABLE [dbo].[Person_Order_ETL_Delay](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Person_Name] [varchar](255) NULL,
[Order_DATE] [date] NOT NULL,
[Order_INTERVAL] [char](5) NOT NULL,
[00:00-00:30] [int] NOT NULL,
[00:30-01:00] [int] NOT NULL,
[01:00-01:30] [int] NOT NULL,
[01:30-02:00] [int] NOT NULL,
[02:00-02:30] [int] NOT NULL,
[02:30-03:00] [int] NOT NULL,
[03:00-03:30] [int] NOT NULL,
[03:30-04:00] [int] NOT NULL,
[04:00-04:30] [int] NOT NULL,
[04:30-05:00] [int] NOT NULL,
[05:00-05:30] [int] NOT NULL,
[05:30-06:00] [int] NOT NULL,
[06:00-06:30] [int] NOT NULL,
[06:30-07:00] [int] NOT NULL,
[07:00-07:30] [int] NOT NULL,
[07:30-08:00] [int] NOT NULL,
[08:00-08:30] [int] NOT NULL,
[08:30-09:00] [int] NOT NULL,
[09:00-09:30] [int] NOT NULL,
[09:30-10:00] [int] NOT NULL,
[10:00-10:30] [int] NOT NULL,
[10:30-11:00] [int] NOT NULL,
[11:00-11:30] [int] NOT NULL,
[11:30-12:00] [int] NOT NULL,
[12:00-12:30] [int] NOT NULL,
[12:30-13:00] [int] NOT NULL,
[13:00-13:30] [int] NOT NULL,
[13:30-14:00] [int] NOT NULL,
[14:00-14:30] [int] NOT NULL,
[14:30-15:00] [int] NOT NULL,
[15:00-15:30] [int] NOT NULL,
[15:30-16:00] [int] NOT NULL,
[16:00-16:30] [int] NOT NULL,
[16:30-17:00] [int] NOT NULL,
[17:00-17:30] [int] NOT NULL,
[17:30-18:00] [int] NOT NULL,
[18:00-18:30] [int] NOT NULL,
[18:30-19:00] [int] NOT NULL,
[19:00-19:30] [int] NOT NULL,
[19:30-20:00] [int] NOT NULL,
[20:00-20:30] [int] NOT NULL,
[20:30-21:00] [int] NOT NULL,
[21:00-21:30] [int] NOT NULL,
[21:30-22:00] [int] NOT NULL,
[22:00-22:30] [int] NOT NULL,
[22:30-23:00] [int] NOT NULL,
[23:00-23:30] [int] NOT NULL,
[23:30-00:00] [int] NOT NULL
) ON [PRIMARY]
GO
Table logic: This table will represent ETL activity of the target application vs the availability in the source application.
Let's take ID = 12 as an example. On 4/1/2020 the source application is taking Bilbo's orders and processing them at the order interval of 05:30 (system time [Order_INTERVAL]) and what we are seeing is that the data was made available for the target reporting application (via ETL time stamps) between [06:00-06:30] (6 volumes finally able to be captured) and [06:30-07:00] (3 more volumes processed and available for ETL).
So we have a source to target delay.
The system interval has appropriated these volumes (6+3=9 total orders) for the 05:30 time interval, however, the target system was not able to capture them until the 06:00 interval and the remainder at the 06:30 interval for the target reporting application.
So this proves that there is some delay between transactions completing, and their availability for the reporting application.
What I would like to do is produce a result set off of this to represent the data more like this:
Pivoting pertinent ETL interval data, displaying the volume and the percentage over total volume. Pertinent meaning, the floor of the volume should be the source application interval bucket for the volume and the ceiling would be the last ETL bucket in the series that contains a volume greater than 0.
Any help with this would be outstanding.
Upvotes: 1
Views: 49
