Solved

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

Posted on 2008-10-30
18
257 Views
Last Modified: 2010-03-20
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
Comment
Question by:jedwards_2
  • 9
  • 8
18 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22846210
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
 

Author Comment

by:jedwards_2
ID: 22846295
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22846306
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22846335
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
 

Author Comment

by:jedwards_2
ID: 22846378
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22846444
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
 

Author Comment

by:jedwards_2
ID: 22850241
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
 

Author Comment

by:jedwards_2
ID: 22850298
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22850446
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:jedwards_2
ID: 22851008
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
 

Author Comment

by:jedwards_2
ID: 22851072
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
 

Author Comment

by:jedwards_2
ID: 22851172
Just did some research and the OVER function will not work.  They are using SQL Server 2000
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22853213
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22854389
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
 

Author Comment

by:jedwards_2
ID: 22855897
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22856985
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
 

Author Comment

by:jedwards_2
ID: 22859516
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22859666
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now