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)
 How to get Total Hours rendered
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 04/05/2013 :  02:23:21  Show Profile  Reply with Quote
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


Edited by - Villanuev on 04/05/2013 02:23:55

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/05/2013 :  02:43:56  Show Profile  Reply with Quote
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

440 Posts

Posted - 04/05/2013 :  03:00:30  Show Profile  Reply with Quote
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--------  ??   -----   ??

Edited by - Villanuev on 04/05/2013 03:03:11
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 04/05/2013 :  05:35:34  Show Profile  Reply with Quote
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

440 Posts

Posted - 04/05/2013 :  09:11:41  Show Profile  Reply with Quote
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--------??

Edited by - Villanuev on 04/05/2013 09:14:39
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/05/2013 :  09:25:21  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/05/2013 09:25:40
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

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

440 Posts

Posted - 04/07/2013 :  22:13:09  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 04/09/2013 :  01:34:30  Show Profile  Reply with Quote
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

440 Posts

Posted - 04/09/2013 :  21:05:26  Show Profile  Reply with Quote
Thanks Visakh for your suggestion.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/10/2013 :  00:49:14  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000