May 23, 2014 at 8:03 am
Hi,
Need to calculate Open Ist Action and Close 1st Action from attached worksheet.
Open Ist Action :
Min,max and AVG from Order open to first response
For MaxDate
OrderID 93207 , when NotificationType = REQ_IN_QUEUE and RecipientID <> 637 , get minimum (NotDate) by each OrderID
For MinDate
Will be when the OrderID was opened
Then need to find average time for that Work OrderID
Close First Action :
Min, Max, and Average time from ticket open to closure
If YEAR(DateCompleted ) in 1970 or 1969 or less
then MinDate and MaxDate for OrderID = ''
ELSE
MinDate and MaxDate
Thanks,
PSB
May 23, 2014 at 8:22 am
Can you post your sample data in a consumable format? Create table and insert statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2014 at 8:35 am
Sean Lange (5/23/2014)
Can you post your sample data in a consumable format? Create table and insert statements.
Agreed. First off I don't open stuff other than query plans and text files from people on the web. Second I can't (conveniently) write a TSQL query against an excel spreadsheet.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2014 at 9:14 am
CREATE TABLE #Order
(
OrderID INT,
DateCreated DATETIME,
DateCompleted DATETIME,
NotificationID INT,
NotDaTE DATETIME,
RecipientID INT,
NotificationType VARCHAR(20)
)
INSERT INTO #Order (OrderID,DateCreated,DateCompleted,NotificationID,NotDaTE,RecipientID,NotificationType)
SELECT 93207,'5/22/2014 08:16:03.000','5/23/2014 10:52.000',601780,'5/22/2014 10:16:19.000',48603,'REQREPLY'
UNION
SELECT 93207,'5/22/2014 08:16:03.000', '5/23/2014 10:52.000', 601779,'5/22/2014 10:16:20.000',16242,'REQTECH'
UNION
SELECT 93207,'5/22/2014 08:16:03.000','5/23/2014 10:52.000',601778,'5/22/2014 10:16:50.000',637,'REQ_IN_QUEUE'
UNION
SELECT 93207,'5/22/2014 08:16:03.000','5/23/2014 10:52.000',601785,'5/22/2014 10:19:50.000',47464,'REQTECH'
UNION
SELECT 93207,'5/22/2014 08:16:03.000','5/23/2014 10:52.000',601784,'5/22/2014 10:19:22.000',47464,'REQ_IN_QUEUE'
UNION
SELECT 93207,'5/22/2014 08:16:03.000','5/23/2014 10:52.000',601786,'5/22/2014 10:22:22.000',48603,'REQREPLY'
UNION
SELECT 93207,'5/22/2014 08:16:03.000', '5/23/2014 10:52.000',601788,'5/22/2014 10:21:3.000',48603,'REQREPLY'
UNION
SELECT 93207,'5/22/2014 08:16:03.000' , '5/23/2014 10:52.000' ,601784,'5/22/2014 10:29:22.000',47469,'REQ_IN_QUEUE'
UNION
SELECT 93208,'5/23/2014 08:16:03.000','12/31/1969 19:00.000',601901,'5/23/2014 10:16:19.000',48603,'REQREPLY'
UNION
SELECT 93208,'5/23/2014 08:16:03.000', '12/31/1969 19:00.000', 601902,'5/23/2014 10:16:20.000',16242,'REQTECH'
UNION
SELECT 93208,'5/23/2014 08:16:03.000','12/31/1969 19:00.000',601903,'5/23/2014 10:16:50.000',637,'REQ_IN_QUEUE'
UNION
SELECT 93208,'5/23/2014 08:16:03.000','12/31/1969 19:00.000',601904,'5/23/2014 10:19:50.000',47464,'REQTECH'
UNION
SELECT 93208,'5/23/2014 08:16:03.000','12/31/1969 19:00.000',601905,'5/23/2014 10:19:22.000',47464,'REQ_IN_QUEUE'
UNION
SELECT 93208,'5/23/2014 08:16:03.000','12/31/1969 19:00.000',601906,'5/23/2014 10:22:22.000',48603,'REQREPLY'
UNION
SELECT 93208,'5/23/2014 08:16:03.000', '12/31/1969 19:00.000',601907,'5/23/2014 10:21:3.000',48603,'REQREPLY'
SELECT * FROM #Order
DROP TABLE #Order
DESIRED Results :
OrderIDTypes MinDate MaxDate AvgDate
93207'Open 1st Action' '5/22/2014 08:16:03.000' '5/22/2014 10:19:22.000'
93207'Close 1st Action' '5/22/2014 08:16:03.000' '5/23/2014 10:52.000'
93208'Open 1st Action' '5/23/2014 08:16:03.000' '5/23/2014 10:19:22.000'
93208'Close 1st Action' NULL NULL
Thanks,
PSB
May 23, 2014 at 9:20 am
Can you try to explain more clearly the desired output? Do you really have orders created yesterday that were completed more than 40 years ago? This would be a lot easier if your sample data had dates more consistent with the real situation. It is difficult to know if a query has the correct data because the values for created and completed are all the same for each order.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2014 at 9:26 am
If the Order has not been completed yet then it shows as 1969-12-31 19:00:00.000 or much older dates..
May 23, 2014 at 9:33 am
PSB (5/23/2014)
If the Order has not been completed yet then it shows as 1969-12-31 19:00:00.000 or much older dates..
Why not use NULL instead of a derived value?
As I asked previously, can you please explain your desired output? I am not understanding what you want. I can see the values but I don't understand the logic behind it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2014 at 9:51 am
Need to find the following:
Open 1st Action :
-Min, Max, and Average time from Order open to first response
Close 1st Action:
-Min, Max, and Average time from Order open to closure
--------------------------------------------------------------------------
Open 1st Action :
MinDate:
Order Open Time is Min Time and to be tracked as MinDate
First Response aka MaxDate
First response is when it was moved to a group from the time it was first opened. That can be tracked by minimum(NotDate) when NotificationType = REQ_IN_QUEUE and RecipientID <> 637
This First response time is to be tracked as MaxDate
Get AVG between these date
Close 1st Action:
WHEN DateCompleted is NULL or '1969-12-31 19:00:00.000' (not completed yet) then both mindate,maxdate,avgdate is null or ''
Else
Order Open Time is Min Time and to be tracked as MinDate
OrderClose time is close time and to be tracked as MaxDate
Get AVG between these date
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply