Find workshift from Date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Find workshift from Date

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-17 : 15:07:08

Here is the deal :

I have a table called records with two columns : ticket_num and close_date.

How do i find the workshift from close date if i have to consider the follwoing criteria for workshift

EUROPE 00:30 - 09:30
NORTH_AMERICA 09:30 - 18:30
ASIA_PACIFIC 18:30 - 00:30

Sample data :

34578 1222094796
38764 1222155746
39007 1223941579
39678 1244602591

I use the following query to pull the ticket_num and close_date.:

select
ticket_num,
dateadd (hh,-7,dateAdd(ss, close_date, '19700101'))[Close_Date],
from records

The idea results should be :
ticket_num close_date Workshift
34578 2008-09-22 07:46:36 EUROPE
38764 2008-09-23 00:42:26 EUROPE
39007 2008-10-13 16:46:19 NORTH_AMERICA
39678 2009-06-09 19:56:31 ASIA_PACIFIC

I tried using case statement and didn't work. Can someone help please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-17 : 15:26:33

Try this


DECLARE	@Sample TABLE
	(
		Ticket INT,
		CloseTime INT
	)

INSERT	@Sample
SELECT	34578, 1222094796 UNION ALL
SELECT	38764, 1222155746 UNION ALL
SELECT	39007, 1223941579 UNION ALL
SELECT	39678, 1244602591

DECLARE	@Workshift TABLE
	(
		Region VARCHAR(20),
		FromTime CHAR(5),
		ToTime CHAR(5)
	)

INSERT	@Workshift
SELECT	'EUROPE', '00:30', '09:30' UNION ALL
SELECT	'NORTH_AMERICA', '09:30', '18:30' UNION ALL
SELECT	'ASIA_PACIFIC', '18:30', '00:30'

SELECT		s.Ticket,
		s.CloseTime,
		s.theTime,
		w.Region
FROM		@Workshift AS w
INNER JOIN	(
			SELECT	Ticket,
				CloseTime,
				DATEADD(SECOND, CloseTime, '19691231 17:00') AS theTime
			FROM	@Sample
		) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-17 : 20:12:05

Forgive my Noviceness Peso.
I already have the table created
I declared the Workshift table
I inserted the values into it
and modified your query but I'm not able to incorporate this query to pull the desired results. Though your query works perfectly if used as such.

How an i use only this part (as my table is already constructed)?

DECLARE @Workshift TABLE
(
Region VARCHAR(20),
FromTime CHAR(5),
ToTime CHAR(5)
)

INSERT @Workshift
SELECT 'EUROPE', '00:30', '09:30' UNION ALL
SELECT 'NORTH_AMERICA', '09:30', '18:30' UNION ALL
SELECT 'ASIA_PACIFIC', '18:30', '00:30'

SELECT s.Ticket,
s.CloseTime,
s.theTime,
w.Region
FROM @Workshift AS w
INNER JOIN (
SELECT Ticket,
CloseTime,
DATEADD(SECOND, CloseTime, '19691231 17:00') AS theTime
FROM records
) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 22:27:18

just use this. Change @sample to records (your table name)


SELECT		s.Ticket,
		s.CloseTime,
		s.theTime,
		w.Region
FROM		@Workshift AS w
INNER JOIN	(
			SELECT	Ticket,
				CloseTime,
				DATEADD(SECOND, CloseTime, '19691231 17:00') AS theTime
			FROM	@Sample records
		) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime

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

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-21 : 01:04:47

Hi,

Thank you much for your inputs. When I Actually went to implement it, the query became long and i could not use it as a subquery. Is there any way i can add Regionn also for each row found out with thw query. can u still help please...

Select a.ticket_num [Ticket Number],a.Description,
dateadd (hh,-7,dateAdd(ss, opentime, '19700101'))[OpenTime],
a.Type, d.sym [priority], a.Status, (b.first_name + ' ' + ' ' + b.last_name) [Assignee], f.location [Location],
dateadd(hh,-7,dateAdd(ss, closetime, '19700101'))[CloseTime],
g.sym [Request Area], a.sla [SLA]
from records a
left outer join contact b on a.assignee = b.contact_uuid
left outer join pri d on a.priority = d.enum
left outer join location f on e.location_id = f.location_id
left outer join ctg g on a.category = g.persid
where b.last_name like 'John'
and dateadd (hh,-7,dateAdd(ss, closetime, '19700101')) >= '2009-03-01'
and dateadd (hh,-7,dateAdd(ss, closedate, '19700101')) < '2009-03-02'

Help pleaseeeeeeeeeeeee...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 01:28:18

what's the name of your actual table for @Workshift ?

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

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-23 : 03:25:09

Apologies for the delay KW.
i don't have a table for workshift. I have a table records which has close date and I want to find out how many tickets are closed in which shift with other ticket details (which i find using the query in my last post. The shift time is as given below :

EUROPE 00:30 - 09:30
NORTH_AMERICA 09:30 - 18:30
ASIA_PACIFIC 18:30 - 00:30
I don't want to create a permanent table but would like to work on temp table while the query is run.
How do i do it?

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-23 : 03:27:23

forgot to add that time is in PST and stored in UNIX timestamp format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-23 : 04:05:07

Make it a derived table then!

DECLARE	@Sample TABLE
	(
		Ticket INT,
		CloseTime INT
	)

INSERT	@Sample
SELECT	34578, 1222094796 UNION ALL
SELECT	38764, 1222155746 UNION ALL
SELECT	39007, 1223941579 UNION ALL
SELECT	39678, 1244602591

SELECT		s.Ticket,
		s.CloseTime,
		s.theTime,
		w.Region
FROM		(
			SELECT	'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
			SELECT	'NORTH_AMERICA', '09:30', '18:30' UNION ALL
			SELECT	'ASIA_PACIFIC', '18:30', '00:30'
		) AS w
INNER JOIN	(
			SELECT	Ticket,
				CloseTime,
				DATEADD(SECOND, CloseTime, '19691231 17:00') AS theTime
			FROM	@Sample
		) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-24 : 00:46:49

how do i make joins to other tables. The normal query that i use is :

Select a.ticket_num [Ticket Number],a.Description,
dateadd (hh,-7,dateAdd(ss, opentime, '19700101'))[OpenTime],
a.Type, d.sym [priority], a.Status, (b.first_name + ' ' + ' ' + b.last_name) [Assignee], f.location [Location],
dateadd(hh,-7,dateAdd(ss, closetime, '19700101'))[CloseTime],
g.sym [Request Area], a.sla [SLA]
from records a
left outer join contact b on a.assignee = b.contact_uuid
left outer join pri d on a.priority = d.enum
left outer join location f on e.location_id = f.location_id
left outer join ctg g on a.category = g.persid
where b.last_name like 'John'
and dateadd (hh,-7,dateAdd(ss, closetime, '19700101')) >= '2009-03-01'
and dateadd (hh,-7,dateAdd(ss, closetime, '19700101')) < '2009-03-02'

i want to modify this query so that it displays one more column with the workshift

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-24 : 01:44:26

Where is table aliased as e?

SELECT		s.Ticket_Num AS [Ticket Number],
		s.Description,
		s.OpenTime,
		s.Type,
		d.sym AS [priority],
		s.Status,
		b.first_name + '  ' + b.last_name AS [Assignee],
		f.location AS Location, 
		s.CloseTime, 
		g.sym AS [Request Area],
		s.sla,
		s.theTime,
		w.Region
FROM		(
			SELECT	'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
			SELECT	'NORTH_AMERICA', '09:30', '18:30' UNION ALL
			SELECT	'ASIA_PACIFIC', '18:30', '00:30'
		) AS w
INNER JOIN	(
			SELECT	Ticket_Num,
				Description,
				CloseTime,
				DATEADD(SECOND, OpenTime, '19691231 17:00') AS OpenTime,
				Type,
				Assignee,
				Priority,
				Category,
				SLA,
				DATEADD(SECOND, CloseTime, '19691231 17:00') AS CloseTime
			FROM	Records
		) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime
LEFT JOIN	contact AS b on b.contact_uuid = s.assignee
			and b.last_name like 'John' 
LEFT JOIN	pri AS d on d.enum = s.priority
LEFT JOIN	location AS f on f.location_id = e.location_id
LEFT JOIN	ctg AS g on g.persid = s.category
WHERE		s.CloseTime >= '2009-03-01' 
		and s.closetime < '2009-03-02'

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 15:28:21

i worked on the query but i get an error :

Msg 8116, Level 16, State 1, Line 2
Argument data type datetime is invalid for argument 2 of dateadd function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 15:58:17

Which query are you using?

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 16:51:21

i tried to use the one in the last post.
ok I'm making it very simple so to include minimum values.

Below is the working query which i use to pull data. I just want one more column called region based on workshift :

Select a.ticket_num [Ticket Number],
dateadd (hh,-7,dateAdd(ss, opentime, '19700101'))[Opentime],
d.sym [priority],
(b.last_name) [assignee],
dateadd(hh,-7,dateAdd(ss, closetime, '19700101'))[Closetime]
from records a
left outer join contact b on a.assignee = b.contact_uuid
left outer join pri d on a.priority = d.enum
where b.last_name LIKE '%a%'
and dateadd (hh,-7,dateAdd(ss, closetime, '19700101')) >= '2009-07-01'
and dateadd (hh,-7,dateAdd(ss, closetime, '19700101')) < '2009-07-02'

Based on your suggestion, I have tried to use the follwoing query and it does not work

SELECT s.Ticket_Num AS [Ticket Number],
s.opentime
d.sym AS [priority],
b.first_name AS [Assignee],
s.CloseTime,
s.theTime,
w.Region
FROM (
SELECT 'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
SELECT 'NORTH_AMERICA', '09:30', '18:30' UNION ALL
SELECT 'ASIA_PACIFIC', '18:30', '00:30'
) AS w
INNER JOIN (
SELECT Ticket_Num,
CloseTime,
DATEADD(SECOND, OpenTime, '19691231 17:00') AS OpenTime,
Assignee,
Priority,
DATEADD(SECOND, CloseTime, '19691231 17:00') AS CloseTime
FROM Records
) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime
LEFT JOIN contact AS b on b.contact_uuid = s.assignee
and b.last_name like 'John'
LEFT JOIN pri AS d on d.enum = s.priority
WHERE s.CloseTime >= '2009-07-01'
and s.closetime < '2009-07-02'

To clarify, the opentime and close time are in UNIX timestamp

what am i doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 17:02:45

It depends on what you mean by "Does not work".

1. Do you get an error?
2. Do you get some results but not all?
3. Do you get results at all?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 17:05:30

Originally you only had one time column, theTime.
And you are still using that column in the query, even when you revelead you are usig two columns.

SELECT		s.Ticket_Num AS [Ticket Number],
		s.opentime
		d.sym AS [priority],
		b.first_name AS [Assignee],
		s.CloseTime, 
		s.theTime,
		w.Region
FROM		(
			SELECT 'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
			SELECT 'NORTH_AMERICA', '09:30', '18:30' UNION ALL
			SELECT 'ASIA_PACIFIC', '18:30', '00:30'
		) AS w
INNER JOIN	(
			SELECT	Ticket_Num,
				CloseTime,
				DATEADD(SECOND, OpenTime, '19691231 17:00') AS OpenTime,
				Assignee,
				Priority,
				DATEADD(SECOND, CloseTime, '19691231 17:00') AS CloseTime
			FROM	Records
		) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime
LEFT JOIN	contact AS b on b.contact_uuid = s.assignee
			AND b.last_name = 'John' 
LEFT JOIN	pri AS d ON d.enum = s.priority
WHERE		s.CloseTime >= '2009-07-01' 
		AND s.closetime < '2009-07-02

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-31 : 17:23:16

It runs sucessfully now.Thanks Peso. The only part not working is the workshit. It shows North America and Europe but not Asia Pacific. IS there somthing that i need to change?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-31 : 17:25:07

Please post your full query as of now.
I can't tell what to change otherwise.

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-08-02 : 15:32:48

This is the query which is used. It does not show ASIA_PACIFIC. Something wrong?

SELECT s.ticket_num AS [Ticket Number],
s.theTime,
w.Region
FROM (
SELECT 'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
SELECT 'NORTH_AMERICA', '09:30', '18:30' UNION ALL
SELECT 'ASIA_PACIFIC', '18:30', '00:30'
) AS w
INNER JOIN (
SELECT ticket_num,
dateadd(hh,-7,dateAdd(ss, closetime, '19700101')) as thetime
FROM records
) AS s
ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime
WHERE s.thetime >= '2009-07-01'
AND s.thetime < '2009-07-05'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-02 : 16:48:25

[code]SELECT s.ticket_num AS [Ticket Number],
s.theTime,
w.Region
FROM (
SELECT 'EUROPE' AS Region, '00:30' AS FromTime, '09:30' AS ToTime UNION ALL
SELECT 'NORTH_AMERICA', '09:30', '18:30' UNION ALL
SELECT 'ASIA_PACIFIC', '18:30', '24:00' UNION ALL
SELECT 'ASIA_PACIFIC', '00:00', '00:30'
) AS w
INNER JOIN (
SELECT ticket_num,
dateAdd(second, closetime, '19691231 17:00') as thetime
FROM records
) AS s ON CONVERT(CHAR(5), s.theTime, 108) BETWEEN w.FromTime AND w.ToTime
WHERE s.thetime >= '2009-07-01'
AND s.thetime < '2009-07-05'[/code]

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-08-03 : 15:18:44

Peso, There is one problem : It does not show records between 00:00 and 00:30. Any help?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources