How to repeat string values

Question

How to repeat string values ​​to the end of a date

Example

Table 1

Date         Name   Dept 
-----------------------
12-02-2009   Raja   IT
13-02-2009   Ravi   CSE
14-02-2009   Ramu   ECE

From the above table value, I want to repeat the value between two dates from 12-02-2009 to 12-03-2009.

Expected Result

Date         Name   Dept 
--------------------------
12-02-2009   Raja   IT
13-02-2009   Ravi   CSE
14-02-2009   Ramu   ECE
15-02-2009   Raja   IT
16-02-2009   Ravi   CSE
17-02-2009   Ramu   ECE
18-02-2009   Raja   IT
19-02-2009   Ravi   CSE
20-02-2009   Ramu   ECE
...
...
12-03-2009

How to make a request to repeat the values ​​of strings to the end date or is possible in vb.net using a while loop, for example

the code

select max(date) from table

while max(date) < End Date

'Repeat the row values

end while

My table structural

Date Name Processing Time

12-02-2009   Raja   IT  09:00 
13-02-2009   Ravi   CSE 18:00 
14-02-2009   Ramu   ECE 10:00

Expected Result

Date Name Dept Time StartDate EndDate

12-02-2009   Raja   IT  09:00 12-02-2009 12-02-2009
13-02-2009   Ravi   CSE 18:00 13-02-2009 14-02-2009
14-02-2009   Ramu   ECE 10:00 14-02-2009 14-02-2009

...

If the StartDate value matches the Date column For EndDate, I have to check the time column, if the time is more than 17:00, the next date should be displayed in the EndDate column.

Requires SQL query or VB.Net code help

Answer 1

You can try something like

DECLARE @Table TABLE(
        Date DATETIME,
        Name VARCHAR(50),
        Dept VARCHAR(50)
)

INSERT INTO @Table SELECT '12 Feb 2009', 'Raja', 'IT'
INSERT INTO @Table SELECT '13 Feb 2009', 'Ravi', 'CSE'
INSERT INTO @Table SELECT '14 Feb 2009', 'Ramu', 'ECE'



DECLARE @StartDate DATETIME,
        @EndDate DATETIME,
        @Count INT

SELECT @Count = COUNT(1) FROM @Table

SELECT @StartDate = '12 Feb 2009',
        @EndDate = '12 Mar 2009'

--using CTE create a date range, and associated id per date
;WITH Dates AS (
        SELECT  @StartDate DateVal,
                0 ID
        UNION ALL
        SELECT  DateVal + 1,
                (ID + 1) % @Count
        FROM Dates
        WHERE DateVal + 1 <= @EndDate
),
--using cte, create id per entry in the table you wish to repeat
RowNumbers AS(
        SELECT  *,
                ROW_NUMBER() OVER(ORDER BY Date) ID
        FROM    @Table
)
--join dates to table to repeat id generated ids.
SELECT  *
FROM    Dates d LEFT JOIN
        RowNumbers r on d.ID + 1 = r.ID

Take a look

• SQL #

• ADO.NET SQL Server

vb.net/#

,

DECLARE @Table TABLE(
        Date DATETIME,
        Name VARCHAR(50),
        Dept VARCHAR(50),
        Time VARCHAR(5)
)

INSERT INTO @Table SELECT  '12 Feb 2009','Raja','IT','09:00'
INSERT INTO @Table SELECT  '13 Feb 2009','Ravi','CSE','18:00'
INSERT INTO @Table SELECT  '14 Feb 2009','Ramu','ECE','10:00'

SELECT  *,
        Date AS StartDate,
        CASE 
            WHEN CAST(LEFT(Time,2) AS INT) > 17 THEN Date + 1 
            ELSE DATE 
        END EndDate
FROM    @Table

, ROW_NUMBER(), .

This is how you would insert values

DECLARE @InsertTable TABLE(
        DateVal DATETIME,
        ID INT,
        Date DATETIME,
        Name VARCHAR(50),
        Dept VARCHAR(50)
)

DECLARE @Table TABLE( 
        Date DATETIME, 
        Name VARCHAR(50), 
        Dept VARCHAR(50) 
) 

INSERT INTO @Table SELECT '12 Feb 2009', 'Raja', 'IT' 
INSERT INTO @Table SELECT '13 Feb 2009', 'Ravi', 'CSE' 
INSERT INTO @Table SELECT '14 Feb 2009', 'Ramu', 'ECE' 



DECLARE @StartDate DATETIME, 
        @EndDate DATETIME, 
        @Count INT 

SELECT @Count = COUNT(1) FROM @Table 

SELECT @StartDate = '12 Feb 2009', 
        @EndDate = '12 Mar 2009' 

--using CTE create a date range, and associated id per date 
;WITH Dates AS ( 
        SELECT  @StartDate DateVal, 
                0 ID 
        UNION ALL 
        SELECT  DateVal + 1, 
                (ID + 1) % @Count 
        FROM Dates 
        WHERE DateVal + 1 <= @EndDate 
), 
--using cte, create id per entry in the table you wish to repeat 
RowNumbers AS( 
        SELECT  *, 
                ROW_NUMBER() OVER(ORDER BY Date) ID 
        FROM    @Table 
) 
--join dates to table to repeat id generated ids. 
INSERT INTO @InsertTable --INSERT VALUES HERE
SELECT  d.DateVal,
        d.ID,
        r.Date,
        r.Name,
        r.Dept 
FROM    Dates d LEFT JOIN 
        RowNumbers r on d.ID + 1 = r.ID
OPTION (MAXRECURSION 0) --this is required.           

SELECT *
FROM @InsertTable