locked
Need SQL help with month sequence in a year RRS feed

  • Question

  • Hi,

    I am creating a spreadsheet that I am hoping is going to look somewhat like this:

    Record Report for the year of 2010

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    January

    February

    March

    April

    May

    June

    July

    August

    Sept

    Oct

    Nov

    Dec

    New Records

    0

    3

    4

    2

    1

    4

    0

    5

    1

    3

    2

    5

    Open Records

    88

    154

    167

    147

    134

    138

    137

    130

    187

    138

    116

    161

    Closed Records

    148

    130

    50

    20

    64

    97

    102

    111

    142

    28

    42

    95

    My SQL JUST for January data is below:

    Declare @Year integer
    Set @Year = 2010 -- as you can see, this would be my parameter to pull the data, selecting just the "year" from the drop down menu on dialog box.

    Select distinct BaseRecordID, RecordStatusCode, RecordStatusTypeCode, OpenDate
    Into #tmpRecordStatus
    From BaseRecord

    Select cc.BaseRecordID, t.RecordStatusCode, t.RecordStatusTypeCode, cc.RecordDetailID, cc.RecordDetailStatusCode, cc.RecordDetailStatusTypeCode, t.OpenDate
    Into #tmpRecordDetailStatus
    From RecordDetail cc
    Join #tmpRecordStatus t on t.BaseRecordID = cc.BaseRecordID
    Where cc.BaseRecordID In (Select BaseRecordID from #tmpRecordStatus)
    and cc.BaseRecordIndicator = 1

    Select distinct t.RecordDetailID as OwnerID, t.BaseRecordID, t.OpenDate, sc.EffectiveDate, sc.StatusChangeID, sc.StatusCode, sc.StatusTypeCode,
    cdc.RecordStatusDescription, cds.RecordStatusTypeCodeDescription
    Into #tmpRecordDetailDescription
    From #tmpRecordDetailStatus t
    Join StatusChange sc on sc.OwnerID = t.RecordDetailID
    Join CoDescRecordStatusCodes cdc on cdc.RecordStatusCode = sc.StatusCode
    Join CoDescRecordStatusTypeCodes cds on cds.RecordStatusTypeCode = sc.StatusTypeCode
    Where EffectiveDate Between CONVERT(varchar(25), @year) + '-01-01' and CONVERT(varchar(25), @year) + '-01-31' -- as you can see here, I only code the date for January (from first date of the month to last date of the month)

    Select
    Case When StatusCode = 2 and OpenDate between CONVERT(varchar(25), @year) + '-01-01' and CONVERT(varchar(25), @year) + '-01-31' Then 'New Record' End as NewRecords,
    Case When StatusCode = 2 and OpenDate not between CONVERT(varchar(25), @year) + '-01-01' and CONVERT(varchar(25), @year) + '-01-31' Then 'Open Record' End as OpenRecords,
    Case When StatusCode = 3 then 'Closed Record' End as ClosedRecords,
    BaseRecordID, OpenDate, EffectiveDate, StatusCode, StatusTypeCode, RecordStatusDescription, RecordStatusTypeCodeDescription
    Into #tmpAllRecords
    From #tmpRecordDetailDescription
    Group by StatusCode, StatusTypeCode, OpenDate, BaseRecordID, EffectiveDate, RecordStatusDescription, RecordStatusTypeCodeDescription

    Select DateName(month,CONVERT(varchar(25), @year) + '-01-01') as 'Month', Year(CONVERT(varchar(25), @year) + '-01-01') as 'Year',
    Count(NewRecords) as NewRecords, Count(OpenRecords) as OpenRecords, Count(ClosedRecords) as ClosedRecords
    Into #tmpFinalAllRecords
    From #tmpAllRecords

    Select * from #tmpFinalAllRecords

    Drop Table #tmpRecordStatus
    Drop Table #tmpRecordDetailStatus
    Drop Table #tmpRecordDetailDescription
    Drop Table #tmpAllRecords
    Drop Table #tmpFinalAllRecords

    _____________________________________

    My result from that query for January is:

    Month                          Year        NewRecords     OpenRecords    ClosedRecords
    ------------------------------ ----------- ----------- ----------- -----------------------------
    January                       2010                   0                      88                     148

    And if I change the date to February (where I have given red color on the code where I hard coded the date for each month) – For example:
    Between CONVERT(varchar(25), @year) + '-02-01' and CONVERT(varchar(25), @year) + '-02-28'

    Then I will get this result below for February:

    Month                          Year        NewRecords     OpenRecords    ClosedRecords
    ------------------------------ ----------- ----------- ----------- -----------------------------
    February                       2010                   3                      154                     130

    Like how it’s shown in the table above (I have manually run for every month by changing the dates and putting the result in the table).

    My question is how do you code it so it will show all the result for all the months in the year - one full year (for example you put in the year of '2009' as your paramater) without having to manually change the dates (for first date of the month and last date of the month) for that year? And automatically calculate the records/data for the next month after that and continue on for the rest of the year or to list months in sequence (first day of the month to last day of the month, from January to December with just putting the “year” parameter in).

    Can anyone help me with this? Thank you so much. Your help would be much appreciated.

    ~Stephanie

    Friday, April 2, 2010 5:30 PM

Answers

  • Ok, here is a direct translation - may be it could have been simplified in some steps:

    -- Test query
    
     Select month(EffectiveDate) AS EffMonth
            ,t.OpenDate
            ,sc.StatusCode
      into #BaseData      
      From #tmpRecordDetailStatus t
      Join StatusChange sc on sc.OwnerID = t.RecordDetailID
      Join CoDescRecordStatusCodes cdc on cdc.RecordStatusCode = sc.StatusCode
      Join CoDescRecordStatusTypeCodes cds on cds.RecordStatusTypeCode = sc.StatusTypeCode
      Where EffectiveDate>=STR(@Year,4)+'0101' and EffectiveDate<=STR(@Year,4)+'1231'
    
      Select left(datename(month,dateadd(month,EffMonth-1,'19000101')),3) as MonthName
            ,sum(Case When StatusCode = 2 and month(OpenDate)=EffMonth Then 1 End) as [New Records]
            ,sum(Case When StatusCode = 2 and month(OpenDate)<>EffMonth Then 1 End) as [Open Records]
            ,sum(Case When StatusCode = 3 then 1 End) as [Closed Records]
      into #MonthSmry      
      from #BaseData
      group by left(datename(month,dateadd(month,EffMonth-1,'19000101')),3)
    
      select MonthName,
            [New Records] as Description
            ,Amount
      into #MonthCatgs      
      from #MonthSmry
      union all
      select MonthName,
            [Open Records] as Description
            ,Amount
      from #MonthSmry
      union all
      select MonthName,
            [Closed Records] as Description
            ,Amount
      from #MonthSmry
      
    
    select Description
          ,sum(case when MonthName ='Jan' then Amount end) as Jan,
          ,sum(case when MonthName ='Feb' then Amount end) as Feb,
          ,sum(case when MonthName ='Mar' then Amount end) as Mar,
          etc.
    from #MonthCatgs group by Description
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Brad_Schulz Tuesday, April 6, 2010 2:27 PM
    • Marked as answer by Hopsie288 Tuesday, April 6, 2010 5:58 PM
    • Marked as answer by Hopsie288 Tuesday, April 6, 2010 5:58 PM
    Tuesday, April 6, 2010 2:05 PM

All replies

  • Hello Stephanie,

    You would need to use pivot for doing that. Try rewrite the query using Pivot. See the below example. If you want any more help, please reply.

    SELECT *
    FROM (
      SELECT
        YEAR(OrderDate) [Year],
        MONTH(OrderDate) [Month],
        SubTotal
      FROM Sales.SalesOrderHeader
    ) TableDate
    PIVOT (
      SUM(SubTotal)
      FOR [Month] IN (
        [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
      )
    ) PivotTable

    Bins
    Friday, April 2, 2010 7:32 PM
  • Hopefully you have SQL2005...

    The code below should produce what you're looking for once you create the #tmpRecordDetailStatus temp table.  This is just off the top of my head... it's untested.  Again, this uses PIVOT and CTE's which are only available in SQL2005 and not in SQL2000:

    ;with BaseData as
    (
      Select month(EffectiveDate) AS EffMonth
            ,t.OpenDate
            ,sc.StatusCode
      From #tmpRecordDetailStatus t
      Join StatusChange sc on sc.OwnerID = t.RecordDetailID
      Join CoDescRecordStatusCodes cdc on cdc.RecordStatusCode = sc.StatusCode
      Join CoDescRecordStatusTypeCodes cds on cds.RecordStatusTypeCode = sc.StatusTypeCode
      Where EffectiveDate>=STR(@Year,4)+'0101' and EffectiveDate<=STR(@Year,4)+'1231'
    )
    ,MonthSmry as
    (
      Select left(datename(month,dateadd(month,EffMonth-1,'19000101')),3) as MonthName
            ,sum(Case When StatusCode = 2 and month(OpenDate)=EffMonth Then 1 End) as [New Records]
            ,sum(Case When StatusCode = 2 and month(OpenDate)<>EffMonth Then 1 End) as [Open Records]
            ,sum(Case When StatusCode = 3 then 1 End) as [Closed Records]
      from BaseData
      group by left(datename(month,dateadd(month,EffMonth-1,'19000101')),3)
    )
    ,MonthCatgs as
    (
      select MonthName
            ,Description
            ,Amount
      from MonthSmry
      unpivot (Amount for Description in ([New Records],[Open Records],[Closed Records])) P
    )
    select Description
          ,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
    from MonthCatgs
    pivot (sum(Amount) for MonthName in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) P
    

    The BaseData CTE gathers all the data for the entire year and adds a column for the month number of the Effective Date.

    The MonthSmry CTE counts all the categories and GROUPs BY the 3-letter month name.

    The MonthCatgs CTE UNPIVOTs the 3 sum columns into rows.

    And the final SELECT PIVOTs the data for the months in 12 separate columns.

     


    --Brad (My Blog)
    • Edited by Brad_Schulz Friday, April 2, 2010 8:13 PM Code Change
    Friday, April 2, 2010 7:59 PM
  • Hi,

    Thank you for your replies... unfortunately I am using SQL 2000 right now.... do you guys have any other idea or other approach that will work for SQL 2000? Thanks for all your help! I appreciate it....

    Tuesday, April 6, 2010 1:40 AM
  • It's easy enough to translate Brad's code into SQL 2000, though I think we may want to use temp table for simplicity - can do all the job with derived tables, but would be a bit too complex.

    Do you need help translating or would be able to do it yourself?

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 6, 2010 1:48 AM
  • If you dont mind, yes I need help translating the approach you had using SQL 2000. Thank you in advance!

    Tuesday, April 6, 2010 12:41 PM
  • Ok, here is a direct translation - may be it could have been simplified in some steps:

    -- Test query
    
     Select month(EffectiveDate) AS EffMonth
            ,t.OpenDate
            ,sc.StatusCode
      into #BaseData      
      From #tmpRecordDetailStatus t
      Join StatusChange sc on sc.OwnerID = t.RecordDetailID
      Join CoDescRecordStatusCodes cdc on cdc.RecordStatusCode = sc.StatusCode
      Join CoDescRecordStatusTypeCodes cds on cds.RecordStatusTypeCode = sc.StatusTypeCode
      Where EffectiveDate>=STR(@Year,4)+'0101' and EffectiveDate<=STR(@Year,4)+'1231'
    
      Select left(datename(month,dateadd(month,EffMonth-1,'19000101')),3) as MonthName
            ,sum(Case When StatusCode = 2 and month(OpenDate)=EffMonth Then 1 End) as [New Records]
            ,sum(Case When StatusCode = 2 and month(OpenDate)<>EffMonth Then 1 End) as [Open Records]
            ,sum(Case When StatusCode = 3 then 1 End) as [Closed Records]
      into #MonthSmry      
      from #BaseData
      group by left(datename(month,dateadd(month,EffMonth-1,'19000101')),3)
    
      select MonthName,
            [New Records] as Description
            ,Amount
      into #MonthCatgs      
      from #MonthSmry
      union all
      select MonthName,
            [Open Records] as Description
            ,Amount
      from #MonthSmry
      union all
      select MonthName,
            [Closed Records] as Description
            ,Amount
      from #MonthSmry
      
    
    select Description
          ,sum(case when MonthName ='Jan' then Amount end) as Jan,
          ,sum(case when MonthName ='Feb' then Amount end) as Feb,
          ,sum(case when MonthName ='Mar' then Amount end) as Mar,
          etc.
    from #MonthCatgs group by Description
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Brad_Schulz Tuesday, April 6, 2010 2:27 PM
    • Marked as answer by Hopsie288 Tuesday, April 6, 2010 5:58 PM
    • Marked as answer by Hopsie288 Tuesday, April 6, 2010 5:58 PM
    Tuesday, April 6, 2010 2:05 PM
  • Thanks, Naomi.
    --Brad (My Blog)
    Tuesday, April 6, 2010 2:27 PM
  • Thank you for your help, Naomi.

    Tuesday, April 6, 2010 5:58 PM