SQLTeam.com | Weblogs | Forums

SQL select statement for generating counter


#1

Dears

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.

Invoice S_ORD_ITEM SCHED_LINE
A893131032017 1 1
A893131032017 1 2
A893131032017 1 3
A893131032017 1 4
A893131032017 1 5
A893131032017 1 6
A893131032017 1 7
A893131032017 1 8
A893131032017 1 9
A893131032017 1 10
A895762032017 1 1
A895762032017 1 2
A895762032017 1 3
A895762032017 1 4
A895762032017 1 5
A895762032017 1 6
A895762032017 1 7
A895762032017 1 8
A895762032017 1 9
A895762032017 1 10


#2
;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

#3

its not executing


#4

Hi Salahuddinit,

Please check if the code below solves your problem.

Select invoice , S_ORD_Item , Row_number ()over(Partition by Invoice , S_ORD_Item order by Invoice desc ) from #t

Regards
Anna


#5

Thanks its working


#6

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.


#7

Hi all,

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.


#8

Hi all,

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.


#9

Apologies. I wasn't following this thread because someone already provided a good answer to the original question.

Do you still need help here?