Post reply

Question.. maybe a dumb one lol

  • August 2, 2011 at 11:47 pm

    #242377

    Need help with sql.

    here's a sample of my table:

    Store #1 - 8/3/2011 8:02PM - Customer #1

    Store #1 - 8/3/2011 8:41PM - Customer #2

    Store #2 - 8/3/2011 8:31PM - Customer #1

    Store #2 - 8/3/2011 9:01PM - Customer #1

    Now, I need a sql statement that will give me the following resultset:

    Store #1 - 2 - 0

    Store #2 - 1 - 1

    The first column is the store number, the second is count from 8pm-9pm, and third column is count 9pm-10pm.

    what would be the best way to do this? obviously it's grouping but how would i get a count of records that fall in certain hours displayed in resultset columns?

    I will be using this on a SSRS report so i would need to drill down if needed.

  • August 2, 2011 at 11:59 pm

    #1362820

    DECLARE @Table TABLE

    (

    store_idINTEGER NOT NULL,

    visit_timeDATETIME NOT NULL,

    customer_idINTEGER NOT NULL

    )

    INSERT @Table

    (store_id, visit_time, customer_id)

    VALUES

    (1, '8/3/2011 8:02PM', 1),

    (1, '8/3/2011 8:41PM', 2),

    (2, '8/3/2011 8:31PM', 1),

    (2, '8/3/2011 9:01PM', 1)

    SELECT

    pvt.store_id,

    [8pm] = pvt.[20],

    [9pm] = pvt.[21]

    FROM

    (

    SELECT

    store_id,

    hr = DATEPART(HOUR, visit_time)

    FROM @Table AS t

    ) AS src

    PIVOT (COUNT(hr) FOR hr IN ([20],[21])) AS pvt

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 3, 2011 at 9:40 pm

    #1363426

    thanks!! that helps a lot but it also requires me to change the compatibility level of the DB, which i can't really do. is there another alternative?

  • August 3, 2011 at 10:01 pm

    #1363431

    rjdpa2 (8/3/2011)

    thanks!! that helps a lot but it also requires me to change the compatibility level of the DB, which i can't really do. is there another alternative?

    What compatibility level do you have set?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 3, 2011 at 11:09 pm

    #1363444

    80 i believe. i searched microsoft and they require 90 or higher to use the pivot function, which i can not change.

  • August 3, 2011 at 11:38 pm

    #1363447

    rjdpa2 (8/3/2011)

    thanks!! that helps a lot but it also requires me to change the compatibility level of the DB, which i can't really do. is there another alternative?

    Sure:

    SELECT

    t.store_id,

    [8pm] = SUM(CASE WHEN DATEPART(HOUR, t.visit_time) = 20 THEN 1 ELSE 0 END),

    [9pm] = SUM(CASE WHEN DATEPART(HOUR, t.visit_time) = 21 THEN 1 ELSE 0 END)

    FROM @Table AS t

    GROUP BY

    t.store_id

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply