1

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.

4

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.

  • Thanks a lot Mikael. Does having the CharToAdd column in TblDetails also will help in query processing time? Reason being this details table will have easily upwards of 1 million records at any given point in time. – prasanth Aug 1 '16 at 7:55
  • 1
    @prasanth You will have to try that on your data. It all depends on what indexes you have on the tables and perhaps you can build a better supporting index on TblDetails if you have the column there? Word of advice is to not move the column just because it probably will make this query go faster. Leave the column where it belongs in your data model and try to make the best with that. As a last resort you could think about storing redundant data. – Mikael Eriksson Aug 1 '16 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.