Sql-server – List ranges and the total count based on condition

sql server 2014sql-server-2008-r2

Disclaimer: I have already posted this question yesterday here. As there were no response there re-posting it in dba forum now.

Table Schema:

CREATE TABLE [dbo].[TblMaster](
    [SID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [VID] [int] NOT NULL,
    [CreatedDate] [datetime] default (getdate()) NOT NULL,
    [CharToAdd] [varchar](10) NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[TblDetails](
    [DetailsID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [SID] [int] NOT NULL,
    [Sno] [int] NOT NULL,
    [ConcatenatedText] [varchar](20) NOT NULL,
    [isIssued] [bit] default (0) NOT NULL,
    [isUsed] [bit] default (0) NOT NULL
)
GO

Sample data:

Insert into dbo.TblMaster Values (1,default, 'CA', 1, 5)
Insert into dbo.TblMaster Values (1,default, 'PA', 1, 5)
GO
Insert into dbo.TblDetails values(1, 1, 'CA1', 0,0)
Insert into dbo.TblDetails values(1, 2, 'CA2', 0,0)
Insert into dbo.TblDetails values(1, 3, 'CA3', 0,0)
Insert into dbo.TblDetails values(1, 4, 'CA4', 1,0)
Insert into dbo.TblDetails values(1, 5, 'CA5', 0,0)
Insert into dbo.TblDetails values(2, 1, 'PA1', 0,0)
Insert into dbo.TblDetails values(2, 2, 'PA2', 0,0)
Insert into dbo.TblDetails values(2, 3, 'PA3', 1,0)
Insert into dbo.TblDetails values(2, 4, 'PA4', 0,0)
Insert into dbo.TblDetails values(2, 5, 'PA5', 0,0)
Insert into dbo.TblDetails values(3, 1, '1', 0,0)
Insert into dbo.TblDetails values(3, 2, '2', 1,0)
Insert into dbo.TblDetails values(3, 3, '3', 1,0)
Insert into dbo.TblDetails values(3, 4, '4', 0,0)
Insert into dbo.TblDetails values(3, 5, '5', 0,0)
GO

Expected Output:

enter image description here

Query I have build till now (result doesn't match the expected output)

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT 
            TM.CharToAdd as Prefix, 
            sno,
            sno - ROW_NUMBER() OVER(ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]             
      WHERE isIssued = 0 and isUsed = 0 
            AND  TM.VID = @VID 
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

In the TblDetails table want to find the range of available values and its total counts from all records whose bit columns are 0. If bit column is 1 then it means it is already used so I am trying to skip it and list rest as available records. Doubtful whether am I explaining the problem statement well so have provided the sample data and expected output for better understanding. I did try doing some recursive function but the result isn't matching the expected output. So looking for help to resolve this.

Best Answer

You were close with what you had. Just needed a couple of changes.

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT 
            TM.CharToAdd as Prefix, 
            sno,
            sno - ROW_NUMBER() OVER(PARTITION BY TM.CharToAdd ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]             
      WHERE isIssued = 0 and isUsed = 0 
            AND  (TM.VID = @VID OR TM.VID IS NULL)
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

You need to partition on TM.CharToAdd to make the counter restart for each prefix value and you need to change AND TM.VID = @VID to AND (TM.VID = @VID OR TM.VID IS NULL) if you want the rows from TblDetails that does not have a match on SID in TblMaster.

Related Question