Solved

Converting Vertical Data to Horizontal Data through Aggregation

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

929 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

10 Experts available now in Live!

Get 1:1 Help Now