Convert Manhours table to SQL codes

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)

Convert Manhours table to SQL codes

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-23 : 03:38:03

Hi Guys,

I have this manhours table. It say that from the time of 6:00AM to 8:00AM the value is 1.75 hrs, from 6:00AM to 10:00AM the value is 3.25, 6 to 12 PM 5:25, then 6:00 to 2:00PM the value is 7 ,from 6:00 4:00PM 8.75 hrs the 6:00 to 6:PM the value is 8.25 hrs. How do i make this in T-SQL codes and/or in SSRS expression codes. thank you.

for example this is the first shift.
Time Value
6 am* 8 am 1.75
6 am* 10 am 3.25
6 am* 12 pm 5.25
6 am* 2 pm 7
6 am* 4 pm 8.75
6 am* 6 pm 10.5

then second the second shift start at 10:PM
10 pm*12 am 1.75
10 pm*2 am 3.25
10 pm*4 am 5.25
10 pm*6 am 7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-23 : 03:44:04

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-23 : 03:51:33

THanks for the reply khtan.
i wanted to get the value:
Btw, its possible to put this in SSRS parameter? or select statement inside the query text in SSRS. I will use the result in computation.

for ex. right now the time is 3:50 PM the result is 5:25.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-23 : 04:13:28

quote:
for ex. right now the time is 3:50 PM the result is 5:25.

what is the logic ?

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-25 : 21:54:59

Hi Kthan,
The reason to automate this manhours table is to identify the current manhours by shift.
I have also a production dashboard that monitor the production output per hour and per shift which
this table has a relation on calculating the production output. the production operation start at 6:00AM, so beyond this time
the production output will be monitored by each team if they are on time or not. thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-25 : 22:49:39

[code]
declare @manhours table
(
time_start datetime,
time_end datetime,
value decimal(10,2)
)

insert into @manhours select '06:00', '08:00', 1.75
insert into @manhours select '06:00', '10:00', 3.25
insert into @manhours select '06:00', '12:00', 5.25
insert into @manhours select '06:00', '14:00', 7.00
insert into @manhours select '06:00', '15:00', 8.75
insert into @manhours select '06:00', '18:00', 10.5

insert into @manhours select '22:00', '00:00', 1.75
insert into @manhours select '22:00', '02:00', 3.25
insert into @manhours select '22:00', '04:00', 5.25
insert into @manhours select '22:00', '05:00', 7.00

declare @now datetime

select @now = '03:50'

select top 1 *
from @manhours
where (
@now >= '06:00'
and @now < '22:00'
and @now >= time_start
and @now < time_end
)
or (
(
@now < '06:00'
or @now >= '22:00'
)
and time_start > time_end
and (
@now >= time_start
or @now < time_end
)
)
order by time_end[/code]

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-25 : 23:35:25

Thank kthan for the prompt reply.
I tried your query and change the value of @now with 11:28AM, the result is 5:25
it should be 3.25. it capture the 12 :00PM value.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-26 : 00:38:52

Hi, Kthan,

Sorry, please disregard my previous reply. this is already correct. Thank you very very much.
Btw, do i need to create a table for this manhours? also how should i put this in SSRS parameter? thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-26 : 00:50:20

you should create a table to store the manhours information. Any changes to the rate or timing etc, you only need to update the table.

Sorry, I am not familiar with SSRS

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-26 : 01:31:11

Okay. Thank you very much kthan.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-30 : 01:12:04

Hi kthan,

May i add another query which is related to my post.
I wanted to create a select statement if it is within the 1st shift 6:00AM to 14:00PM then do the following query
then if with the 2nd shift 22:00PM to 06:00AM then do the following query. actually i have already the select statement but how can i break it down with the shifting.

How can i apply this in T-SQL. do i need to use the case statement or if statement.
Thank you in advance. your help is very much appreciated.

Here is a sample DDL:

Create table #sample
(ID nvarchar(5), DateandTime datetime, qty int)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 06:01:11.000',1)
Insert into #sample(ID, dateandtime,qty) values('TTTT','2014-05-30 07:41:11.000',1)
Insert into #sample(ID, dateandtime,qty) values('TXXX','2014-05-30 09:35:00.000',1)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 22:01:41.000',1)
Insert into #sample(ID, dateandtime,qty) values('TTTT','2014-05-30 23:15:00.000',1)
Insert into #sample(ID, dateandtime,qty) values('TXXX','2014-05-30 02:35:00.000',1)

Desired result:

ID---------A_SHIFT________________B_SHIFT
------------------------------------------
XXXX---------1------------------------1
TTTT---------1------------------------1
TXXX---------1------------------------1

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-23 : 03:02:01

Hi Kthan,

I have another query based on the solution that you have given. i wanted to have the first ship from 6:00 AM to 6:00PM abd the second ship wil start from 6:00PM to 6:00AM of the next day.

sample today is 6/23/2014 monday

1. the first shift start at 6:00AM and will end at 6:00PM of the day
then the second ship will start at 6:00PM then will end at 6:00AM of the next day 6/24/14. How could i encorporate this on the existing solution that you have given.

2. another thing is when the first ship is perform i need the second shift value is zero. then when the second ship is performed the last value of first shif will be as ease.

Thank you in advance for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources