Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Displaying totals for data puled from January thru December

Posted on 2013-12-03
3
241 Views
Last Modified: 2013-12-04
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?
vb3.doc
0
Comment
Question by:mahpog
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39694443
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"
WITH
      CTE
      AS (
                  SELECT
                        a.srp_no
                      , @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 (
                              SELECT
                                    Value
                              FROM fn_Split(@locationlist, ',')
                        )
                        AND ( a.entry_dt >= DATEADD(YEAR, (@selyear - 1900), 0)
                        AND   a.entry_dt <  DATEADD(YEAR, (@selyear - 1899), 0)  )
                  GROUP BY
                        a.srp_no
            )
SELECT
      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
FROM CTE
UNION ALL
      SELECT
            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
ORDER BY
      sort_by
    , 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

select
     dateadd(year,(@selyear-1900),0)
  ,  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.
0
 

Author Comment

by:mahpog
ID: 39695737
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.
0
 

Author Closing Comment

by:mahpog
ID: 39695753
Argh... thorough.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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