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 #sampleCreate 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 passsqtyfrom #sample ainner join (select WrkCtrId,lineid, COUNT(status) as totalqty from #sample b group by WrkCtrId,lineid ) bon 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 ) con 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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=5Select lineid, WrkCtrId , Count(*) Totalqty, SUM(CASE WHEN [status]=5 Then 1 Else 0 END) passsqtyFROM #sample GROUP BY lineid, WrkCtrId lineid---WrkCtrId---2013-1-1----2013-1-2----------------------------------------Line1----EOL-------- ?? ----- ??Line2----EOL-------- ?? ----- ?? |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-04-05 : 05:35:34
|
Hi VisakhNeed 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' |
|
|
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 #samplewhere [status] = 5group by lineid, wrkctridlineid---wrkctrid---creadatetime---hour---min --------------------------------------------------------line1---EOL-------2013-1-1--------??line2---EOL-------2013-1-2--------?? |
|
|
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)?CheersMIK |
|
|
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_SecFrom#samplewhere status=5Group By lineid, WrkCtrId,DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime))RESULT:lineid WrkCtrId CreatedDatetime StartDt EndDt Hr_Min_SecLine1 EOL 2013-01-01 00:00:00.000 2013-01-01 23:00:53.000 2013-01-01 23:52:41.000 00:51:48Line2 EOL 2013-01-01 00:00:00.000 2013-01-01 23:54:32.000 2013-01-01 23:54:32.000 00:00:00Line2 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 |
|
|
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 |
|
|
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_SecFrom#samplewhere status=5Group By lineid, WrkCtrId,DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreatedDatetime))RESULT:lineid WrkCtrId CreatedDatetime StartDt EndDt Hr_Min_SecLine1 EOL 2013-01-01 00:00:00.000 2013-01-01 23:00:53.000 2013-01-01 23:52:41.000 00:51:48Line2 EOL 2013-01-01 00:00:00.000 2013-01-01 23:54:32.000 2013-01-01 23:54:32.000 00:00:00Line2 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-04-09 : 21:05:26
|
Thanks Visakh for your suggestion. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 00:49:14
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|