Solved

Converting Vertical Data to Horizontal Data through Aggregation

Posted on 2004-04-20
4
707 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

831 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