Need help with a query

Question

Post reply

Need help with a query

charlesafarr

SSC Veteran

Points: 252
More actions
August 13, 2013 at 11:34 am

#276878

Hi all,
I need some help here. I posted in the wrong forum and i appreciated the help received, but i forgot the db was 2008R2. So here goes the issue.
DECLARE @testing AS TABLE (
AgentID INT,
ExcepCodeDetailName VARCHAR(10),
Detail_Start_Time DATETIME,
Detail_End_Time DATETIME
)
INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Break', '2013-08-06 09:00:00', '2013-08-06 09:10:00')
INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Lunch', '2013-08-06 13:00:00', '2013-08-06 14:00:00')
SELECT * FROM @testing
-------------------Results
12345 Break 2013-08-06 09:00:00.000 2013-08-06 09:10:00.000
12345 Lunch 2013-08-06 13:00:00.000 2013-08-06 14:00:00.000
-----------Results needed
12345 Break 2013-08-06 09:00:00.000 2013-08-06 09:10:00.000
12345 Open 2013-08-06 09:10:00.000 2013-08-06 13:00:00.000
12345 Lunch 2013-08-06 13:00:00.000 2013-08-06 14:00:00.000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Try this:

DECLARE @testing AS TABLE (

AgentID INT,

ExcepCodeDetailName VARCHAR(10),

Detail_Start_Time DATETIME,

Detail_End_Time DATETIME

)

INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Break', '2013-08-06 09:00:00', '2013-08-06 09:10:00')

INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Lunch', '2013-08-06 13:00:00', '2013-08-06 14:00:00')

SELECT * FROM @testing

INSERT @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time)

SELECT AgentId, 'Open',

MAX( CASE WHEN ExcepCodeDetailName = 'Break' THEN Detail_End_Time END),

MAX( CASE WHEN ExcepCodeDetailName = 'Lunch' THEN Detail_Start_Time END)

FROM @testing

GROUP BY AgentId

SELECT * FROM @testing

ORDER BY AgentId, Detail_Start_Time

charlesafarr SSC Veteran Points: 252 More actions · Answer 2

Thank You very much for your response.

I have solved that issue, but unfortunately using the ExcepCodeDetailName wouldn't be an option as there are over 200 of them and not every agents record would be this way. That is why i am having an issue with adding the record. thoughts?

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

laurie-789651

SSCertifiable

Points: 7680

August 13, 2013 at 2:24 pm

#1641126

What exactly do you want to do?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

not sure if this will help, buy may set you on your way

http://www.sqlservercentral.com/articles/T-SQL/71550/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

I didn't realize you'd reposted this problem.

Here's the SQL 2008 R2 compatible solution I posted in the SQL 2012 forum to your question.

http://www.sqlservercentral.com/Forums/FindPost1484043.aspx

Actually it should be compatible with SQL 2005 also. My suggestion was to treat your base data as islands and convert them to gaps (to get the Open time slots).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

WITH CTE AS (

SELECT AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time,

ROW_NUMBER() OVER(PARTITION BY AgentID ORDER BY Detail_Start_Time) AS rn

FROM @testing)

SELECT AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time

FROM @testing

UNION ALL

SELECT a.AgentID, 'Open', a.Detail_End_Time, b.Detail_Start_Time

FROM CTE a

INNER JOIN CTE b ON b.AgentID = a.AgentID

AND b.rn = a.rn + 1

AND b.Detail_Start_Time > a.Detail_End_Time

ORDER BY AgentID, Detail_Start_Time;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537