September 20, 2010 at 9:50 pm
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.
September 20, 2010 at 10:29 pm
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.
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
September 20, 2010 at 10:39 pm
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
September 20, 2010 at 10:43 pm
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.
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
September 21, 2010 at 12:26 am
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.
September 21, 2010 at 12:44 am
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
September 21, 2010 at 12:49 am
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://qa.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
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
September 21, 2010 at 1:43 am
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
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
September 21, 2010 at 2:03 am
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. 🙂
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
September 21, 2010 at 2:32 am
Hi Coffie,
Thanks for your help. Got it.
Thanks
September 21, 2010 at 5:03 am
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