Hourly data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Hourly data

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-06-19 : 23:33:49

Hi,

I want to find the records between hours. daily
for ex. start time will be 06:00 AM to 07:00 of 6/20/2013
Next will be 07:00 AM to 08:00 of 6/20/2013
and soon..
What approach should i apply to get the desired result. Thanks.

Sample Data:

Create table #sample
(ShippedDate datetime)
Insert into #sample
Select '2013-06-20 08:00:00.000' union all
Select '2013-06-20 08:36:50.447' union all
Select '2013-06-20 08:06:14.777' union all
Select '2013-06-20 08:57:00.000' union all
Select '2013-06-20 09:14:56.200' union all
Select '2013-06-20 09:15:05.527' union all
Select '2013-06-20 10:37:11.483' union all
Select '2013-06-20 10:55:19.170'

select * from #sample
where ShippedDate between 'hour' and 'hour'

JanakiRam
Starting Member

22 Posts

Posted - 2013-06-20 : 00:31:46

SELECT * FROM
#sample
WHERE
CAST(LEFT(CONVERT(TIME,ShippedDate),2) AS INT) BETWEEN 9 AND 10

Check it once...

JanakiRam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:07:21

do you mean this?


select min(ShippedDate) AS Start,MAX(ShippedDate) AS End,COUNT(*)  
from #sample 
group by (datediff(minute,DATEADD(dd,DATEDIFF(dd,0,ShippedDate),0),ShippedDate)/60)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:08:37

if you want to dynamically set slots use this

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-18 : 22:41:29

Hi Visakh,

Sorry i forgot to reply and i need to recall this topic.
I have a query that needs to pulled out records every hour.
i think this can be achieved in subscription but how could i put this in a query..

SELECT Trandate,field1, field2, etc..
FROM MyTable
WHERE Trandate ??

the report started to run at 6:00 AM. so from
6:00AM-7:00AM-------1 hr
7:00AM-8:00AM-------1 hr
and so on..

i need the hourly data..

THanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-19 : 00:49:54

ok...so each one hour it needs to pick everything from that time to 1hr backward?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-22 : 21:38:10

Yes Visakh, thanks for your reply. how could be done this in a query. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 01:12:11

you mean this?


select *
from #sample 
where ShippedDate >= DATEADD(hh,-1,GETDATE())
AND ShippedDate < DATEADD(ss,1,GETDATE())

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 01:43:04

Thanks Visakh.I will try this solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 01:46:36

ok...let me know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 01:56:05

Hi visakh,

OK. btw, i tried to post a new thread but i'm not successful. i got an error posting the new thread. i have no problem using the reply.
it's okay if i will post to this thread?

my requirements is to get the average date using datediff with condition.
i tried some scenario but could not get the exact result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 02:05:30

quote:
Originally posted by Villanuev

Hi visakh,

OK. btw, i tried to post a new thread but i'm not successful. i got an error posting the new thread. i have no problem using the reply.
it's okay if i will post to this thread?

my requirements is to get the average date using datediff with condition.
i tried some scenario but could not get the exact result.

what do you mean by average date? Can you illustrate with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 02:23:41

[code]

create table #sample
(wrkctrid nvarchar(35),
createddate datetime,
lastupdate datetime,
asustatus int)
go
insert into #sample
select 'Assembly 2','2013-07-10','2013-07-19',5 union all
select 'Assembly 2','2013-07-14','2013-07-23',5 union all
select 'Assembly 2','2013-07-17','2013-07-23',1 union all
select 'Assembly 2','2013-05-14','2013-05-30',6 union all
select 'Assembly 2','2013-07-20','2013-07-22',6 union all
select 'Assembly 2','2013-07-20','2013-07-23',6 union all
select 'Assembly 2','2013-07-18','2013-07-23',2

;with cte as
(
select
wrkctrid,
createddate,
lastupdate,
asustatus,
datediff(dd,createddate,lastupdate) as nodays
from #sample
order by asustatus
)
Select
wrkctrid,
Sum(Case When ASUSTATUS=1 OR ASUSTATUS=2 OR ASUSTATUS=5 Then 1 else 0 End) As WIPQty,
Sum(Case When ASUSTATUS=6 Then 1 else 0 End) As FailQty,
Case When ASUSTATUS=1 OR ASUSTATUS=2 OR ASUSTATUS=5 then avg(nodays) End as avgdayswip,
Case When ASUSTATUS=6 then AVG(nodays) End as avgdaysfail,
COUNT(wrkctrid) as GrandTotal
from cte
group by wrkctrid, ASUSTATUS

desired result:
----------------

Wrkctrid-------wipqty---failqty---avg days wip----avg days fail----total qty---tot ave days
--------------------------------------------------------------------------------------------
Assembly 2-------4---------3--------7.25--------------7 -------------7------------7.14[/code]

[/code]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 02:36:39

I trid this but the avg days for wip and fail is not correct..

with cte as
(
select
from
)
Select
t2.wrkctrid,
SUM(case When t2.asustatus IN (1,2,5) then t2.wipqty else 0 end) as wqty,
SUM(case When t2.asustatus =6 then t2.failqty else 0 end) as fqty,
AVG(t2.nodays) as TotalAveDays,
COUNT(t2.wrkctrid) as GrandTotal,
AVG(Case when t2.asustatus IN (1,2,5) then t2.nodays else 0 end) as wavg,
AVG(Case when t2.asustatus =6 then t2.nodays else 0 end) as favg
from cte as t2
group by wrkctrid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 04:02:56

Can you post what should be your desired output for the sample data below?


create table #sample
(wrkctrid nvarchar(35),
createddate datetime,
lastupdate datetime,
asustatus int)
go
insert into #sample
select 'Assembly 2','2013-07-10','2013-07-19',5 union all
select 'Assembly 2','2013-07-14','2013-07-23',5 union all
select 'Assembly 2','2013-07-17','2013-07-23',1 union all
select 'Assembly 2','2013-05-14','2013-05-30',6 union all
select 'Assembly 2','2013-07-20','2013-07-22',6 union all
select 'Assembly 2','2013-07-20','2013-07-23',6 union all
select 'Assembly 2','2013-07-18','2013-07-23',2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 05:13:23

This is the desired result:

desired result:
----------------

Wrkctrid-------wipqty---failqty---avg days wip----avg days fail----total qty---tot ave days
--------------------------------------------------------------------------------------------
Assembly 2-------4---------3--------7.25--------------7 -------------7------------7.14

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 05:27:40

[code]
SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS avgdayswip,
SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS avgdaysfail,
COUNT(*) AS totalqty,
DATEDIFF(dd,createddate,lastupdate)*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 05:43:56

hi Visakh, just got an error..

Msg 8120, Level 16, State 1, Line 7
Column '#sample.createddate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 7
Column '#sample.lastupdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 07:30:52

show me the code please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 08:53:29

I used your solution.

SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS avgdayswip,
SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS avgdaysfail,
COUNT(*) AS totalqty,
DATEDIFF(dd,createddate,lastupdate)*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 21:39:47

Hi Visakh. just created a separate thread. i think my internet connection is working fine. please no need to reply on this thread. thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources