T-SQL Query | [ The GAP Puzzle ]

Puzzle Statement

1. I got this puzzle on the web. The Puzzle posted by Jim Geissman.
2. Here we have to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings per location, but only one largest gap. (If there are two largest
gaps, it does’t matter which one is used for the sums.
3. In case of CA the largest gap is 6 [ Difference between 103 and 109 ], and the sum of N for the 2 rows below the gap is 8, and for the 2 above the gap it’s 5.
4. Please check out the sample input and expected output for details.

Sample Input

 Loc East N CA 100 3 CA 103 5 CA 109 2 CA 110 3 OR 100 3 OR 108 5 OR 109 2 OR 110 3 WA 108 5 WA 109 2 WA 110 3 WA 115 3

Expected Output

 Loc GapStart AfterGap MaxGapSize CA 8 5 6 OR 3 10 8 WA 10 3 5

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 — ``` CREATE TABLE a11(Loc CHAR(2), East INT, N INT) go INSERT a11 VALUES('CA', 100, 3) INSERT a11 VALUES('CA', 103, 5) INSERT a11 VALUES('CA', 109, 2) INSERT a11 VALUES('CA', 110, 3) INSERT a11 VALUES('OR', 100, 3) INSERT a11 VALUES('OR', 108, 5) INSERT a11 VALUES('OR', 109, 2) INSERT a11 VALUES('OR', 110, 3) INSERT a11 VALUES('WA', 108, 5) INSERT a11 VALUES('WA', 109, 2) INSERT a11 VALUES('WA', 110, 3) INSERT a11 VALUES('WA', 115, 3) ``` —

Update May 8 | Solution 1 – Pawan Kumar Khowal

 ``` -- ;WITH CTE1 AS ( SELECT * , EAST - LAG(EAST) OVER (PARTITION BY loc ORDER BY EAST) MaxGapSize ,SUM(N) OVER (PARTITION BY loc ORDER BY EAST DESC) AfterGap ,( SELECT SUM(N) FROM a11 WHERE a.Loc = a11.Loc AND a.East > a11.East ) GapStart FROM a11 a ) SELECT Loc , GapStart , AfterGap , MaxGapSize FROM CTE1 a WHERE MaxGapSize = ( SELECT MAX(MaxGapSize) FROM CTE1 WHERE a.Loc = CTE1.Loc ) -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal