billy21
asked on
Converting Vertical Data to Horizontal Data through Aggregation
The following query does a good job of converting my rows to columns. It works because There can only be either zero or one records for a combination of Va.VDate and Va.SOSID. These two fields make my primary key. The problem I am having is that reporting zero when a record doesn't exist is not acceptable. I somehow need this query to return NULL if no record exists for a given date and sosid combination. I also can't assume that if the query returns zero the record doesn't exist because there could be a record with units value of zero.
Create Function dbo.SOSCurrentVary(@StartD ate DateTime)
Returns Table
AS
Return
Select Va.SOSID,
Max(Case When Va.VDate = @StartDate Then IsNull(Units,0) Else 0 End) CurUnits1,
Max(Case When Va.VDate = @StartDate+1 Then IsNull(Units,0) Else 0 End) CurUnits2,
Max(Case When Va.VDate = @StartDate+2 Then IsNull(Units,0) Else 0 End) CurUnits3,
Max(Case When Va.VDate = @StartDate+3 Then IsNull(Units,0) Else 0 End) CurUnits4,
Max(Case When Va.VDate = @StartDate+4 Then IsNull(Units,0) Else 0 End) CurUnits5,
Max(Case When Va.VDate = @StartDate+5 Then IsNull(Units,0) Else 0 End) CurUnits6,
Max(Case When Va.VDate = @StartDate+6 Then IsNull(Units,0) Else 0 End) CurUnits7
From SOSVary Va
Group By Va.SOSID
)
Go
The most important consideration here is efficiency. It is not an option to make 7 joins or use 7 exists statements (which will result in 7 joins anyway)
I'm going to post an additional question asking how I can make this same query perform a clustered index seek as opposed to a clustered index scan. My clustered index is made up of my primary key fields (SOSID, Vdate).
Thanks in advance,
Billy
Create Function dbo.SOSCurrentVary(@StartD
Returns Table
AS
Return
Select Va.SOSID,
Max(Case When Va.VDate = @StartDate Then IsNull(Units,0) Else 0 End) CurUnits1,
Max(Case When Va.VDate = @StartDate+1 Then IsNull(Units,0) Else 0 End) CurUnits2,
Max(Case When Va.VDate = @StartDate+2 Then IsNull(Units,0) Else 0 End) CurUnits3,
Max(Case When Va.VDate = @StartDate+3 Then IsNull(Units,0) Else 0 End) CurUnits4,
Max(Case When Va.VDate = @StartDate+4 Then IsNull(Units,0) Else 0 End) CurUnits5,
Max(Case When Va.VDate = @StartDate+5 Then IsNull(Units,0) Else 0 End) CurUnits6,
Max(Case When Va.VDate = @StartDate+6 Then IsNull(Units,0) Else 0 End) CurUnits7
From SOSVary Va
Group By Va.SOSID
)
Go
The most important consideration here is efficiency. It is not an option to make 7 joins or use 7 exists statements (which will result in 7 joins anyway)
I'm going to post an additional question asking how I can make this same query perform a clustered index seek as opposed to a clustered index scan. My clustered index is made up of my primary key fields (SOSID, Vdate).
Thanks in advance,
Billy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Hilare you're a champion!
Glad to help and thks for the grade !
ASKER
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SOSVary
drop table [dbo].[SOSVary]
GO
CREATE TABLE [dbo].[SOSVary] (
[SOSID] [int] NOT NULL ,
[VDate] [datetime] NOT NULL ,
[Units] [smallmoney] NOT NULL ,
[VariationReason] [int] NOT NULL ,
[VariationNote] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_
[VariedBySPNo] [int] NOT NULL ,
[VariedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SOSVary] WITH NOCHECK ADD
CONSTRAINT [PK_SOSVary] PRIMARY KEY CLUSTERED
(
[SOSID],
[VDate]
) WITH FILLFACTOR = 60 ON [PRIMARY]
GO
ALTER TABLE [dbo].[SOSVary] ADD
CONSTRAINT [DF__SOSVary__Units__52DAC
CONSTRAINT [DF__SOSVary__Variati__53C
CONSTRAINT [DF__SOSVary__VariedD__54C
GO