Thread: Advanced Query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0

    Angry Advanced Query


    Forgive me for any newbee blunders.
    I have started a query and am running into many problems finishing this. Any assistance would be greatly appreciated.
    Using a table with over 2 million records, Monthly sales numbers by month over the last 36 months. The task is to compare 2 aggregated records for 12 month periods (last 12 months compared to the 12 months before that - [last year compared to the year before]) and do this on a monthly rolling basis for the last 36 months.

    Here is what I started with, expecting to use a temptable in the first round then pull from that to run calculations.

    IF OBJECT_ID('tempdb.dbo.#TMPTBLSCO') IS NOT NULL
    DROP TABLE #TMPTBLSCO
    SELECT
    B.f1
    ,B.f2
    ,CASE
    WHEN B.f3 = 'BEC' THEN 'ECO'
    WHEN B.f3 = 'BSO' THEN 'OTR'
    ELSE 'KSR'
    END AS f4
    ,B.f5
    ,CASE
    WHEN SUM(B.f6) BETWEEN 1 AND 12 THEN 1
    WHEN B.f6 BETWEEN 2 AND 13 THEN 2
    WHEN B.f6 BETWEEN 3 AND 14 THEN 3
    WHEN B.f6 BETWEEN 4 AND 15 THEN 4
    WHEN B.f6 BETWEEN 5 AND 16 THEN 5
    WHEN B.f6 BETWEEN 6 AND 17 THEN 6
    END AS f7
    ,SUM(CASE WHEN B.f6 BETWEEN 1 AND 12 THEN B.f8 ELSE 0 END) AS a1
    ,SUM(CASE WHEN B.f6 BETWEEN 2 AND 13 THEN B.f8 ELSE 0 END) AS a2
    --INTO #TMPTBLSCO
    FROM dbo.SCO_REF002_BRAND_SLS AS B
    GROUP BY B.f1, B.f2, B.f3, B.f5
    ORDER BY B.f2, B.f5
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    you neglected to mention what happens when you run that query, but i'm gonna guess it doesn't even run

    you have this --

    ,CASE
    WHEN SUM(B.f6) BETWEEN 1 AND 12 THEN 1
    WHEN B.f6 BETWEEN 2 AND 13 THEN 2
    ...

    and that's not going to work since f6 isn't in the GROUP BY

    you also say "the task is to compare 2 aggregated records for 12 month periods" but i don't see anything that identifies the 2 aggregated records, nor selects any 12 month period
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0
    Sorry, I did fail to mention that it fails because F6 is not in the group. And I can group on F6 because that is the Identifier for the number of months back (1-36).
    As you can see it was also unfinished, which is the way I progress, get a little bit to work and build from that...

    OH WOOPPS I did not realize that I left that SUM(F6) in there, I started thinking about that and realized it would not provide whatI was looking for.. just forgot to remove it..

    I was planning to build from this the do the comparison. This
    ,SUM(CASE WHEN B.f6 BETWEEN 1 AND 12 THEN B.f8 ELSE 0 END) AS a1
    ,SUM(CASE WHEN B.f6 BETWEEN 2 AND 13 THEN B.f8 ELSE 0 END) AS a2
    Was the first part of building the fields to be compared. Following this progression, F13 would be compared to F1, and so on.

IMN logo majestic logo threadwatch logo seochat tools logo