Post reply

T SQL statement issue

  • February 21, 2013 at 6:10 am

    #268900

    Guys,

    I have a requirement as follows:

    CREATE TABLE #temp

    (CallDate DAtetime,

    Talktime INT,

    ID INT,

    others Varchar(10))

    INSERT INTO #temp

    SELECT '2013-01-01', 10,1,'ABC'

    UNION

    SELECT '2013-01-01', 10,1,'---'

    UNION

    SELECT '2013-01-01', 20,2,'XYZ'

    UNION

    SELECT '2013-01-01', 20,2,'--'

    UNION

    SELECT '2013-01-01', 30,3,'asd'

    UNION

    SELECT '2013-01-01', 30,3,'---'

    UNION

    SELECT '2013-01-02', 40,1,'ABCD'

    UNION

    SELECT '2013-01-02', 40,1,'---_'

    UNION

    SELECT '2013-01-02', 30,2,'XYZQ'

    UNION

    SELECT '2013-01-02', 30,2,'--1'

    UNION

    SELECT '2013-01-02', 20,3,'asdf'

    UNION

    SELECT '2013-01-02', 20,3,'---'

    Using a query like this:

    --------------

    select calldate,

    SUM(talktime) as Overall,

    case when ID = 1 then SUM(talktime) END AS A_Talktime,

    case when ID = 2 then SUM(talktime) END AS B_Talktime,

    case when ID = 3 then SUM(talktime) END AS C_Talktime

    FROM #temp

    GROUP BY calldate, ID

    --------------------

    I need an output like below:

    Calldate------------Overall------A_Talktime-----B_Talktime------C_Talktime

    _________________________________________________________________

    2013-01-01----------120-----------20-------------40--------------60

    2013-01-02----------180-----------80-------------60--------------40

    Let me know if any other info is required.

    Thanks

  • February 21, 2013 at 6:25 am

    #1589917

    So nearly there...

    SELECT

    calldate,

    Overall= SUM(talktime),

    A_Talktime= SUM(case when ID = 1 then talktime END),

    B_Talktime= SUM(case when ID = 2 then talktime END),

    C_Talktime= SUM(case when ID = 3 then talktime END)

    FROM #temp

    GROUP BY calldate

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 21, 2013 at 6:31 am

    #1589920

    Its embarassing :blush: . Thank you!!

  • February 21, 2013 at 7:06 am

    #1589942

    chandrakant_gaurav (2/21/2013)

    Its embarassing :blush: . Thank you!!

    Heh - don't be embarrassed, you almost got there. Thanks loads for posting a sample script.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 26, 2013 at 9:43 pm

    #1591376

    chandrakant_gaurav (2/21/2013)

    Its embarassing :blush: . Thank you!!

    If you'd like to learn more about this "CROSSTAB" technique (which is also known as a PIVOT), please see the following articles...

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --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)

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

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