Solved

SQL Query  to retrieve Records

Posted on 2011-02-10
6
296 Views
Last Modified: 2012-08-13
Hello Experts,

I need to run a report based on the Datetime range in the tables.

Two tables TXTN and LEVELS.

TxTN
ID         DT                                   Tank                                   Qty
001        01/01/2011 15:00             001                                     20.1
001        01/01/2011 16:00             001                                     25.2
002       01/01/2010  12:00             001                                     20.5
001       01/02/2011  10:00             001                                     20.1
001       01/02/2011  19:00             001                                     25.2
002       01/02/2010   11:00            001                                     20.5


LEVEL

TANK    DT                              LEVEL
001       01/01/2011 12:00        1000
001       01/02/2011  15:00         750


I need to get records from TXTN table on the Last two datetimes it got levels. For instance if I got the LEVEL for Tank#001 as 1000 on 01/01/2011 at 12:00 PM, then I need to take that as start datetime and till the next level I received for the same tank on 01/02/2011 15:00. So the total quantity for tank#001 is 65.4 gallons.And Insert into another table Totalizer as Qty = 65.4 and datetime as 01/02/2011 since the end datetime is 01/02/2011. It means the TXTN's happen for the whole day(01/02/2011).


Totalizer

Tank       Date                    CNT
001       01/02/2011             65.4    

Here the Date  field is End Date and CNT is Sum of Quantity we got.

Business Logic :
I need to get records in the TXTN table from the last time it got record entry and till the next record. And also I need to make sure If I have two levels for the same tank .
For Instance:
LEVEL
TANK    DT                                        LEVEL
001       01/01/2011 12:00                1000
001       01/02/2011  15:00                 750
001       01/02/2011  19:00                 550

Then I need to get the last record datetime as the End Datetime here it is 01/02/2011 19:00. and the Quantity will be 90.6. And Insert into  Totalizer table.
Totalizer:

Tank       Date                    CNT
001       01/02/2011           90.6.  

It's a  reconciliation report, so I need to adjust all the totalizers.

Thanks.
0
Comment
Question by:ASPDEV
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Expert Comment

by:jasonduan
ID: 34864828
try this:

;WITH LEVELS_CTE(Tank, DTMin, DTMax)
AS
(
    SELECT Tank, MIN(DT) AS DTMin, MAX(DT) AS DTMax
    FROM [LEVELS]
    GROUP BY Tank
)

INSERT INTO [Totalizer](Tank,  [Date], CNT)
SELECT l.Tank, l.DTMax AS DT, SUM(t.Qty) AS CNT
FROM [LEVELS_CTE] l
INNER JOIN [TXTN] t ON t.Tank = l.Tank AND t.DT BETWEEN l.DTMin AND l.DTMax
GROUP BY l.Tank, l.DTMax
0
 

Author Comment

by:ASPDEV
ID: 34865102
Jasonduan,

It does not work on the Min date, and also it find all the SUM of all Quantity. I need to know only last two datetime in the Levels table for that Tank.
0
 
LVL 11

Accepted Solution

by:
jasonduan earned 400 total points
ID: 34865243
revised (the CTE portion is changed):

;WITH LEVELS_CTE0(Tank, DT, N) AS
(
  SELECT Tank, DT, ROW_NUMBER() OVER(PARTITION BY Tank ORDER BY DT DESC) AS N
  FROM [LEVELS]
),
LEVELS_CTE(Tank, DTMin, DTMax) AS
(
      SELECT t.Tank,
               (SELECT DT FROM [LEVELS_CTE0] WHERE Tank=t.Tank AND N = 2) AS DTMin
               (SELECT DT FROM [LEVELS_CTE0] WHERE Tank=t.Tank AND N = 1) AS DTMax
    FROM LEVELS_CTE0 t
)

INSERT INTO [Totalizer](Tank,  [Date], CNT)
SELECT l.Tank, l.DTMax AS DT, SUM(t.Qty) AS CNT
FROM [LEVELS_CTE] l
INNER JOIN [TXTN] t ON t.Tank = l.Tank AND t.DT BETWEEN l.DTMin AND l.DTMax
GROUP BY l.Tank, l.DTMax
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
ID: 34865417
Use ranking, it should help (reference: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html)

What you would do is rank your levels by occurence date in descending order and then check difference between rows ranked 1 and 2.

See if this gives you close to what you are looking for:
;WITH levels_ranked AS (
   SELECT [TANK], [DT], [LEVEL]
        , ROW_NUMBER() 
		     OVER(PARTITION BY TANK ORDER BY DT DESC) RN
   FROM [LEVELS]
)
SELECT t.[TANK], SUM(t.QTY) AS QTY
FROM [TxTN] t
INNER JOIN (
   SELECT [TANK]
        , MIN(CASE RN WHEN 2 THEN [DT] END) AS StartDT
        , MAX(CASE RN WHEN 1 THEN [DT] END) AS EndDT
		, MAX(CASE RN WHEN 2 THEN [DT] END) AS StartLevel
        , MAX(CASE RN WHEN 1 THEN [DT] END) AS EndLevel
   FROM levels_ranked
   GROUP BY [TANK]
) l ON t.[TANK] = l.[TANK] 
   AND t.[DT] BETWEEN l.[StartDT] AND l.[EndDT]
;

Open in new window

(1) Rank rows in LEVELS table.
(2) Use conditional aggregates, pivot or other method to get MIN/MAX dates for LEVELS (showed with actual LEVEL amount at start and end here in case that is needed).
(3) Filter TxTN table rows based on fitting within window of Start and End DT.

Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 34865442
Arggh! @jasonduan: my apologies, I was typing away and didn't see you already posted.  You remembered about the EndDT being in the final select too, so mine is less correct without having l.[EndDT] in the SELECT list.  Anyway, sorry for duplicative post.
0
 

Author Closing Comment

by:ASPDEV
ID: 34946379
Thanks.
0

Featured Post

Why spend so long doing email signature updates?

Do you spend loads of your time carrying out email signature updates? Not very interesting are they? Don’t let signature updates get you down. Let Exclaimer Cloud - Signatures for Office 365 make managing email signatures a breeze.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

760 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

22 Experts available now in Live!

Get 1:1 Help Now