question

amitsrivastava-0957 avatar image
0 Votes"
amitsrivastava-0957 asked amitsrivastava-0957 edited

How to group data and provide index

Hi Folks!!

I have data in a table as below


Id. Startdate. ColA. ColB.
1. 1 Jan 2020. A. B.
2. 2 Jan 2020. A. B.
3. 3 Jan 2020. A. C.
4. 4 Jan 202. A. C.
5. 5 Jan 2020. A. B.
6. 6 Jan 2020. A. B.
7. 7 Jan 2020. A. C.

Now I want to introduce one more column such as Index and that will provide same index to all the grouped record until date is in sequence and data will be like as below

Id. Startdate. ColA. ColB. index
1. 1 Jan 2020. A. B. 1
2. 2 Jan 2020. A. B. 1
3. 3 Jan 2020. A. C. 2
4. 4 Jan 202. A. C. 2
5. 5 Jan 2020. A. B. 3
6. 6 Jan 2020. A. B. 3
7. 7 Jan 2020. A. C. 4

Please suggest.

Thanks
Amit Srivastava

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 commented
 ;With cteRn As
 (Select Id, Startdate, ColA, ColB,
   Row_Number() Over(Order By Startdate, Id) As rn
 From @Sample),
 cteIndex As
 (Select r.Id, r.Startdate, r.ColA, r.ColB, r.rn, 1 As [Index]
 From cteRn r
 Where r.rn = 1
 Union All
 Select r.Id, r.Startdate, r.ColA, r.ColB, r.rn, 
   Case When i.ColA = r.ColA And i.ColB = r.ColB Then i.[Index] Else i.[Index] + 1 End As [Index] 
 From cteIndex i
 Inner Join cteRn r On i.rn+1 = r.rn)
 Select Id, Startdate, ColA, ColB, [Index] 
 From cteIndex;

Tom

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TomCooper-6989 ,

Apologies for not mentioning it, i am working on AZURE SQL DATABSE and it is above query is not supported on AZURE DB , it states below error


DATABASE VERSION - Microsoft Azure SQL Data Warehouse - 10.0.13670.0

ERROR - Recursive common table expressions are not supported in this version.

Please provide the same that will support on Microsoft Azure SQL Data Warehouse


Thanks
Amit

0 Votes 0 ·
TomCooper-6989 avatar image TomCooper-6989 amitsrivastava-0957 ·

@amitsrivastava-0957 I'm sorry, I don't use Azure SQL so I can't help you. Hopefully someone else will.
Tom

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered amitsrivastava-0957 edited

Check a suggestion:

 ;
 with Q as
 (
     select *,
         iif(lag(ColA) over (order by Startdate) <> ColA or lag(ColB) over (order by Startdate) <> ColB, 9, NULL) f
     from ATable
 )
 select Id, Startdate, ColA, ColB, count(f) over (order by Startdate) + 1 as [index]
 from Q
 order by Startdate

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Viorel-1 Thanks for the reply.

The above code is not working for data mentioned below i.e. where two id's having same date

id startdate cola colb
1 2022-02-04 a c
1 2022-01-29 a b
1 2022-01-30 b b
1 2022-01-31 b b
2 2022-02-01 a c
1 2022-02-01 b b

1 2022-02-03 a b
2 2022-02-04 a c
1 2022-02-05 b b

While executing query provided by you, it will give me below output

Id Startdate ColA ColB index
1 2022-01-29 a b 1
1 2022-01-30 b b 2
1 2022-01-31 b b 2
1 2022-02-01 b b 4

1 2022-02-03 a b 5
1 2022-02-04 a c 6
1 2022-02-05 b b 7
2 2022-02-01 a c 4
2 2022-02-04 a c 6

Ideally, for id=1 and date 2022-01-31 and id=1 and date 2022-02-01, index must be same as dates for id are in sequence and ColA and ColB values are same.

Most Important - ABOVE QUERY IS NOT WORKING in AZURE SQL DATABASE, PLEASE PROVIDE QUERY I.E. SUPPORTED IN AZURE SQL DATABASE


0 Votes 0 ·
Viorel-1 avatar image Viorel-1 amitsrivastava-0957 ·

If there are repeated dates, then it is not clear how to order the rows. In addition, in your new vision, the Id column does not look like a key which can be used for additional ordering.

I have no idea why the query is not supported in your database and what errors are displayed.


0 Votes 0 ·
amitsrivastava-0957 avatar image
0 Votes"
amitsrivastava-0957 answered

Hi @Viorel-1

I have done the above with some work around, as detailed below

Table : dbo.distinct_id_number: Will have only distinct Employee Employee Number with Row ID against every Employee NUmber.

Table : ATable : contain complete set of data.

Table : dbo.TablePostIndexInclusion : Will hold the new data with Proper Index.


**DECLARE @Counter INT
DECLARE @Max INT
SET @Max = (select MAX(Row_Num) c from dbo.distinct_id_number)
--select @Max
SET @Counter=(select MIN(Row_Num) c from dbo.distinct_id_number)
WHILE ( @Counter <= @Max)
BEGIN
; with Q as
(
select ,
iif(lag(ColA) over (order by Startdate) <> ColA or lag(ColB) over (order by Startdate) <> ColB, 9, NULL) f
from ATable b (nolock) JOIN dbo.distinct_id_number a (nolock) on b.employee_number=a.employee_number
Where a.Row_num=@Counter
)
insert into dbo.TablePostIndexInclusion
select Id, Startdate, ColA, ColB, count(f) over (order by Startdate) + 1 as [index]
from Q
SET @Counter = @Counter + 1
END
*


Thanks
Amit

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.