Need Help with SQL Query

Question

3.00/5 (2 votes)

See more:

Thank you all for your comments, help and patience... trying again...

I need to get the last lineitem for each job, which should be either 1 less than the next higher line item, or the same value as the lineitem (which would indicate that the job consists of just the 1 line item), or NULL (which would indicate that the job consists of all line items remaining).

TableA
OrderNo| JobNo | LineItem
11111 | 11111 | 1
11111 | 11111-1 | 3
11111 | 11111-2 | 4
11112 | 11112 | 1
11113 | 11113 | 1
11113 | 11113-1 | 6
11113 | 11113-2 | 8

Output:
JobNo | LineItem | LastLineItem
11111 | 1 | 2
11111-1 | 3 | 3
11111-2 | 4 | NULL
11112 | 1 | NULL
11113 | 1 | 5
11113-1 | 6 | 7
11113-2 | 8 | NULL

Posted 5-Sep-14 10:56am

Mike Twohig

Updated 8-Sep-14 4:32am

v3

Add a Solution

2 solutions

Solution 1

I haven't got the tables, so you might have to tweak this a little:

SQL

SELECT 
  a1.JobNo, 
  a1.LineItem, 
  CASE WHEN a2.LineItem IS NULL THEN NULL ELSE a2.LineItem-1 AS LastLineItem
FROM 
  TableA a1
LEFT JOIN 
  TableA a2 on a2.JobNo = PARSENAME(REPLACE(a1.JobNo,'-','.'),2) + '-' + CAST(PARSENAME(REPLACE(a1.JobNo,'-','.'),1) AS INT) + 1

The join works like this: it joins every record of the table with the record with the next jobNo ending, so it joins 11111-0 to 11111-1, 11113-2 to 11113-3 etc.
If the join has a result, then the last item of the previous job is the start of the next job-1, if there is no result, then it is the last job, so by your definition LastLineItem should return NULL.

Again, you might run into a type mismatch or syntax error in my query, because I did not have the change to test it, but it should demonstrate the idea I'm trying here.

Posted 5-Sep-14 11:27am

kbrandwijk

Comments

Mike Twohig 8-Sep-14 10:33am

Thank you so much for your time and effort, kbrandwijk.
Unfortunately, in my effort to try to simplify the scenario I unknowingly changed too much for that query to work for me. I apologize.
I have updated the original post with better data.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kbrandwijk · Accepted Answer · 2014-09-08T04:58:00

Given the updated table structure, we need to look at the next row by creating a rownumber:

SQL

;
WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY t.OrderNo, t. JobNo),
    t.OrderNo,
    t.JobNo,
    t.LineItem
  FROM TableA t
)
SELECT
  CTE.JobNo,
  CTE.LineItem,
  CASE WHEN nex.OrderNo = CTE.OrderNo THEN nex.LineItem - 1 ELSE NULL END AS LastLineItem,
FROM CTE
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

GO

In SQL 2012, you can do:

SQL

SELECT
  t.JobNo,
  t.LineItem,
  CASE WHEN LEAD(t.OrderNo) OVER (ORDER BY t.OrderNo, t.JobNo) = t.OrderNo THEN ((LEAD(t.LineItem) OVER (ORDER BY t.OrderNo, t.JobNo) - 1)) ELSE NULL END AS LastLineItem
FROM TableA t

Need Help with SQL Query

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0

Need Help with SQL Query

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us