CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Alexander
Alexander

Reputation: 225

Retrieve data from changed rows records

I'm working in Microsoft SQL Server 2005 Management Studio. I'm new to T-SQL and i have to make the following report:

I have a table (H2O) that contains a records of timestamp, pumpstatus, tankVolume.

The data is inserted into H2O every 1 minute.

I have to make a report (based on timestamp between date1 and date2) on the emptying tank.

The indicator when the emptying process is started is the pumpstatus chages from 2 to 3

The indicator when the emptying process is finished is the pumpstatus chages from 3 to 2

Thanks for any help!

Upvotes: 0

Views: 49

Answers (2)

Alexander
Alexander

Reputation: 225

   ;WITH cte ([emptying time], [Initial Volume], Volume) AS 
        (SELECT      (SELECT TOP 1 H2O_1.[timestamp]
         FROM        [H2O] H2O_1
         WHERE       H2O_1.[H2O_TOOLS_P2_JY_TREND_VAL0] = 2 
         AND         H2O_1.[timestamp] < H2O_2.[timestamp]) AS [emptying time],
        (SELECT TOP 1 H2O_1.H2O_LT301_VOL_VAL0
         FROM       [H2O] H2O_1
         WHERE      H2O_1.[H2O_TOOLS_P2_JY_TREND_VAL0] = 2 
         AND        H2O_1.[timestamp] < H2O_2.[timestamp]) AS [Initial Volume],
         H2O_2.H2O_LT301_VOL_VAL0 As Volume         

         FROM        [H2O] H2O_2
         WHERE       H2O_2.H2O_TOOLS_P2_JY_TREND_VAL0 = 3
        )
   Select  Distinct c.[emptying time],
             max(c.[Initial Volume]) as [Initial Volume],                
             min(c.Volume) as [Final Volume],
             (max(c.[Initial Volume]) - min(c.Volume)) as [Emptying volume]
   from cte c
   group by  c.[emptying time]

Upvotes: 0

artm
artm

Reputation: 8584

Try this:

;WITH cte (dt, st, v) AS 
(SELECT timestamp AS dt, status AS st, volume AS v
FROM h2o)
SELECT DISTINCT h.timestamp
, h.status
, h.volume
FROM cte c
INNER JOIN h20 h ON h.dt > c.dt AND ((c.st = 2 AND h.status = 3) OR (c.st = 3 AND h.status = 2))

You can join on itself using the timestamp and the status columns and date higher than the previous record and when the status has changed from 2 to 3 or from 3 to 2.

Upvotes: 1

Related Questions