Solved

Converting Vertical Data to Horizontal Data through Aggregation

Posted on 2004-04-20
4
702 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:billy21
  • 2
  • 2
4 Comments
 
LVL 6

Author Comment

by:billy21
ID: 10867633
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

0
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 10867741
what about

Create Function dbo.SOSCurrentVary(@StartDate DateTime)
Returns Table
AS
Return
     Select Va.SOSID,
     Max(Case When Va.VDate = @StartDate Then Units End) CurUnits1,
     Max(Case When Va.VDate = @StartDate+1 Then Units End) CurUnits2,
     Max(Case When Va.VDate = @StartDate+2 Then Units End)  CurUnits3,
     Max(Case When Va.VDate = @StartDate+3 Then Units End)  CurUnits4,
     Max(Case When Va.VDate = @StartDate+4 Then Units End)  CurUnits5,
     Max(Case When Va.VDate = @StartDate+5 Then Units End)  CurUnits6,
     Max(Case When Va.VDate = @StartDate+6 Then Units End)  CurUnits7
     From SOSVary Va
     Group By Va.SOSID
)
0
 
LVL 6

Author Comment

by:billy21
ID: 10867846
Thanks Hilare you're a champion!
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10867864
Glad to help and thks for the grade !
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now