Help with SQL Query

Question

Post reply

Help with SQL Query

reggiete

Old Hand

Points: 316

July 31, 2017 at 8:49 am

#343786

Hello All,

I am new here and a Novice to SQL. I am needing help on joining the data below into a single row. Please see below

Lets say I have a table with the below columns.

TicketHistoryID	TicketID	DateTime	Name	TicketActionID	ID1	ID2	TicketStateID	TicketBoxID	OwnerID	PriorityID	TicketCategoryID	Myrow
55784889	6465775	1/11/17 11:22 AM	NULL	1	284	0	2	327	0	1	1	1
55785202	6465775	1/11/17 11:36 AM	Jane Doe	2	3	2	1	327	0	1	1	2

I would like to join the data above into a single row to show like this. Where Myrow = 1 (Date Received) and Myrow = 2 Date Closed.

TicketID	Name	DateReceived	DateClosed
6465775	Jane Doe	1/11/17 11:22 AM	1/11/17 11:36 AM

Viewing 15 posts - 1 through 14 (of 14 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

This should get you started
SELECT TicketId ,NAME = max(NAME) ,DateReceived = min([DateTime]) ,DateClosed = max([DateTime]) FROM TABLE Group BY TicketId
Note that 'DateTime' is a datatype in SQL Server & should therefore not be used as a column name.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 2

What have you tried so far? There could be a few answers to this, but it also depends on your data(for example, will it always only have a maximum of 2 rows? Then instead of a JOIN you could use LEAD and a CTE (to avoid scanning your table twice), or even a GROUP Function.

Post what you've tried so far, and we can direct you on where you might be going wrong.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

reggiete Old Hand Points: 316 More actions · Answer 3

Thanks Phil. One more question. How would I use the above select query if I have multiple records. See Below

TicketHistoryID	TicketID	DateTime	Name	TicketActionID	ID1	ID2	TicketStateID	TicketBoxID	OwnerID	PriorityID	TicketCategoryID	Myrow
57440055	6702938	7/14/17 1:34 PM	NULL	1	284	0	2	327	0	1	1	1
57440833	6702938	7/14/17 2:12 PM	Jane Doe	2	3	2	1	228	0	1	1	2
57442401	6702938	7/14/17 3:40 PM	NULL	2	3	1	2	228	0	3	1	1
57443687	6702938	7/14/17 5:02 PM	Jane Doe	2	3	2	1	327	5194	1	1	2
57467800	6702938	7/17/17 7:32 PM	NULL	2	3	1	2	327	5194	3	1	1
57471861	6702938	7/18/17 9:37 AM	Jane Doe	2	3	2	1	228	0	1	1	2
57473103	6702938	7/18/17 10:26 AM	NULL	2	3	1	2	228	0	3	1	1
57476353	6702938	7/18/17 1:00 PM	Jane Doe	2	3	2	1	228	0	1	1	2
57478207	6702938	7/18/17 2:15 PM	NULL	2	3	1	2	228	0	3	1	1
57479526	6702938	7/18/17 3:00 PM	Jane Doe	2	3	2	1	327	0	1	1	2
57480034	6702938	7/18/17 3:15 PM	NULL	2	3	1	2	327	0	3	1	1
57480537	6702938	7/18/17 3:34 PM	Jane Doe	2	3	2	1	228	0	1	1	2

The above table would show Jane Doe 6 times with different dateopened/daeclosed

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

reggiete - Monday, July 31, 2017 9:16 AM
Thanks Phil. One more question. How would I use the above select query if I have multiple records. See Below

TicketHistoryID TicketID DateTime Name TicketActionID ID1 ID2 TicketStateID TicketBoxID OwnerID PriorityID TicketCategoryID Myrow
57440055 6702938 7/14/17 1:34 PM NULL 1 284 0 2 327 0 1 1 1
57440833 6702938 7/14/17 2:12 PM Jane Doe 2 3 2 1 228 0 1 1 2
57442401 6702938 7/14/17 3:40 PM NULL 2 3 1 2 228 0 3 1 1
57443687 6702938 7/14/17 5:02 PM Jane Doe 2 3 2 1 327 5194 1 1 2
57467800 6702938 7/17/17 7:32 PM NULL 2 3 1 2 327 5194 3 1 1
57471861 6702938 7/18/17 9:37 AM Jane Doe 2 3 2 1 228 0 1 1 2
57473103 6702938 7/18/17 10:26 AM NULL 2 3 1 2 228 0 3 1 1
57476353 6702938 7/18/17 1:00 PM Jane Doe 2 3 2 1 228 0 1 1 2
57478207 6702938 7/18/17 2:15 PM NULL 2 3 1 2 228 0 3 1 1
57479526 6702938 7/18/17 3:00 PM Jane Doe 2 3 2 1 327 0 1 1 2
57480034 6702938 7/18/17 3:15 PM NULL 2 3 1 2 327 0 3 1 1
57480537 6702938 7/18/17 3:34 PM Jane Doe 2 3 2 1 228 0 1 1 2
The above table would show Jane Doe 6 times with different dateopened/daeclosed

Based on the data which you have provided, what is the logic for associating any particular MyRow = 1 and MyRow = 2 row. Is it purely based on TicketId and DateTime?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

If you would be prepared to provide your sample data in the form of CREATE TABLE and INSERT scripts, you will find that people here will take the time to provide you with a coded solution. See the link in my signature for an explanation of how to do this.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 6

Phils logic looks like it will still work.
I converted your paste to consumable data
;WITH MyCTE([TicketHistoryID],[TicketID],[DateTime],[Name],[TicketActionID],[ID1],[ID2],[TicketStateID],[TicketBoxID],[OwnerID],[PriorityID],[TicketCategoryID],[Myrow]) AS ( SELECT CONVERT(int,'57440055'),CONVERT(int,'6702938'),CONVERT(datetime,'7/14/17 1:34 PM'),CONVERT(VARCHAR(30),NULL),CONVERT(int,'1'),CONVERT(int,'284'),CONVERT(int,'0'),CONVERT(int,'2'),CONVERT(int,'327'),CONVERT(int,'0'),CONVERT(int,'1'),CONVERT(int,'1'),CONVERT(int,'1') UNION ALL SELECT '57440833','6702938','7/14/17 2:12 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57442401','6702938','7/14/17 3:40 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57443687','6702938','7/14/17 5:02 PM','Jane Doe','2','3','2','1','327','5194','1','1','2' UNION ALL SELECT '57467800','6702938','7/17/17 7:32 PM',NULL,'2','3','1','2','327','5194','3','1','1' UNION ALL SELECT '57471861','6702938','7/18/17 9:37 AM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57473103','6702938','7/18/17 10:26 AM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57476353','6702938','7/18/17 1:00 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57478207','6702938','7/18/17 2:15 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57479526','6702938','7/18/17 3:00 PM','Jane Doe','2','3','2','1','327','0','1','1','2' UNION ALL SELECT '57480034','6702938','7/18/17 3:15 PM',NULL,'2','3','1','2','327','0','3','1','1' UNION ALL SELECT '57480537','6702938','7/18/17 3:34 PM','Jane Doe','2','3','2','1','228','0','1','1','2' ) SELECT TicketId ,NAME = max(NAME) ,DateReceived = min([DateTime]) ,DateClosed = max([DateTime]) FROM MyCTE Group BY TicketId

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

reggiete Old Hand Points: 316 More actions · Answer 7

Phil,

Thanks. The code below gets me to the data table I posted above.

Drop Table #Test

SELECT th.[TicketHistoryID]

,th.[TicketID]

,th.[DateTime]

,ag.Name

,th.[TicketActionID]

,th.[ID1]

,th.[ID2]

,th.[TicketStateID]

,th.[TicketBoxID]

,th.[OwnerID]

,th.[PriorityID]

,th.[TicketCategoryID]

,Case When th.TicketStateID=2 and th.ID1=3 or th.ID1=1 or th.ID1=284 Then 1

When th.TicketStateID=1 Then 2 End As Myrow

INTO #TEST

FROM [Visnetic Mailflow].[dbo].[TicketHistory] th

Left Join [Visnetic Mailflow].dbo.Agents ag on ag.AgentID = th.AgentID

Where th.TicketBoxID In (228,235,239,342,327) and th.DateTime>='2017-01-01' and th.TicketID = 6702938 and th.ID2 Not In (228,235,239,342,327)

order by th.DateTime Asc

The above code will create the table with the rows I posted above. The Myrow is based on ticketStateID.
TicketStateid = 2 means Open, TicketStateId = 1 means closed.

The desired output would be this. Whoever has a 2 in myrow is the user who closed the ticket. So Where Name is null means the ticket went into a queue and then Jane doe closed it. So I want to tie that together to show the output below.

TicketID	Name	DateReceived	DateClosed
6702938	Jane Doe	7/14/17 1:34 PM	7/14/17 2:12 PM
6702938	Jane Doe	7/14/17 3:40 PM	7/14/17 5:02 PM
6702938	Jane Doe	7/17/17 7:32 PM	7/18/17 9:37 AM
6702938	Jane Doe	7/18/17 10:26 AM	7/18/17 1:00 PM
6702938	Jane Doe	7/18/17 2:15 PM	7/18/17 3:00 PM
6702938	Jane Doe	7/18/17 3:15 PM	7/18/17 3:34 PM

reggiete Old Hand Points: 316 More actions · Answer 8

Lowell - Monday, July 31, 2017 10:36 AM
Phils logic looks like it will still work.
I converted your paste to consumable data
;WITH MyCTE([TicketHistoryID],[TicketID],[DateTime],[Name],[TicketActionID],[ID1],[ID2],[TicketStateID],[TicketBoxID],[OwnerID],[PriorityID],[TicketCategoryID],[Myrow]) AS ( SELECT CONVERT(int,'57440055'),CONVERT(int,'6702938'),CONVERT(datetime,'7/14/17 1:34 PM'),CONVERT(VARCHAR(30),NULL),CONVERT(int,'1'),CONVERT(int,'284'),CONVERT(int,'0'),CONVERT(int,'2'),CONVERT(int,'327'),CONVERT(int,'0'),CONVERT(int,'1'),CONVERT(int,'1'),CONVERT(int,'1') UNION ALL SELECT '57440833','6702938','7/14/17 2:12 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57442401','6702938','7/14/17 3:40 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57443687','6702938','7/14/17 5:02 PM','Jane Doe','2','3','2','1','327','5194','1','1','2' UNION ALL SELECT '57467800','6702938','7/17/17 7:32 PM',NULL,'2','3','1','2','327','5194','3','1','1' UNION ALL SELECT '57471861','6702938','7/18/17 9:37 AM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57473103','6702938','7/18/17 10:26 AM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57476353','6702938','7/18/17 1:00 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL SELECT '57478207','6702938','7/18/17 2:15 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL SELECT '57479526','6702938','7/18/17 3:00 PM','Jane Doe','2','3','2','1','327','0','1','1','2' UNION ALL SELECT '57480034','6702938','7/18/17 3:15 PM',NULL,'2','3','1','2','327','0','3','1','1' UNION ALL SELECT '57480537','6702938','7/18/17 3:34 PM','Jane Doe','2','3','2','1','228','0','1','1','2' ) SELECT TicketId ,NAME = max(NAME) ,DateReceived = min([DateTime]) ,DateClosed = max([DateTime]) FROM MyCTE Group BY TicketId

Thanks, Lowell. The code words but gives me the Min and Max between all records. I am need the Min and Max for each transaction. The output im looking for is

TicketID	Name	DateReceived	DateClosed
6702938	Jane Doe	7/14/17 1:34 PM	7/14/17 2:12 PM
6702938	Jane Doe	7/14/17 3:40 PM	7/14/17 5:02 PM
6702938	Jane Doe	7/17/17 7:32 PM	7/18/17 9:37 AM
6702938	Jane Doe	7/18/17 10:26 AM	7/18/17 1:00 PM
6702938	Jane Doe	7/18/17 2:15 PM	7/18/17 3:00 PM
6702938	Jane Doe	7/18/17 3:15 PM	7/18/17 3:34 PM

So I am needing to group by myrow. So for every Myrow 1+2 I need the min and max datetime. then go to the next grouping of my row 1+2 and give me the min and max. Hopefully that makes sense

Phil Parkin SSC Guru Points: 246965 More actions · Answer 9

Can you please explain how exactly the same TicketId can get repeatedly opened and closed? This process sounds a bit strange.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

reggiete Old Hand Points: 316 More actions · Answer 10

So the ticketid Is a uniqueID for a particular issue. So lets say I have an issue resetting my password. So a ticket is created, lets say its ticketID = 1.

So any correspondence regarding ticketid=1, is tracked and linked to ticketID #1. So the data I am showing above is the correspondence regarding a specific ticketid. So I am wanting to track from date open to date closed. Which is why I created myrow to mark when we received correspondence and when we replied (myrow#)

Phil Parkin SSC Guru Points: 246965 More actions · Answer 11

reggiete - Monday, July 31, 2017 12:29 PM
So the ticketid Is a uniqueID for a particular issue. So lets say I have an issue resetting my password. So a ticket is created, lets say its ticketID = 1.
So any correspondence regarding ticketid=1, is tracked and linked to ticketID #1. So the data I am showing above is the correspondence regarding a specific ticketid. So I am wanting to track from date open to date closed. Which is why I created myrow to mark when we received correspondence and when we replied (myrow#)

So I am wanting to track from date open to date closed

Fine, that makes sense, but your earlier responses suggest that you want to track
Date opened to date closed to date opened to date closed to date opened to date closed to date opened to date closed etc etc
for the same TicketId. That's the strange part: how can the same ticket be opened and closed repeatedly?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

reggiete Old Hand Points: 316 More actions · Answer 12

same ticketid can be opened and closed multiple times due to correspondence. There is a column labeled TicketHistoryID which is the unique ID for each transaction.

Its basically Parent to Child relationship. Parent being the TicketID and child being all the transactions that relate to that TicketID.

Transactions = email correspondence for a ticketID
Myrow#1 = Open ( when email is received from outside of the company (in this case Jane Doe))
Myrow#2 = Closed (responded to Jane Doe email) If jane doe responds back again then it will show in the data.

So from the data I posted above. Jane Doe submitted 6 emails to us and we responded to all 6 emails which linked to ticketID#6702938. In essence by ticketid I am trying to track the correspondence.

TicketID	Name	DateReceived	DateClosed
6702938	Jane Doe	7/14/17 1:34 PM	7/14/17 2:12 PM
6702938	Jane Doe	7/14/17 3:40 PM	7/14/17 5:02 PM
6702938	Jane Doe	7/17/17 7:32 PM	7/18/17 9:37 AM
6702938	Jane Doe	7/18/17 10:26 AM	7/18/17 1:00 PM
6702938	Jane Doe	7/18/17 2:15 PM	7/18/17 3:00 PM
6702938	Jane Doe	7/18/17 3:15 PM	7/18/17 3:34 PM

Lowell SSC Guru Points: 323518 More actions · Answer 13

this does what you are asking, I guess:
this assumes your a ticket is always opened and always closed, and forces an order via the Row_number so i can join the table against itself;
you could probably use lag or lead to do the same.

;WITH MyCTE([TicketHistoryID],[TicketID],[DateTime],[Name],[TicketActionID],[ID1],[ID2],[TicketStateID],[TicketBoxID],[OwnerID],[PriorityID],[TicketCategoryID],[Myrow])
AS
(
SELECT CONVERT(int,'57440055'),CONVERT(int,'6702938'),CONVERT(datetime,'7/14/17 1:34 PM'),CONVERT(VARCHAR(30),NULL),CONVERT(int,'1'),CONVERT(int,'284'),CONVERT(int,'0'),CONVERT(int,'2'),CONVERT(int,'327'),CONVERT(int,'0'),CONVERT(int,'1'),CONVERT(int,'1'),CONVERT(int,'1') UNION ALL
SELECT '57440833','6702938','7/14/17 2:12 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57442401','6702938','7/14/17 3:40 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57443687','6702938','7/14/17 5:02 PM','Jane Doe','2','3','2','1','327','5194','1','1','2' UNION ALL
SELECT '57467800','6702938','7/17/17 7:32 PM',NULL,'2','3','1','2','327','5194','3','1','1' UNION ALL
SELECT '57471861','6702938','7/18/17 9:37 AM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57473103','6702938','7/18/17 10:26 AM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57476353','6702938','7/18/17 1:00 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57478207','6702938','7/18/17 2:15 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57479526','6702938','7/18/17 3:00 PM','Jane Doe','2','3','2','1','327','0','1','1','2' UNION ALL
SELECT '57480034','6702938','7/18/17 3:15 PM',NULL,'2','3','1','2','327','0','3','1','1' UNION ALL
SELECT '57480537','6702938','7/18/17 3:34 PM','Jane Doe','2','3','2','1','228','0','1','1','2'
),
Tickets
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY TicketID ORDER BY [DateTime]) AS RW,*
FROM MyCTE
)
SELECT *
FROM Tickets T1
INNER JOIN Tickets T2
ON T1.[TicketID] = T2.[TicketID]
AND T1.[TicketHistoryID] < T2.[TicketHistoryID]
AND T1.[RW]=T2.RW + -1
WHERE T1.TicketStateID=2
AND T2.[TicketStateID] = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

reggiete Old Hand Points: 316 More actions · Answer 14

Lowell - Monday, July 31, 2017 1:00 PM
this does what you are asking, I guess:
this assumes your a ticket is always opened and always closed, and forces an order via the Row_number so i can join the table against itself;
you could probably use lag or lead to do the same.

;WITH MyCTE([TicketHistoryID],[TicketID],[DateTime],[Name],[TicketActionID],[ID1],[ID2],[TicketStateID],[TicketBoxID],[OwnerID],[PriorityID],[TicketCategoryID],[Myrow])
AS
(
SELECT CONVERT(int,'57440055'),CONVERT(int,'6702938'),CONVERT(datetime,'7/14/17 1:34 PM'),CONVERT(VARCHAR(30),NULL),CONVERT(int,'1'),CONVERT(int,'284'),CONVERT(int,'0'),CONVERT(int,'2'),CONVERT(int,'327'),CONVERT(int,'0'),CONVERT(int,'1'),CONVERT(int,'1'),CONVERT(int,'1') UNION ALL
SELECT '57440833','6702938','7/14/17 2:12 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57442401','6702938','7/14/17 3:40 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57443687','6702938','7/14/17 5:02 PM','Jane Doe','2','3','2','1','327','5194','1','1','2' UNION ALL
SELECT '57467800','6702938','7/17/17 7:32 PM',NULL,'2','3','1','2','327','5194','3','1','1' UNION ALL
SELECT '57471861','6702938','7/18/17 9:37 AM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57473103','6702938','7/18/17 10:26 AM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57476353','6702938','7/18/17 1:00 PM','Jane Doe','2','3','2','1','228','0','1','1','2' UNION ALL
SELECT '57478207','6702938','7/18/17 2:15 PM',NULL,'2','3','1','2','228','0','3','1','1' UNION ALL
SELECT '57479526','6702938','7/18/17 3:00 PM','Jane Doe','2','3','2','1','327','0','1','1','2' UNION ALL
SELECT '57480034','6702938','7/18/17 3:15 PM',NULL,'2','3','1','2','327','0','3','1','1' UNION ALL
SELECT '57480537','6702938','7/18/17 3:34 PM','Jane Doe','2','3','2','1','228','0','1','1','2'
),
Tickets
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY TicketID ORDER BY [DateTime]) AS RW,*
FROM MyCTE
)
SELECT *
FROM Tickets T1
INNER JOIN Tickets T2
ON T1.[TicketID] = T2.[TicketID]
AND T1.[TicketHistoryID] < T2.[TicketHistoryID]
AND T1.[RW]=T2.RW + -1
WHERE T1.TicketStateID=2
AND T2.[TicketStateID] = 1

THANK THANK THANK. I was able to tweak the code to what I needed. THANKS AGAIN