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)
 How to get Total Hours rendered

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-05 : 02:23:21
Hi Guys,

I have a requirements to get the total hour rendered per line by day or month. this is my first time to encounter this requirements and i dont have any idea. kindly please help me guys on how to achieve this. thank you very much.

see below is the sample data and script.


drop table #sample

Create table #sample
(WrkCtrId nvarchar(10),
createdby nvarchar(10),
[status] int,
passqty int,
lineid nvarchar(10),
CreatedDatetime datetime)

insert into #sample values ('EOL','RBer0',5,1,'Line1','2013-01-01 23:00:53.000')
insert into #sample values ('EOL','RBer0',6,1,'Line1','2013-01-01 23:15:18.000')
insert into #sample values ('EOL','RBer0',5,1,'Line1','2013-01-01 23:15:57.000')
insert into #sample values ('EOL','kath1',5,1,'Line1','2013-01-01 23:31:32.000')
insert into #sample values ('EOL','RBer0',5,1,'Line1','2013-01-01 23:33:04.000')
insert into #sample values ('EOL','rema0',5,1,'Line1','2013-01-01 23:33:58.000')
insert into #sample values ('EOL','rema0',5,1,'Line1','2013-01-01 23:40:59.000')
insert into #sample values ('EOL','RBer0',6,1,'Line1','2013-01-01 23:41:54.000')
insert into #sample values ('EOL','RBer0',5,0,'Line1','2013-01-01 23:52:41.000')
insert into #sample values ('EOL','RBer0',5,1,'Line2','2013-01-01 23:54:32.000')
insert into #sample values ('EOL','RBer0',6,1,'Line2','2013-01-02 00:07:45.000')
insert into #sample values ('EOL','RBer0',5,1,'Line2','2013-01-02 00:09:16.000')
insert into #sample values ('EOL','kath1',5,1,'Line2','2013-01-02 00:19:38.000')
insert into #sample values ('EOL','RBer0',5,1,'Line2','2013-01-02 00:21:05.000')
insert into #sample values ('EOL','rema0',5,1,'Line2','2013-01-02 00:26:57.000')
insert into #sample values ('EOL','rema0',5,1,'Line2','2013-01-02 00:29:52.000')
insert into #sample values ('EOL','RBer0',6,1,'Line2','2013-01-02 00:52:34.000')
insert into #sample values ('EOL','RBer0',5,0,'Line2','2013-01-02 00:53:26.000')


Select distinct a.lineid, a.WrkCtrId, count(b.totalqty), COUNT(c.passqty) as passsqty
from #sample a
inner join (select WrkCtrId,lineid, COUNT(status) as totalqty
from #sample b group by WrkCtrId,lineid ) b
on b.lineid=a.lineid and b.WrkCtrId=a.WrkCtrId
inner join (select lineid, COUNT(passqty) as passqty
from #sample b where status=5 GROUP by WrkCtrId,lineid ) c
on c.lineid=a.lineid
group by a.lineid, a.WrkCtrId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-05 : 02:43:56
so what should be the expected output? whats the logic for rendered line time? is it timediff between two consecutive status?

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-05 : 03:00:30
Hi Visakh,

Thanks for the reply. The logic should be the first transacttion of the day and the last transaction of the day.
by using the status=5



Select lineid, WrkCtrId
, Count(*) Totalqty
, SUM(CASE WHEN [status]=5 Then 1 Else 0 END) passsqty

FROM #sample
GROUP BY lineid, WrkCtrId



lineid---WrkCtrId---2013-1-1----2013-1-2
----------------------------------------
Line1----EOL-------- ?? ----- ??
Line2----EOL-------- ?? ----- ??
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-05 : 05:35:34
Hi Visakh

Need to get the timediff between the first date transaction and the last date transaction per lineid and WrkCtrId using the status =5.

could not get on how to to this in script. thanks

ex. January 1, 2013 first transaction '2013-01-01 23:00:53.000'
January 1, 2013 last date transaction '2013-01-01 23:54:32.000'


Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-05 : 09:11:41
hi guys,

here is my script. if you need any idea to enhance this script on getting the rendered hrs using min and max of createdatetime per line, wrkctrid.

thanks.

select lineid, wrkctrid ,datediff(HOUR,MIN(CreatedDatetime),MAX(CreatedDatetime)) as Hour,datediff(MINUTE,MIN(CreatedDatetime),MAX(CreatedDatetime)) as min 
from #sample
where [status] = 5
group by lineid, wrkctrid

lineid---wrkctrid---creadatetime---hour---min
--------------------------------------------------------
line1---EOL-------2013-1-1--------??
line2---EOL-------2013-1-2--------??
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-05 : 09:25:21
I think Visakh question is still not answered - What is the expected output based on the data in #sample table as you've provided? And what is the logic behind the output (which you called as rendered line time)?

Cheers
MIK
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-07 : 21:39:20
Here is my Query.

Btw, hould could i convert the Hr_Min_Sec into 12 hrs format: like 12:00 PM/AM. thanks.


Select 
lineid,
WrkCtrId,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime)) as CreatedDatetime,
Min(CreatedDatetime) StartDt,
Max(CreatedDatetime) EndDt,
Convert(varchar(8), dateadd(second, DATEDIFF(Second,Min(CreatedDatetime), Max(CreatedDatetime)), 0),108) Hr_Min_Sec
From
#sample
where
status=5
Group By lineid, WrkCtrId,DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime))



RESULT:
lineid WrkCtrId CreatedDatetime StartDt EndDt Hr_Min_Sec
Line1 EOL 2013-01-01 00:00:00.000 2013-01-01 23:00:53.000 2013-01-01 23:52:41.000 00:51:48
Line2 EOL 2013-01-01 00:00:00.000 2013-01-01 23:54:32.000 2013-01-01 23:54:32.000 00:00:00
Line2 EOL 2013-01-02 00:00:00.000 2013-01-02 00:09:16.000 2013-01-02 00:53:26.000 00:44:10




Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-07 : 22:13:09
This is the code to get the 12 hrs format.

right('0' + ltrim(right(convert(varchar,cast(Hr_Min_Sec as dateTime), 100), 7)), 7) as Hr_Min_Sec,

Hr_Min_Sec='09:07:14'

result:
09:07AM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 01:34:30
quote:
Originally posted by Villanuev

Here is my Query.

Btw, hould could i convert the Hr_Min_Sec into 12 hrs format: like 12:00 PM/AM. thanks.


Select 
lineid,
WrkCtrId,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime)) as CreatedDatetime,
Min(CreatedDatetime) StartDt,
Max(CreatedDatetime) EndDt,
Convert(varchar(8), dateadd(second, DATEDIFF(Second,Min(CreatedDatetime), Max(CreatedDatetime)), 0),108) Hr_Min_Sec
From
#sample
where
status=5
Group By lineid, WrkCtrId,DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime))



RESULT:
lineid WrkCtrId CreatedDatetime StartDt EndDt Hr_Min_Sec
Line1 EOL 2013-01-01 00:00:00.000 2013-01-01 23:00:53.000 2013-01-01 23:52:41.000 00:51:48
Line2 EOL 2013-01-01 00:00:00.000 2013-01-01 23:54:32.000 2013-01-01 23:54:32.000 00:00:00
Line2 EOL 2013-01-02 00:00:00.000 2013-01-02 00:09:16.000 2013-01-02 00:53:26.000 00:44:10







this is a presentation issue and should be dealt with at front end application.

If you really want to do this in sql use CONVERT with proper style value

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-09 : 21:05:26
Thanks Visakh for your suggestion.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-10 : 00:49:14
welcome

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

- Advertisement -