Link to home
Start Free TrialLog in
Avatar of billy21
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(@StartDate 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
Avatar of billy21
billy21

ASKER

Table strucure is as follows:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SOSVary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
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_AS NULL ,
      [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__52DAC1AA] DEFAULT (0) FOR [Units],
      CONSTRAINT [DF__SOSVary__Variati__53CEE5E3] DEFAULT (0) FOR [VariationReason],
      CONSTRAINT [DF__SOSVary__VariedD__54C30A1C] DEFAULT (getdate()) FOR [VariedDate]
GO

ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of billy21

ASKER

Thanks Hilare you're a champion!
Glad to help and thks for the grade !