CODEWITHSUNDEEP
sqlsql-server-2008
Raymondo
Raymondo

Reputation: 587

SQL Server 2008 - calculations on last two max ID's

I have a table as follows:

ImportFileID    ImportID    FileID  DateLoaded  FileRowCount
1   1   1   2013-10-11  488527
2   1   2   2013-10-11  531175
3   1   3   2013-10-11  488255
6   1   5   2013-10-11  488527
7   1   6   2013-10-11  531175
8   2   1   2013-10-12  750000
9   3   1   2013-10-13  250000
10  1   7   2013-10-11  2505053
11  1   8   2013-10-11  61168
12  1   9   2013-10-11  1484677
13  1   10  2013-10-11  3153568
14  1   11  2013-10-11  162461
15  1   12  2013-10-11  26002
16  1   13  2013-10-11  1131180
17  1   14  2013-10-11  1442160
18  1   4   2013-10-11  2505053
19  4   1   2013-10-14  564881

I want to grab the last two file loads via the ImportID (for all files) and work out the percantage difference on the FileRowCount.

For instance, rows 9 and 19 are the most recent loads for FileID 1.

I'd like to see something like:

FileID  CountDiff PercDiff
1       314881    44

Any suggestions?

Thanks

Upvotes: 0

Views: 64

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use this CTE with ROW_NUMBER:

WITH CTE AS
(
    SELECT [ImportFileID], [ImportID], [FileID], [DateLoaded], [FileRowCount],
      RN = ROW_NUMBER() OVER (PARTITION BY FileID
                              ORDER BY DateLoaded DESC)
    FROM dbo.Import
)
SELECT c1.FileID,  
       CountDiff=c1.FileRowCount-c2.FileRowCount, 
       PercDiff=ROUND(100.0 * c2.FileRowCount / c1.FileRowCount, 0)
FROM CTE c1 INNER JOIN CTE c2
  ON c1.RN=1 AND c2.RN=2 AND c1.FileID = c2.FileID

Demonstration

If you just want to know it for a given FileID use Where on the final Select. Since FileID=1 is the only one which has two rows, it's the only one that is shown yet.

Upvotes: 4

Related Questions