August 23, 2024 at 5:40 pm
Need to generate a from and to
From to
50 -99
100 -299
300 --
I tried to do the logic ,but its working as
1-50
51-100
101 -
FinalCTE AS ( Select ,FROMQUANTITY AS S_FROMQUANTITY ,ROW_NUMBER() OVER(PARTITION BY C.po_line_id ORDER BY C.FROMQUANTITY ASC) AS A ,CASE WHEN CAST(C.FROMQUANTITY AS NUMERIC(18,7)) >= 1.0 THEN CASE WHEN ROW_NUMBER() OVER(PARTITION BY line_id ORDER BY C.FROMQUANTITY ASC) = 1 THEN 1 ELSE CASE WHEN CHARINDEX('.',FROMQUANTITY) > 0 THEN LAG(CAST(C.FROMQUANTITY AS NUMERIC(18,7)), 1, 1) OVER (PARTITION BY line_id ORDER BY C.FROMQUANTITY) + 1 ELSE LAG(CAST(C.FROMQUANTITY AS NUMERIC(18,7)), 1, 1) OVER (PARTITION BY line_id ORDER BY C.FROMQUANTITY) + 1 END END ELSE C.FROMQUANTITY END AS FromQUANTITY ,CAST(C.FROMQUANTITY AS NUMERIC(18,7)) AS TOQUANTITY FROM CTE AS C )
August 23, 2024 at 7:42 pm
GENERATE_SERIES (Transact-SQL) - SQL Server | Microsoft Learn
Or of course you could search around on here. I know there are folks here that have built TVFs to generate series like that.
August 23, 2024 at 9:42 pm
That is only available in SQL Server 2022 or higher - and the OP is using SQL Server 2016.
The code provided isn't formatted - at all and I don't have the time right now to dig into it. The only thing I can say is that using ROW_NUMBER starts from 1 and not 50. If you want to start at 50 then you need to add 49 to the row number function that is generating the values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply