locked
Calculate counts for a field based on set conditions RRS feed

  • Question

  • Hi, I am trying to calculate the total noon counts if I have counts at certain time intervals. Please find code below.

    CREATE TABLE [#Coefficient]( [Description] [varchar](100) NOT NULL, [Week] [int] NOT NULL, [Year] [int] NOT NULL, [TimeOfDay] [float] NOT NULL, [Day1] [int] NOT NULL, [Day2] [int] NOT NULL, [Day3] [int] NOT NULL, [Day4] [int] NOT NULL, [Day5] [int] NOT NULL, [Day6] [int] NOT NULL, [Day7] [int] NOT NULL, [Total] [int] NOT NULL, [NoonCount] [int] NULL ) INSERT INTO [#Coefficient] ([Description] ,[Week] ,[Year] ,[TimeOfDay] ,[Day1] ,[Day2] ,[Day3] ,[Day4] ,[Day5] ,[Day6] ,[Day7] ,[Total], [NoonCount]) SELECT 'Actual',37,2009,5,0,0,0,0,0,0,0,0,0 UNION ALL SELECT 'Actual',37,2009,10,0,0,0,0,0,149,0,149,154 UNION ALL SELECT 'Actual',37,2009,12,52,64,94,147,123,154,116,750,750 UNION ALL SELECT 'Actual',37,2009,14,0,44,0,0,0,132,0,176,218 UNION ALL SELECT 'Actual',37,2009,16,0,41,0,0,0,97,0,138,218 UNION ALL SELECT 'Actual',37,2009,22,0,0,0,0,0,0,0,0,0 SELECT * FROM [#Coefficient]

    "NoonCount" is the field I am trying to get the counts for. The above code has desired output for "NoonCount".

    For intance: Counts at 10 am has value only for Day6 so I want to get back the corresponding noon count which is 154; at 2 pm I have counts for Day2 & Day6 so the noon count for 2 pm would be 64 + 154 = 218.

    Thanks in advance............

    Tuesday, January 8, 2013 10:05 PM

Answers

  • So the temp table is the result of a dynamic pivot?

    You should always do your pivot the last thing you do. You should make the calculations before you do the pivoting. Or maybe as part of the pivoting.

    If you make calculations on the pivoted data, it will be horriby painful, because in a relational database you are supposed to compute over rows, not over columns. (Because a column is supposed to represent a unique attribute, so it would not make sense.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, January 10, 2013 1:54 AM
    • Marked as answer by Iric Wen Thursday, January 17, 2013 8:55 AM
    Wednesday, January 9, 2013 10:45 PM

All replies

  • I assume that #Coefficient is the result of some earlier calculation, as it looks a bit denormalised. Anyway:

    CREATE TABLE [#Coefficient](
       [Description] [varchar](100) NOT NULL,
       [Week] [int] NOT NULL,
       [Year] [int] NOT NULL,
       [TimeOfDay] [float] NOT NULL,
       [Day1] [int] NOT NULL,
       [Day2] [int] NOT NULL,
       [Day3] [int] NOT NULL,
       [Day4] [int] NOT NULL,
       [Day5] [int] NOT NULL,
       [Day6] [int] NOT NULL,
       [Day7] [int] NOT NULL,
       [Total] [int] NOT NULL,
       [NoonCount] [int] NULL,
       NoonCount2 int

    )
    INSERT INTO [#Coefficient] ([Description] ,[Week] ,[Year] ,[TimeOfDay] ,[Day1] ,[Day2] ,[Day3] ,[Day4] ,[Day5] ,[Day6] ,[Day7] ,[Total], [NoonCount]) 
    SELECT 'Actual',37,2009,5,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 'Actual',37,2009,10,0,0,0,0,0,149,0,149,154 UNION ALL
    SELECT 'Actual',37,2009,12,52,64,94,147,123,154,116,750,750 UNION ALL
    SELECT 'Actual',37,2009,14,0,44,0,0,0,132,0,176,218 UNION ALL
    SELECT 'Actual',37,2009,16,0,41,0,0,0,97,0,138,218 UNION ALL
    SELECT 'Actual',37,2009,22,0,0,0,0,0,0,0,0,0

    UPDATE a
    SET    NoonCount2 = CASE WHEN a.Day1 <> 0 THEN b.Day1 ELSE 0 END +
                        CASE WHEN a.Day2 <> 0 THEN b.Day2 ELSE 0 END +
                        CASE WHEN a.Day3 <> 0 THEN b.Day3 ELSE 0 END +
                        CASE WHEN a.Day4 <> 0 THEN b.Day4 ELSE 0 END +
                        CASE WHEN a.Day5 <> 0 THEN b.Day5 ELSE 0 END +
                        CASE WHEN a.Day6 <> 0 THEN b.Day6 ELSE 0 END +
                        CASE WHEN a.Day7 <> 0 THEN b.Day7 ELSE 0 END
    FROM   #Coefficient a
    JOIN   #Coefficient b ON a.Description = b.Description
                         AND a.Week        = b.Week
                         AND a.Year        = b.Year
                         AND b.TimeOfDay   = 12

    SELECT * FROM [#Coefficient]
    go
    DROP TABLE #Coefficient


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 8, 2013 11:09 PM
  • Hi Erland, Thanks very much for your response. You are correct #Coefficient Table is an intermediary resultset of an earlier calculation. The day fields in the Table are actually the dates in the week 37 of year 2009. How do we make the above query dynamic if the user were to select more than 1 week(Let's say 37 and 38).

    Regards...........

    Tuesday, January 8, 2013 11:58 PM
  • Not sure what you mean. I wrote the query so that it would work with multiple Descriptions, Years and Weeks. If the query does not work for you, please post an example that demonstrates what you want.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 9, 2013 8:43 AM
  • Hi Erland, What I meant was #Coefficient table is the resultset of a prior pivot that I am doing. If I pass in @StartWeek = 20, @EndWeek = 21, @Year = 2012. I get the resultset in the below manner.

    CREATE TABLE [#Coefficient](
    	[Description] [varchar](100) NOT NULL,
    	[Week] [int] NOT NULL,
    	[Year] [int] NOT NULL,
    	[TimeOfDay] [float] NOT NULL,
    	[Day1] [int] NOT NULL,
    	[Day2] [int] NOT NULL,
    	[Day3] [int] NOT NULL,
    	[Day4] [int] NOT NULL,
    	[Day5] [int] NOT NULL,
    	[Day6] [int] NOT NULL,
    	[Day7] [int] NOT NULL,
    	[Day8] [int] NOT NULL,
    	[Day9] [int] NOT NULL,
    	[Day10] [int] NOT NULL,
    	[Day11] [int] NOT NULL,
    	[Day12] [int] NOT NULL,
    	[Day13] [int] NOT NULL,
    	[Day14] [int] NOT NULL,	
    	[Total] [int] NOT NULL,
    	[NoonCount] [int] NULL)	
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,10,0,0,0,0,0,149,0,0,0,0,0,0,0,0,149,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,10,0,0,0,0,0,0,0,0,0,148,0,0,102,0,250,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,12,52,64,94,147,123,154,116,0,0,0,0,0,0,0,750,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,12,0,0,0,0,0,0,0,107,102,131,103,95,104,59,701,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,14,0,44,0,0,0,132,0,0,0,0,0,0,0,0,176,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,14,0,0,0,0,0,0,0,0,0,111,0,0,76,0,187,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,16,0,41,0,0,0,97,0,0,0,0,0,0,0,0,138,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,16,0,0,0,0,0,0,0,0,0,76,0,0,57,0,133,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',20,2012,22,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL)
    INSERT INTO [#Coefficient] ([Description],[Week],[Year],[TimeOfDay],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],[Day13],[Day14],[Total],[NoonCount])VALUES('Actual',21,2012,22,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL)
    SELECT * FROM [#Coefficient]
    ORDER BY 2, 4

    And Day1 - Day14 are actually dates from 05/07/2012 - 05/20/2012. Is there a way to do the update dynamically.

    Regards.......

    Wednesday, January 9, 2013 4:40 PM
  • So the temp table is the result of a dynamic pivot?

    You should always do your pivot the last thing you do. You should make the calculations before you do the pivoting. Or maybe as part of the pivoting.

    If you make calculations on the pivoted data, it will be horriby painful, because in a relational database you are supposed to compute over rows, not over columns. (Because a column is supposed to represent a unique attribute, so it would not make sense.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, January 10, 2013 1:54 AM
    • Marked as answer by Iric Wen Thursday, January 17, 2013 8:55 AM
    Wednesday, January 9, 2013 10:45 PM
  • Thanks Erland I will make changes to my code accordingly.


    Thursday, January 10, 2013 2:40 PM