CODEWITHSUNDEEP
asp.netsql
Ketan mevada
Ketan mevada

Reputation: 152

Select Record with Repeated Status using Sql Query

i have one Schedule Table like enter image description here

i want to look like select query data is:

1 and 11 is Duplicate trainer and Duplicate day,time

10 and 12 is Duplicate trainer, Duplicate vanueid and Duplicate date,time

so last two column are display is not Duplicate than available and is Duplicate than display notavailable enter image description here

Upvotes: 1

Views: 56

Answers (1)

Devesh
Devesh

Reputation: 4550

enter image description hereHere is the solution that is coming to my mind.There may some syntax issue but i given the logic which may help you. 

        DECLARE @duplicate TABLE ( 
        trainerId INT,
        dt varchar(50)
        )

          INSERT INTO @duplicate SELECT TrainerId , [Date] from tbl GROUP BY TrainerId , Date HAVING (COUNT(*) > 1)
         SELECT * FROM @duplicate
         DECLARE @tempTable TABLE (
             trainerId INT,
             dt varchar(50),
             status int
          )
          INSERT INTO @tempTable   
          SELECT trainerId , [Date]  , STATUS = (SELECT COUNT(*) FROM @duplicate  where           trainerId = tbl.TrainerId and dt = tbl.Date) FROM tbl
          ![enter image description here][2]SELECT * ,  CASE [status] WHEN 0 THEN 'Available' ELSE 'Not Available' END FROM @tempTable

Upvotes: 1

Related Questions