Post reply

count number of rows per hour per client and in the end total

  • September 16, 2008 at 10:45 am

    #72923

    how to count number of rows per hour per client and in the end total

    here is something like needed output

    datetime no. of rows client

    9/16/2008 7:00:00 AM 10 abc

    9/16/2008 8:00:00 AM 20 abc

    9/16/2008 9:00:00 AM 30 abc

    9/16/2008 7:00:00 AM 20 xyz

    9/16/2008 8:00:00 AM 10 xyz

    9/16/2008 9:00:00 AM 40 xyz

    9/16/2008 7:00:00 AM 10 tqm

    9/16/2008 8:00:00 AM 20 tqm

    9/16/2008 9:00:00 AM 60 tqm

    Total 220

  • September 16, 2008 at 1:21 pm

    #872648

    You're looking for something like this?

    Declare @StartTimedatetime,

    @EndTimedatetime,

    @Totalint

    SELECT Client, datepart(hh, YourDate) HourNumber, SUM([NumRows]) VRowCount

    INTO #1

    FROM YourTable

    WHERE YourDate BETWEEN @StartTime and @EndTime

    GROUP BY Client, datepart(hh,YourDate)

    SET @Total = (SELECT SUM(VRowCount)

    FROM #1)

    SELECT Client, HourNumber, VRowCount, @Total TotalRows

    FROM #1

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • September 16, 2008 at 1:43 pm

    #872662

    This will give you client sub-totals without the need for a temporary table.

    SELECT

    CASE WHEN client IS NOT NULL AND hourSegment IS NULL THEN 'Client Total' ELSE CAST(hourSegment AS VARCHAR) END,

    [No of Rows],

    CASE WHEN client IS NULL AND hourSegment IS NULL THEN 'Grand Total' ELSE client END AS client

    FROM (

    SELECT LEFT(CONVERT(VARCHAR(30), [datetime], 20), 13) AS hourSegment, client, SUM([no. of rows]) AS [No of Rows]

    FROM myTable

    GROUP BY LEFT(CONVERT(VARCHAR(30), [datetime], 20), 13), client WITH ROLLUP

    )x

    The above should give you something like:

    hourSegment No of Rows Client

    9/16/2008 7 10 abc

    9/16/2008 8 20 abc

    9/16/2008 9 30 abc

    Client Total 60 abc

    9/16/2008 7 20 xyz

    9/16/2008 8 10 xyz

    9/16/2008 9 40 xyz

    Client Total 70 xyz

    9/16/2008 7 10 tqm

    9/16/2008 8 20 tqm

    9/16/2008 9 60 tqm

    Client Total 90 tqm

    220 Grand Total


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 16, 2008 at 1:48 pm

    #872667

    thanks , this wht i needed

  • October 10, 2008 at 9:34 am

    #883503

    i don't want grant total row. can you help me in that?

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

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