SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Hourly data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 06/19/2013 :  23:33:49  Show Profile  Reply with Quote
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'

Edited by - Villanuev on 06/19/2013 23:34:55

JanakiRam
Starting Member

India
22 Posts

Posted - 06/20/2013 :  00:31:46  Show Profile  Reply with Quote
SELECT * FROM
#sample
WHERE
CAST(LEFT(CONVERT(TIME,ShippedDate),2) AS INT) BETWEEN 9 AND 10

Check it once...

JanakiRam
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/20/2013 :  01:07:21  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/20/2013 01:10:38
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/20/2013 :  01:08:37  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/18/2013 :  22:41:29  Show Profile  Reply with Quote
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..


Edited by - Villanuev on 07/18/2013 22:44:53
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/19/2013 :  00:49:54  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/22/2013 :  21:38:10  Show Profile  Reply with Quote
Yes Visakh, thanks for your reply. how could be done this in a query. thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  01:12:11  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  01:43:04  Show Profile  Reply with Quote
Thanks Visakh.I will try this solution.



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  01:46:36  Show Profile  Reply with Quote
ok...let me know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  01:56:05  Show Profile  Reply with Quote
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.


Edited by - Villanuev on 07/23/2013 01:56:44
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  02:05:30  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  02:23:41  Show Profile  Reply with Quote


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



Edited by - Villanuev on 07/23/2013 02:24:41
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  02:36:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  04:02:56  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  05:13:23  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  05:27:40  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  05:43:56  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  07:30:52  Show Profile  Reply with Quote
show me the code please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  08:53:29  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  21:39:47  Show Profile  Reply with Quote
Hi Visakh. just created a separate thread. i think my internet connection is working fine. please no need to reply on this thread. thanks.

Edited by - Villanuev on 07/23/2013 21:41:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000