Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Need to Create Function or Procedure to Calculate Dates/Times - Microsoft, SQL Server, 2000, SQL, SQL Server 2000

I am trying to add the time spent between each SCR.  I am trying to create some SQL to get this information but the time and date is in one field.  Here is what I have

1      SCR 1      Initiate      In Work      7/7/06 9:54 AM
2      SCR 1      In Work      To QA      7/7/06 10:28 AM
3      SCR 1      To QA      In Work      7/7/06 11:10 AM
4      SCR 1      In Work      To QA      7/7/06 11:16 AM
5      SCR 1      To QA      In Work      7/7/06 11:16 AM
6      SCR 1      In Work      To QA      7/7/06 11:22 AM
7      SCR 1      To QA      Ready for Production       7/7/06 11:22 AM
8      SCR 1      In Work      To QA      7/7/06 11:24 AM
9      SCR 1      To QA      Ready for Production       7/7/06 11:24 AM



Here is the report that I want to get to management.


SCR_ID      Process      HoursinQueue
SCR 1      Initiate      5.5
SCR 1      In Work      8
SCR 1      To QA      45


If you need additional information please let me know.
0
jedwards_2
Asked:
jedwards_2
  • 9
  • 8
1 Solution
 
Cvijo123Commented:
to be sure i dont understand how did u get those numbers in result set (i can only asume they are just test data)

what u need to do .. time differance between each row you inserted grouped by your SCR_ID ?
SCR1 Initiate 0
SCR1 InWork 00:34
SCR1 To QA 00:42
SCR1 InWork 00:04
SCR1 To QA 00:00
SCR1 InWork 00:08

or something else u had in mind? if you mybe need to group by Process too then how would you like to calculate those time ?
0
 
jedwards_2Author Commented:
Yes this is how the data actually resides in the system. I know it sucks.

How would you get the time difference between each row.  THe information that you displayed is exactly what I am looking for.  After I get that information I am going to Group By Process so I can add the time calculations.  I think I would have to create a stored proc or function, but I don't have any idea where to start.  THis is the last piece of the report and I just can't finish it.  Your help will be greatly appreciated.
0
 
Kevin CrossChief Technology OfficerCommented:
I don't know if there is more data NOT shown here that will yield the results OR maybe I am misunderstanding which duration is the queue time, but here would be my solution but I get different values that what you have.  Think I get 0, 0.8, and 0.7 respectively.
DECLARE @scr AS TABLE (ID INT, name NVARCHAR(10), status_begin NVARCHAR(50), status_end NVARCHAR(50), date_begin datetime)
 
INSERT INTO @scr
SELECT 1, 'SCR 1', 'Initiate', 'In Work', '7/7/06 9:54 AM'
UNION ALL SELECT 2,'SCR 1','In Work','To QA','7/7/06 10:28 AM'
UNION ALL SELECT 3,'SCR 1','To QA','In Work','7/7/06 11:10 AM'
UNION ALL SELECT 4,'SCR 1','In Work','To QA','7/7/06 11:16 AM'
UNION ALL SELECT 5,'SCR 1','To QA','In Work','7/7/06 11:16 AM'
UNION ALL SELECT 6,'SCR 1','In Work','To QA','7/7/06 11:22 AM'
UNION ALL SELECT 7,'SCR 1','To QA','Ready for Production',' 7/7/06 11:22 AM'
UNION ALL SELECT 8,'SCR 1','In Work','To QA','7/7/06 11:24 AM'
UNION ALL SELECT 9,'SCR 1','To QA','Ready for Production',' 7/7/06 11:24 AM'
 
;WITH scrCTE AS (
	SELECT *,
	row_number() OVER (PARTITION BY name ORDER BY ID) AS statusChangeID
	FROM @scr
)
SELECT s1.name AS SCR_ID, s1.status_begin AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, IsNull(s2.date_begin, s1.date_begin), s1.date_begin)) * 1.0/60, 1) AS HoursinQueue
FROM scrCTE s1 LEFT JOIN scrCTE s2
ON s1.name = s2.name AND s1.statusChangeID-1=s2.statusChangeID
GROUP BY s1.name, s1.status_begin

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
Basics of the solution is to use a self join to make it work.  I used the CTE to illustrate if data had multiple SCR in it that didn't make the autonumber go sequentially you could create you own nice sequence.

Other way to do it without a nice sequence is use a subquery that goes and gets the maximum ID that is less than the ID you are currently on and replace null with current datetime like I am doing above.  This will make initial step 0 hours in queue and then subquent will be datediff between current date and date of previous record.
0
 
jedwards_2Author Commented:
The results were test data in my original email.  Yes, I have more data, but what i am going to do is add my SQL statement  which pulls all data where you are hardcoding the data.  I am about to try it out in a bit.  Have a west coast meeting in 8 minutes.  I will let you know if that works.
0
 
Kevin CrossChief Technology OfficerCommented:
And FYI, everything above the ;WITH scrCTE was only to give me test data to ensure my query worked with your expected results.

For your solution you can just replace @scr in this statement:
;WITH scrCTE AS (
      SELECT *,
      row_number() OVER (PARTITION BY name ORDER BY ID) AS statusChangeID
      FROM @scr
)
...put in your actual tablename and replace name with real column (e.g. SCR_ID) and change ID to be your real PK field or you can use the date field since they should go in sequence anyway.  Might be best to ensure no negative datediff's.

In the select piece of code immediately -- same thing -- just replace with your actual column names.  The column name for statusChangeID and table names there don't need changing as they should be CTE and row_number result as coded.
0
 
jedwards_2Author Commented:
This is what I have so far.  How would I execute this?  WOuld I place this in a stored proc. or function?  How would you go about executing the information below.  Once I execute the statement, I think we should be done.  I can modify anything, but creating from scratch is crazy.  I don't want to crash the server because I am doing something wrong.  See below....  All I did was change the way how the data was gather.  

DECLARE @scr AS TABLE (ID INT, name NVARCHAR(10), status_begin NVARCHAR(50), status_end NVARCHAR(50), date_begin datetime)
 
INSERT INTO @scr
SELECT
th.ID, th.TagID, th.Value1, th.Value2, Date
FROM [dbo].[TransactionHistory] th
 
;WITH scrCTE AS (
      SELECT *,
      row_number() OVER (PARTITION BY name ORDER BY ID) AS statusChangeID
      FROM @scr
)
SELECT s1.name AS SCR_ID, s1.status_begin AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, IsNull(s2.date_begin, s1.date_begin), s1.date_begin)) * 1.0/60, 1) AS HoursinQueue
FROM scrCTE s1 LEFT JOIN scrCTE s2
ON s1.name = s2.name AND s1.statusChangeID-1=s2.statusChangeID
GROUP BY s1.name, s1.status_begin
0
 
jedwards_2Author Commented:
Man, this attach code snippet is cool.  Here is the code that I have right now.
DECLARE @scr AS TABLE (ID INT, name NVARCHAR(10), status_begin NVARCHAR(50), status_end NVARCHAR(50), date_begin datetime)
 
INSERT INTO @scr
SELECT 
ncth.NCTransactionID, ncth.TagID, ncth.Value1, ncth.Value2, Date
FROM [VoughtNC].[dbo].[NCTransactionHistory] ncth
WHERE
ncth.TransactionTypeID = '22'
AND ncth.TAGID = 'WT20067793716384'
 
;WITH scrCTE AS (
	SELECT *,
	row_number() OVER (PARTITION BY name ORDER BY ID) AS statusChangeID
	FROM @scr
)
SELECT s1.name AS SCR_ID, s1.status_begin AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, IsNull(s2.date_begin, s1.date_begin), s1.date_begin)) * 1.0/60, 1) AS HoursinQueue
FROM scrCTE s1 LEFT JOIN scrCTE s2
ON s1.name = s2.name AND s1.statusChangeID-1=s2.statusChangeID
GROUP BY s1.name, s1.status_begin

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
So is it working for you?  Because of the DECLARE and INSERT statement before the select you would have to put this in a function or stored procedure; however, if you just want to use as a select for say a view you can just go straight to your data table.


WITH scrCTE AS (
	SELECT
	row_number() OVER (PARTITION BY ncth.TagID ORDER BY ID) AS statusChangeID,
ncth.NCTransactionID AS ID, ncth.TagID AS name, ncth.Value1 AS status_begin, ncth.Value2 AS status_end, Date AS date_begin
FROM [VoughtNC].[dbo].[NCTransactionHistory] ncth
WHERE
ncth.TransactionTypeID = '22'
AND ncth.TAGID = 'WT20067793716384'
)
SELECT s1.name AS SCR_ID, s1.status_begin AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, IsNull(s2.date_begin, s1.date_begin), s1.date_begin)) * 1.0/60, 1) AS HoursinQueue
FROM scrCTE s1 LEFT JOIN scrCTE s2
ON s1.name = s2.name AND s1.statusChangeID-1=s2.statusChangeID
GROUP BY s1.name, s1.status_begin

Open in new window

0
 
jedwards_2Author Commented:
Okay, I am erroring out everywhere.  SInce you have most of the code for this, could you provide me the code snippet that I would put in the function.  where should I put all this information.  Truly I want my results to be in a VIEW.  Is this possible?
0
 
jedwards_2Author Commented:
WOuld I do it this way.  The one major error I am getting when trying to run this through the SQL Analyzer is row_number is not a recognizable function name.
SELECT s1.name AS SCR_ID, s1.status_begin AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, IsNull(s2.date_begin, s1.date_begin), s1.date_begin)) * 1.0/60, 1) AS HoursinQueue
FROM (SELECT
	row_number() OVER (PARTITION BY ncth.TagID ORDER BY ID) AS statusChangeID,
ncth.NCTransactionID AS ID, ncth.TagID AS name, ncth.Value1 AS status_begin, ncth.Value2 AS status_end, Date AS date_begin
FROM [VoughtNC].[dbo].[NCTransactionHistory] ncth
WHERE
ncth.TransactionTypeID = '22'
AND ncth.TAGID = 'WT20067793716384') scrCTE s1 LEFT JOIN scrCTE s2
ON s1.name = s2.name AND s1.statusChangeID-1=s2.statusChangeID
GROUP BY s1.name, s1.status_begin

Open in new window

0
 
jedwards_2Author Commented:
Just did some research and the OVER function will not work.  They are using SQL Server 2000
0
 
Kevin CrossChief Technology OfficerCommented:
Ok, sorry the above said SQL 2005.  I have to run out right now, but can give you an example later of what I was talking about with doing this by looking at the MAX date that is less than the one you are on.  That will work for SQL 2000.
0
 
Kevin CrossChief Technology OfficerCommented:
Hopefully I didn't mistype anything, but here is an example of what I meant:
+Match up the records that occur before this record and have the same TagID.
+Get the max date from those records as it will be the one immediately before this.
+If not record found (as in first instance), then use the same date as current record to get 0.

SELECT ncth.TagID AS SCR_ID, ncth.Value1 AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, 
IsNull((SELECT MAX(Date) FROM [VoughtNC].[dbo].[NCTransactionHistory] ncthb WHERE ncthb.NCTransactionID < ncth.NCTransactionID AND ncthb.TagID = ncth.TagID)
, ncth.Date), ncth.Date)) * 1.0/60, 1) AS HoursinQueue
FROM [VoughtNC].[dbo].[NCTransactionHistory] ncth
WHERE ncth.TransactionTypeID = '22'
AND ncth.TAGID = 'WT20067793716384'
GROUP BY ncth.TagID, ncth.Value1

Open in new window

0
 
jedwards_2Author Commented:
When I run the SQL above this is the error message that I get.

Server: Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
0
 
Kevin CrossChief Technology OfficerCommented:
Arg.  I just typed it up and couldn't parse since I don't have those tables, I should have caught that.  You have to do in two different steps.  Just got carried away and tried to consolidate to one.


SELECT TagID AS SCR_ID, Value1 AS Process
-- calculate how long before this status started and previous ended
-- if no previous, make this 0
-- get in minutes for precision, then divide by 60 to get in hours
, ROUND(SUM(DateDiff(mi, preDate, Date)) * 1.0/60, 1) AS HoursinQueue
FROM (SELECT *
, IsNull((SELECT MAX(Date) FROM [VoughtNC].[dbo].[NCTransactionHistory] ncthb WHERE ncthb.NCTransactionID < ncth.NCTransactionID AND ncthb.TagID = ncth.TagID)
, ncth.Date) AS prevDate
FROM [VoughtNC].[dbo].[NCTransactionHistory] ncth
WHERE ncth.TransactionTypeID = '22'
AND ncth.TAGID = 'WT20067793716384') derived
GROUP BY TagID, Value1

Open in new window

0
 
jedwards_2Author Commented:
It worked.  Although, I ended up creating some SQL also which kind of looks accurate also.  Take a look and let me know what you think.  I can truly say that I am at the end of this question.  Just let me know what you think about what I done.

select a.TagID, a.xStat, Sum(a.MinutesinQueue) as Queue
from (select
	x.TagID,
    x.NCTransactionID xId,
    y.NCTransactionID yId,
    x.Value2 xStat,
    y.Value2 yStat, 
	x.Date xDate,
	y.Date yDate,
 	DATEDIFF(minute, x.Date, y.Date) as MinutesinQueue
     
from 
    NCTransactionHistory x 
    left join NCTransactionHistory y on x.NCTransactionID + 1 = y.NCTransactionID
WHERE x.TransactionTypeID = '22'
--AND x.TAGID = 'WT20067793716384'
AND x.TAGID = y.TagID) a
 
GROUP BY a.TagID, a.xStat

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Think that will work if the NCTransactionID's go in sequence by 1 for a given TagID.  I didn't want to make that assumption with data as in most systems you have multiple SCR projects going on at once, so you could get:

1 SCR1 ...
2 SCR2 ...
3 SCR1 ...
4 SCR3 ...

So that is why I was using row_number()  (OVER statement) for SQL 2005 and the subquery for SQL 2000.

Hope that makes sense.  If the above works with your data though, there doesn't appear to be anything else wrong with it.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now