Post reply

The query output should be

  • August 5, 2009 at 9:49 pm

    #385126

    We have database called store, I have an issue to generate the hourly count report dynamically.

    Whenever I execute the below query and I get the output as below

    Date_Time MsgCount

    2009-08-06 01 2452

    2009-08-06 02 2210

    2009-08-06 03 4173

    2009-08-06 04 6450

    2009-08-06 05 5452

    2009-08-06 06 2452

    2009-08-06 07 2462

    2009-08-06 10 4521

    But I need the output should be

    Date_Time MsgCount

    2009-08-06 01 2452

    2009-08-06 02 2210

    2009-08-06 03 4173

    2009-08-06 04 6450

    2009-08-06 05 5452

    2009-08-06 06 2452

    2009-08-06 07 2462

    2009-08-06 08 0

    2009-08-06 09 0

    2009-08-06 10 4521

    I am using the query to generate the report as below,

    select convert(varchar(13), lastupdated, 120) As Date_Time, count(*) As MsgCount from store_details(nolock)

    group by convert(varchar(13), lastupdated, 120)

    order by convert(varchar(13), lastupdated, 120)

    Please help me how will I get the output.

    Thanks in Advance

    Kindest Regards,

    karthik

  • August 5, 2009 at 10:39 pm

    #1035172

    Heh... get busy Karthik... you know how to use a Tally table with an outer join. 😉

    --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)
    Intro to Tally Tables and Functions

  • August 5, 2009 at 10:50 pm

    #1035178

    AND you've been around long enough to know how to post data to get help. If not, please reread the article at the first link in my signature below. I'm going to stop helping unless you start helping. 😉

    --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)
    Intro to Tally Tables and Functions

  • August 5, 2009 at 11:29 pm

    #1035185

    AND you posted this on both the 2k and 2k5 forum! You know better than to double post! All it does is tick people off. Stop it. 😉

    --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)
    Intro to Tally Tables and Functions

  • August 5, 2009 at 11:33 pm

    #1035186

    My requirement is different.

    Kindest Regards,

    karthik

  • August 6, 2009 at 12:08 am

    #1035194

    karthikeyan (8/5/2009)

    My requirement is different.

    Not according to what you posted. Capture the output in a temp table and apply a Tally table to get the dates.

    --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)
    Intro to Tally Tables and Functions

  • August 6, 2009 at 11:53 am

    #1035558

    Quick question... are you the same Karthikeyan that is a "Senior Software Engineer" working on a PHD or are you someone else?

    --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)
    Intro to Tally Tables and Functions

  • August 7, 2009 at 5:02 am

    #1035889

    I am bangalore, India.

    Kindest Regards,

    karthik

  • August 7, 2009 at 6:42 am

    #1035939

    Ah... my very bad then... you are a different Karthik than the one I'm used to working with. Someone showed me that there are two people with the same "Karthikeyan" handle on this forum and I didn't know that was possible. The one I've worked with in the past knows all about the Tally table. My most humble apologies for the confusion.

    I'm on my way to work... I'll try to bang this out during lunch unless someone beats me to it.

    --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)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

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