I want to get the output in the below format
whenever invoice and S_ORD_Item is same i need to generate a counter in (SCHED_Line).
and this counter will change whenever the combination (Invoice and S_ORD_Item) is changing.
;WITH yourSource
AS ( SELECT 'A893' AS Invoice UNION ALL
SELECT 'A895'
)
,myNumbers
AS(
SELECT 1 AS i
UNION ALL
SELECT i+1
FROM myNumbers
WHERE i<10
)
SELECT Invoice, 1 as S_Ord_Item, i AS Sched_Line
FROM
yourSource AS S
CROSS JOIN myNumbers AS N
ORDER BY
S.Invoice
,N.i
MUCH better, faster, and less resource intensive than recursive CTEs, which are actually worse than a WHILE loop when doing single row operations like this.
By using the above logic i am able to avoid duplicates if i am getting a set of data only once.
i am loading the data initially with this logic, then daily there is a delta load of data in the same table after this logic, i need to take in consideration this above logic during insert of a record, i have to check this combination in the table where already there is a initial data, how can i increase the (SCHED_LiNE) counter before inserting the record.
By using the above logic i am able to avoid duplicates if i am getting a set of data only once..
i am loading the data initially with this logic, then daily there is a delta load of data in the same table after this logic, i need to take in consideration this above logic during insert of a record, i have to check this combination in the table where already there is a initial data, how can i increase the (SCHED_LiNE) counter before inserting the record.
