Geek Answers Handbook

Filling number ranges and concatenating text in a child table

I wonder how to build the logic for the scenario below (I am using SQL Server 2008 and above).

In TblMaster, each entry will have a Start and End value. Along with this is the CharToAdd varchar column. If the range says 1 to 3, then TblDetails should have an entry for 1,2,3. Not sure if I am explaining this effectively, so I provided a screenshot with an example of I / O.

Ranges will range from 5,000 to 100,000 (max), so performance is also a concern.

Table:

CREATE TABLE [dbo].[VMaster](
    [VID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [VName] [varchar](30) NOT NULL
)
GO

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) NOT NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
) ON [PRIMARY]
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

ALTER TABLE [dbo].[TblMaster]  WITH CHECK ADD CONSTRAINT [fk_SI_id] FOREIGN KEY([VID])
REFERENCES [dbo].[VMaster] ([VID])
GO

ALTER TABLE [dbo].[TblMaster] CHECK CONSTRAINT [fk_SI_id]
GO

Example data for tables:

Insert into dbo.VMaster Values ('A1')
GO
Insert into dbo.TblMaster Values (1,default, 'ABC', 100, 105)
GO

Examples of records inserted into tables and the result expected in TblDetails: enter image description here

EDIT: , TblMaster ? TblDetails 0 , " ".

--Modifying the original range. So the records 100, 104, 105 has to be removed 
-- from tblDetails if its isIssued, isUsed are both 0. 
-- If not will have to reject this update 
Update dbo.TblMaster 
Set Start = 101, [End] = 103
Where SID = 1

--Another scenario. We need to remove 100,101,102,103,104,105 from tblDetails if
-- its isIssued, isUsed are both 0. Then create entries from 1000 to 1500 in tblDetails. 
-- If isIssued, isUsed are not 0 then will have to reject this update 
Update dbo.TblMaster 
Set Start = 1000, [End] = 1500
Where SID = 1
+4
4

UDF . Numbers Tally

Declare @Table table (SID int,VID int,CreateDate DateTime,CharToAdd varchar(25),Start int, [End] Int)
Insert Into @Table values
(1,1,'2016-06-30 19:56:14.560','ABC',100,105),
(2,2,'2016-06-30 19:56:14.560','ABC',10,15)

Declare @Min int,@Max int
Select @Min=min(Start),@Max=max([End]) From @Table

Select B.SID
      ,Sno = A.RetVal
      ,ConcetratedText = concat(B.CharToAdd,A.RetVal)
 From (Select RetVal=Cast(RetVal as int) from [dbo].[udf-Create-Range-Number](@Min,@Max,1)) A
 Join @Table B on A.RetVal Between B.Start and B.[End]
 Order By B.Sid,A.RetVal


SID Sno ConcetratedText
1   100 ABC100
1   101 ABC101
1   102 ABC102
1   103 ABC103
1   104 ABC104
1   105 ABC105
2   10  ABC10
2   11  ABC11
2   12  ABC12
2   13  ABC13
2   14  ABC14
2   15  ABC15

UDF, (, maxrecursion 0)

CREATE FUNCTION [dbo].[udf-Create-Range-Number] (@R1 money,@R2 money,@Incr money)

-- Syntax Select * from [dbo].[udf-Create-Range-Number](0,100,2)

Returns 
@ReturnVal Table (RetVal money)

As
Begin
    With NumbTable as (
        Select NumbFrom = @R1
        union all
        Select nf.NumbFrom + @Incr
        From NumbTable nf
        Where nf.NumbFrom < @R2
    )
    Insert into @ReturnVal(RetVal)

    Select NumbFrom from NumbTable Option (maxrecursion 32767)

    Return
End
+2

Go :

    ;with recur as (select vid, chartoadd, [start] as nm, [end], 1 as Detailsid  from tblMaster
                   union all
                   select vid, chartoadd, nm+1, [end], Detailsid+1  from recur where nm<[end])
    select Detailsid, vid, chartoadd+cast(nm as char) as ConcatenatedText
      from recur
OPTION (MAXRECURSION 0)

Ouput

Detailsid   vid ConcatenatedText
1   1   ABC100                           
2   1   ABC101                           
3   1   ABC102                           
4   1   ABC103                           
5   1   ABC104                           
6   1   ABC105
+1

You can use one of many tricks to create a list of numbers. It is probably the easiest thing for you to create it in the database. I will leave this task to you. Therefore, suppose you have a table Numberswith values ​​from 0 to the maximum possible rows in a range.

select ...
from
    Master m
    inner join Numbers n
        on n.Number between 0 and m.End - m.Start
    inner join Details d
        d.Sno = n.Number + m.Start /* and d.SID = m.SID ? */
0
source

The following example illustrates how dynamic it is.

Pay attention to UPDATE

Declare @Table table (SID int,VID int,CreateDate DateTime,CharToAdd varchar(25),Start int, [End] Int)
Insert Into @Table values
(1,1,'2016-06-30 19:56:14.560','ABC',1000,1500),
(2,2,'2016-06-30 19:56:14.560','ABC',10,15)

-----------------------------------------------------------
Update @Table Set Start=101,[End]=103 where SID=1
----------------------------------------------------------

Declare @Min int,@Max int
Select @Min=min(Start),@Max=max([End]) From @Table


Select B.SID
      ,Sno = A.RetVal
      ,ConcetratedText = concat(B.CharToAdd,A.RetVal)
 From (Select RetVal=Cast(RetVal as int) from [dbo].[udf-Create-Range-Number](@Min,@Max,1)) A
 Join @Table B on A.RetVal Between B.Start and B.[End]
 Order By B.Sid,A.RetVal

Returns

SID Sno ConcetratedText
1   101 ABC101            --<< 101 - 103 not 1000 - 1500
1   102 ABC102            --<< 101 - 103 not 1000 - 1500
1   103 ABC103            --<< 101 - 103 not 1000 - 1500
2   10  ABC10
2   11  ABC11
2   12  ABC12
2   13  ABC13
2   14  ABC14
2   15  ABC15
0
source

More articles:


All Articles