• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Displaying totals for data puled from January thru December

I have a complete annual  report minus totals at end. I attempted to use another sql query to generate totals for  'Literal14.Text = GenerateTotal()  (see attached) without success. I deleted it out.  I attempted to create temp variable table (sql 2008) - but not sure I can delcare and insert into @temp ???) - - Tried but got sql unhandled exceptions.

Is there a way to use datarows, data columns, or any other technique to store the counts for each dataRow I defined for Jan- dec and then display at end?
  • 2
1 Solution
I would suggest a rethinking of the SQL query.

It's possible to arrange the results into 12 monthly columns using case expressions. Plus, this can be performed as a "common table expression" (CTE) which allows reuse of the calculations performed; and we reuse them to produce the column totals.

I have also replaced the use of "between" with a better method,  see: "Beware of Between"
      AS (
                      , @selyear                                                  AS selyear
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 1 THEN a.srp_no END)  AS month_1
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 2 THEN a.srp_no END)  AS month_2
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 3 THEN a.srp_no END)  AS month_3
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 4 THEN a.srp_no END)  AS month_4
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 5 THEN a.srp_no END)  AS month_5
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 6 THEN a.srp_no END)  AS month_6
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 7 THEN a.srp_no END)  AS month_7
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 8 THEN a.srp_no END)  AS month_8
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 9 THEN a.srp_no END)  AS month_9
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 10 THEN a.srp_no END) AS month_10
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 11 THEN a.srp_no END) AS month_11
                      , COUNT(CASE WHEN MONTH(a.entry_dt) = 12 THEN a.srp_no END) AS month_12
                  FROM asrp_usage a
                  WHERE a.frp_ror = '1'
                        AND a.bus_type_gc = 'G'
                        AND a.location IN (
                              FROM fn_Split(@locationlist, ',')
                        AND ( a.entry_dt >= DATEADD(YEAR, (@selyear - 1900), 0)
                        AND   a.entry_dt <  DATEADD(YEAR, (@selyear - 1899), 0)  )
                  GROUP BY
      0 AS sort_by
    , a.srp_no
    , selyear
    , month_1
    , month_2
    , month_3
    , month_4
    , month_5
    , month_6
    , month_7
    , month_8
    , month_9
    , month_10
    , month_11
    , month_12
            1             AS sort_by
          , NULL          AS srp_no
          , NULL          AS selyear
          , SUM(month_1)  AS month_1
          , SUM(month_2)  AS month_2
          , SUM(month_3)  AS month_3
          , SUM(month_4)  AS month_4
          , SUM(month_5)  AS month_5
          , SUM(month_6)  AS month_6
          , SUM(month_7)  AS month_7
          , SUM(month_8)  AS month_8
          , SUM(month_9)  AS month_9
          , SUM(month_10) AS month_10
          , SUM(month_11) AS month_11
          , SUM(month_12) AS month_12
      FROM CTE
    , srp_no

Open in new window

Note instead of '01/01/@selyear' etc. I use of dateadd() ;Those calculations produce Jan 1 of the chosen year, and Jan 1 of the next year, like so:
declare @selyear int = 2012

  ,  dateadd(year,(@selyear-1899),0)

|                       COLUMN_0 |                       COLUMN_1 |
| January, 01 2012 00:00:00+0000 | January, 01 2013 00:00:00+0000 |

Open in new window

By the way, it isn't necessary to also do this "AND YEAR(a.entry_dt) = r.selyear" if you are choosing only dates >= "Jan 1 chosen year" and < "Jan 1 next year"

I hope the above makes sense. I guess I should point out that the output of the suggested query mimics the 12 column layout, so your ASP code would need to be amended to suit.
mahpogAuthor Commented:
wow, that is some code. I know about CTE, but wow.   Date range was good technique. Lot to learn from this. going to take some time.
mahpogAuthor Commented:
Argh... thorough.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now