Databases
StackOverflow2013
Postsdbo
PONumber of minutes between the two dates depending on the status
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONumber of minutes between the two dates depending on the status
    primarykey
    data
    text
    <p>I have a question related to Sql Server 2005 ( Tsql) </p> <p>I have two columns Date Status</p> <p>And the Data looks.. like </p> <pre><code>Date Status 2012-09-01 00:01:00.000 2 2012-09-01 04:17:00.000 4 2012-09-01 04:34:00.000 4 2012-09-01 04:35:00.000 4 2012-09-01 04:35:48.000 4 2012-09-01 04:35:51.000 1 2012-09-01 17:28:25.000 2 2012-09-01 23:58:00.000 4 2012-09-01 23:59:00.000 1 </code></pre> <p>I need to calculate the time difference between the status ... for example. for min time is when status 2 start and max time when status= 1 (stop) between that date i want the number of minutes. I have done through the cursors checking the status and recording the minumum time and maximum time in the variables </p> <p>Do we have any easy of doing it using CTE.</p> <p>MY QUERY TAKES LONG TIME TO FINISH ... PLEASE HELP.</p> <pre><code> DECLARE @pdunitid INT DECLARE @Date DATETIME DECLARE @pddatetime DATETIME DECLARE @pdstatus INT DECLARE @starttime DATETIME DECLARE @endTime DATETIME DECLARE @calc INT DECLARE @Totaltime INT DECLARE @START INT SET @pdunitid = 33568906 SET @Date = GETDATE() - 102 set @Totaltime = 0 SET @calc = 0 SET @START = 0 DECLARE s CURSOR FAST_FORWARD FOR SELECT pddatetime,pdstatus FROM s1 WITH (NOLOCK) WHERE pdunitid = @pdUnitid AND CONVERT(VARCHAR,pddatetime,112) = CONVERT(VARCHAR,@Date,112) ORDER BY pddatetime,pdstatus OPEN s FETCH NEXT FROM s INTO @pddatetime,@pdstatus WHILE @@FETCH_STATUS = 0 BEGIN -- status 2 is for start sometimes you don't get start so you will have to use the first date with status 4 IF pdstatus IN (2,4) AND @START = 0 BEGIN SET @starttime = @pddatetime SET @START = 1 END -- status 1 is for stop IF (@pdstatus= 1) BEGIN SET @endTime = @pddatetime SET @calc = 1 END -- if you dont get the status 1 by '23:59:00' take the end time IF convert(varchar,@pdgpsdatetime,108) = '23:59:00' BEGIN SET @endTime = @pddatetime SET @calc = 1 END -- Calculate the minutes. IF @calc = 1 BEGIN SET @Totaltime = @Totaltime + DATEDIFF(mi, @starttime,@endTime) SET @calc = 0 SET @START = 0 END FETCH NEXT FROM s INTO @pddatetime,@pdstatus END CLOSE s DEALLOCATE s SELECT @Totaltime </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser978324
    1. USuser978324
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONumber of minutes between the two dates depending on the status
      1. This table or related slice is empty.
      1. VTUpMod
    1. COplease add your query that you have written.
      singulars
      1. PONumber of minutes between the two dates depending on the status
      1. USSachin Shanbhag
    2. COAre you using a Cursor? These are not very efficient. Are the columns indexed? Post the TSQL that you have attempted.
      singulars
      1. PONumber of minutes between the two dates depending on the status
      1. USPhil Murray
    3. COI have posted by query also... and commented where ever it is required..
      singulars
      1. PONumber of minutes between the two dates depending on the status
      1. USuser978324
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload