How to generate serial no in sql

Question

hi.... how to generate serial no starting from 1 for each month chnage or each finacial year change means year no change when yr chnage as (1-4-17 to 31-3-18) in sql or c# plz ans....

Tapan Patel · Answer

CREATE TABLE #test(Dt DATE ) INSERT INTO #test SELECT '1 - 1 -2018' UNION SELECT '2 - 1 -2018' UNION SELECT '3 - 1 -2018' UNION SELECT '4 - 1 -2017' UNION SELECT '5 - 1 -2017' UNION SELECT '6 - 1 -2017' UNION SELECT '7 - 1 -2017' UNION SELECT '8 - 1 -2017' UNION SELECT '9 - 1 -2017' UNION SELECT '10 - 1 -2017' UNION SELECT '11 - 1 -2017' UNION SELECT '12 - 1 -2017' SELECT Dt CASE WHEN MONTH (Dt)>3 THEN MONTH (Dt)-3 ELSE MONTH (Dt)+(3*3) END [Series] FROM #test ORDER BY Dt Desc Please mark it as answer if addresses the issue.