select query with all values

  • Hi,

    I am selecting the rows from a set of dates and should get values for all minutes and should replace zero if there is no value to any minute.

    here is my sample query:

    select isnull(datepart(n,time_stamp),0), datepart(hh,time_stamp), count(sender_mobile_no) from testtable

    where time_stamp between '9/20/2010 00:00:00'and '9/20/2010 23:59:59'

    group by datepart(n,time_stamp),datepart(hh,time_stamp)

    order by datepart(hh,time_stamp),datepart(n,time_stamp)

    my requirement is suppose if you don't have records on 23rd minute then also I should get a record with zero value.

    pls help.

  • Please provide a sample table and data. See the first link in my signature to help if you need it.

    Short form: You'll be looking at creating a 'minutes' table, then left joining to an aggregate query with your real data.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi,

    Here is the sample data in a table which is having a two columns [time_stamp].

    sample data:

    Number time_stamp

    98424123452010-09-20 00:00:02.173

    98424143452010-09-20 00:00:23.470

    98424123452010-09-20 00:00:27.377

    98424143452010-09-20 00:00:58.813

    98424133452010-09-20 00:02:18.737

    98424153452010-09-20 00:02:18.893

    98424113452010-09-20 00:02:22.143

    98424163452010-09-20 00:04:16.973

    98424183452010-09-20 00:06:11.087

    98424103452010-09-20 00:07:29.760

    I need result like

    min....hours....count

    0 0 4

    1 0 0

    2 0 3

    3 0 0

    4 0 1

    5 0 0

    6 0 1

    7 0 1

    Thanks

  • Take two: Please actually read the link. You're going to want to give us a CREATE TABLE, INSERT INTO SELECT ... UNION SELECT... structure. Well, unless someone gets inspired to do it for you.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi,

    Sorry..Here is the sample data which you suggested.

    --===== If the test table already exists, drop it

    IF OBJECT_ID('aaaa..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (Mnbr varchar(15),time_stamp DATETIME)

    --inserting data

    insert into #mytable (Mnbr,time_stamp)

    select '9842412345', '2010-09-20 00:00:02.173' union all

    select '9842414345', '2010-09-20 00:00:23.470' union all

    select '9842412345', '2010-09-20 00:00:27.377' union all

    select '9842414345', '2010-09-20 00:00:58.813' union all

    select '9842413345', '2010-09-20 00:02:18.737' union all

    select '9842415345', '2010-09-20 00:02:18.893' union all

    select '9842411345', '2010-09-20 00:02:22.143' union all

    select '9842416345', '2010-09-20 00:04:16.973' union all

    select '9842418345', '2010-09-20 00:06:11.087' union all

    select '9842410345', '2010-09-20 00:07:29.760'

    Query:

    select datepart(n,time_stamp), datepart(hh,time_stamp), count(Mnbr) from #mytable

    where time_stamp between '9/20/2010 00:00:00'and '9/20/2010 23:59:59'

    group by datepart(n,time_stamp),datepart(hh,time_stamp)

    order by datepart(hh,time_stamp),datepart(n,time_stamp)

    Expecting Result:

    select '0'as minutes,'0'as hours,'4'as cnt union all

    select '1'as minutes,'0'as hours,'0'as cnt union all

    select '2'as minutes,'0'as hours,'3'as cnt union all

    select '3'as minutes,'0'as hours,'0'as cnt union all

    select '4'as minutes,'0'as hours,'1'as cnt union all

    select '5'as minutes,'0'as hours,'0'as cnt union all

    select '6'as minutes,'0'as hours,'1'as cnt union all

    select '7'as minutes,'0'as hours,'1'as cnt

    Please help.

  • Try the below query. It is using a table variable to populate the minute, Hour table and using the same for populating the result set.

    DECLARE @MinuteTable Table(minuteofDay SMALLINT, hourofDay SMALLINT)

    DECLARE @HourofDay SMALLINT

    DECLARE @MinuteofDay SMALLINT

    SET@MinuteofDay = 0

    SET@HourofDay = 0

    WHILE@HourofDay < 24

    BEGIN

    WHILE @MinuteofDay < 60

    BEGIN

    INSERT INTO @MinuteTable(minuteofDay,hourofDay) VALUES(@MinuteofDay,@HourofDay)

    SET @MinuteofDay = @MinuteofDay +1

    END

    SET @HourofDay = @HourofDay + 1

    SET @MinuteofDay = 0

    END

    SELECTmt.hourofDay as 'Hour'

    ,mt.minuteofDay as 'Minute'

    , COUNT(sender_mobile_no)

    FROM@MinuteTable mt

    LEFT JOINtesttable tt

    ONmt.minuteofDay = DATEPART(minute,tt.time_stamp)

    ANDmt.hourofDay = DATEPART(hour,tt.time_stamp)

    WHEREtt.time_stamp BETWEEN '9/20/2010 00:00:00' AND '9/20/2010 23:59:59'

    GROUP BYmt.hourofDay, mt.minuteofDay

    ORDER BYmt.hourofDay, mt.minuteofDay

  • Well, if I'd ever seen a request for a tally table solution, this would be it. 🙂 Much easier to build out the necessary pre-array this way.

    Check out this link to find out what and where tempdb.dbo.tally comes from. You'll find it to be a new friend: http://www.sqlservercentral.com/articles/T-SQL/62867/

    SELECT

    drvHrsMins.Hrs AS [Hours],

    drvHrsMins.Mins AS [Minutes],

    ISNULL( drvCnt.cnt, 0) AS [count]

    FROM

    (SELECT

    Hrs, Mins

    FROM

    (SELECT top 24 N-1 AS Hrs from tempdb.dbo.tally) AS tHrs, -- The -1 is to have a 0 based array

    (SELECT top 60 N-1 AS Mins from tempdb.dbo.tally) AS tMin

    ) AS drvHrsMins

    LEFT JOIN

    (select

    datepart(n,time_stamp) As Mins,

    datepart(hh,time_stamp) AS Hrs,

    count(Mnbr) AS cnt

    from

    #mytable

    where

    time_stamp between '9/20/2010 00:00:00'and '9/20/2010 23:59:59'

    group by

    datepart(n,time_stamp),datepart(hh,time_stamp)

    ) AS drvCnt

    ONdrvHrsMins.Hrs = drvCnt.Hrs

    AND drvHrsMins.Mins = drvCnt.Mins

    ORDER BY

    drvHrsMins.Hrs,

    drvHrsMins.Mins


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig and Vekatraman, With all due respect to your wonderful attempts, both of your queries are not what the OP is expecting..

    Try this:

    DECLARE @tab TABLE

    (

    Number BIGINT,

    Time_Stamp DATETIME

    )

    INSERT INTO @tab

    SELECT 9842412345, '2010-09-20 00:00:02.173'

    UNION ALL SELECT 9842414345, '2010-09-20 00:00:23.470'

    UNION ALL SELECT 9842412345, '2010-09-20 00:00:27.377'

    UNION ALL SELECT 9842414345, '2010-09-20 00:00:58.813'

    UNION ALL SELECT 9842413345, '2010-09-20 00:02:18.737'

    UNION ALL SELECT 9842415345, '2010-09-20 00:02:18.893'

    UNION ALL SELECT 9842411345, '2010-09-20 00:02:22.143'

    UNION ALL SELECT 9842416345, '2010-09-20 00:04:16.973'

    UNION ALL SELECT 9842418345, '2010-09-20 00:06:11.087'

    UNION ALL SELECT 9842410345, '2010-09-20 00:07:29.760'

    ;WITH Tens (N) AS

    (

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1

    ),

    HUNDREDS AS

    (

    SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2

    ),

    THOUSANDS AS

    (

    SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2

    ),

    Numbers AS

    (

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS

    ),

    TimeTable_CTE AS

    (

    SELECT Hrs.Rn AS Hrs, Mins.RN AS MinsInAnHour , 0 AS [Count]

    FROM

    ( SELECT (RN-1) AS RN FROM Numbers WHERE RN < 25 ) AS Hrs

    CROSS JOIN

    ( SELECT (RN-1) AS RN FROM Numbers WHERE RN < 61 ) AS Mins

    ),

    DataFromTable AS

    (

    SELECT

    DATEPART(MI,Time_Stamp) Mins

    ,DATEPART(HOUR,Time_Stamp) Hrs

    , CountOfNumbers = COUNT(*)

    FROM

    @tab

    GROUP BY

    DATEPART(MI,Time_Stamp)

    ,DATEPART(HOUR,Time_Stamp)

    ),

    [Collection] AS

    (

    SELECT Timetable.Hrs , Timetable.MinsInAnHour , 0 as [Count]

    FROM TimeTable_CTE Timetable

    JOIN (

    SELECT Hrs , MAX(Mins) MaxMins

    FROM DataFromTable

    GROUP BY Hrs

    ) AS Tab

    ON Timetable.Hrs = Tab.Hrs AND

    Timetable.MinsInAnHour <= Tab.MaxMins

    )

    SELECT C.Hrs, C.MinsInAnHour , ISNULL(D.CountOfNumbers,C.[Count]) [Count]

    FROM [Collection] C

    LEFT JOIN DataFromTable D

    ON C.Hrs = D.Hrs AND

    C.MinsInAnHour = D.Mins

    ORDER BY

    C.Hrs, C.MinsInAnHour

  • ColdCoffee (9/21/2010)


    Craig and Vekatraman, With all due respect to your wonderful attempts, both of your queries are not what the OP is expecting..

    Hm, Coffee, you have a valid point. I did explode mine out to make sure it covered an entire day rather then just the specific points in his exact result set. I made the assumption it needed to be expanded to dates next, actually. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Coffie,

    Thanks for your help. Got it.

    Thanks

  • Craig Farrell (9/21/2010)


    I did explode mine out to make sure it covered an entire day rather then just the specific points in his exact result set. I made the assumption it needed to be expanded to dates next, actually. 🙂

    Craig, even i had a slant towards that front, but i confined myself to the request the OP put forth 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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