Repeat Row between From and To Date RangeHow to return only the Date from a SQL Server DateTime datatypeWhat is the difference between varchar and nvarchar?Multiple Indexes vs Multi-Column IndexesHow to concatenate text from multiple rows into a single text string in SQL server?Select columns from result set of stored procedureInserting multiple rows in a single SQL query?How do I generate random number for each row in a TSQL Select?What do Clustered and Non clustered index actually mean?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?
Full backup on database creation
How do I align equations in three columns, justified right, center and left?
Why doesn't the Earth's acceleration towards the Moon accumulate to push the Earth off its orbit?
What is the largest (size) solid object ever dropped from an airplane to impact the ground in freefall?
Where is the encrypted mask value?
Where is the logic in castrating fighters?
Can you heal a summoned creature?
How to capture more stars?
How to convert to standalone document a matrix table
How strong are Wi-Fi signals?
Ticket sales for Queen at the Live Aid
When and what was the first 3D acceleration device ever released?
If a person had control of every single cell of their body, would they be able to transform into another creature?
Infinite Sequence based on Simple Rule
Is there a down side to setting the sampling time of a SAR ADC as long as possible?
What are these arcade games in Ghostbusters 1984?
Is the first derivative operation on a signal a causal system?
LASSO Regression - p-values and coefficients
Different circular sectors as new logo of the International System
What is the most important source of natural gas? coal, oil or other?
I unknowingly submitted plagiarised work
Can a Beholder use rays in melee range?
Why does the 'metric Lagrangian' approach appear to fail in Newtonian mechanics?
Dictionary size reduces upon increasing one element
Repeat Row between From and To Date Range
How to return only the Date from a SQL Server DateTime datatypeWhat is the difference between varchar and nvarchar?Multiple Indexes vs Multi-Column IndexesHow to concatenate text from multiple rows into a single text string in SQL server?Select columns from result set of stored procedureInserting multiple rows in a single SQL query?How do I generate random number for each row in a TSQL Select?What do Clustered and Non clustered index actually mean?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
Hi i am having leave table with From and To date column with no of days leave applied.I want to repeat the row between From and To date.
Actual Result
leaveid fromdate Todate noofdays
1 01/01/2019 02/01/2019 2
Excepted Result
leveid fromdate Todate
1 01/01/2019 01/01/2019
2 02/01/2019 02/01/2019
sql-server asked Mar 24 at 7:21
MithranMithran
1
add a comment |
Hi i am having leave table with From and To date column with no of days leave applied.I want to repeat the row between From and To date.
Actual Result
leaveid fromdate Todate noofdays
1 01/01/2019 02/01/2019 2
Excepted Result
leveid fromdate Todate
1 01/01/2019 01/01/2019
2 02/01/2019 02/01/2019
asked Mar 24 at 7:21
MithranMithran
1
add a comment |
Hi i am having leave table with From and To date column with no of days leave applied.I want to repeat the row between From and To date.
Actual Result
leaveid fromdate Todate noofdays
1 01/01/2019 02/01/2019 2
Excepted Result
leveid fromdate Todate
1 01/01/2019 01/01/2019
2 02/01/2019 02/01/2019
asked Mar 24 at 7:21
MithranMithran
1
Hi i am having leave table with From and To date column with no of days leave applied.I want to repeat the row between From and To date.
Actual Result
leaveid fromdate Todate noofdays
1 01/01/2019 02/01/2019 2
Excepted Result
leveid fromdate Todate
1 01/01/2019 01/01/2019
2 02/01/2019 02/01/2019
asked Mar 24 at 7:21
MithranMithran
1
asked Mar 24 at 7:21
MithranMithran
1
asked Mar 24 at 7:21
MithranMithran
1
asked Mar 24 at 7:21
MithranMithran
1
asked Mar 24 at 7:21
MithranMithran
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can use master..[spt_values] to generate the desired number of rows and use Dateadd to generate the dates like following.
;with cte
as (select number
from master..[spt_values]
where type = 'p')
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join cte c
on c.number < t.noofdays
Online Demo
If you don't want to use master..[spt_values], you can also use recursive CTE like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join seq c
on c.number < t.noofdays
Output
+---------+-------------------------+-------------------------+
| leaveid | fromdate | todays |
+---------+-------------------------+-------------------------+
| 1 | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2 | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+
EDIT:
If you want the dates in specific format, you can use CONVERT like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
convert(varchar, Dateadd(day, number, fromdate), 103) fromdate,
convert(varchar, Dateadd(day, number, fromdate), 103) todays
from @table t
inner join seq c
on c.number < t.noofdays
Online Demo
Output
+---------+------------+------------+
| leaveid | fromdate | todays |
+---------+------------+------------+
| 1 | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2 | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+
answered Mar 24 at 7:40
PSKPSK
13.3k31835
The second rowleaveidshould be 2 instead of 1
– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55321568%2frepeat-row-between-from-and-to-date-range%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use master..[spt_values] to generate the desired number of rows and use Dateadd to generate the dates like following.
;with cte
as (select number
from master..[spt_values]
where type = 'p')
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join cte c
on c.number < t.noofdays
Online Demo
If you don't want to use master..[spt_values], you can also use recursive CTE like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join seq c
on c.number < t.noofdays
Output
+---------+-------------------------+-------------------------+
| leaveid | fromdate | todays |
+---------+-------------------------+-------------------------+
| 1 | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2 | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+
EDIT:
If you want the dates in specific format, you can use CONVERT like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
convert(varchar, Dateadd(day, number, fromdate), 103) fromdate,
convert(varchar, Dateadd(day, number, fromdate), 103) todays
from @table t
inner join seq c
on c.number < t.noofdays
Online Demo
Output
+---------+------------+------------+
| leaveid | fromdate | todays |
+---------+------------+------------+
| 1 | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2 | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+
answered Mar 24 at 7:40
PSKPSK
13.3k31835
The second rowleaveidshould be 2 instead of 1
– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
add a comment |
You can use master..[spt_values] to generate the desired number of rows and use Dateadd to generate the dates like following.
;with cte
as (select number
from master..[spt_values]
where type = 'p')
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join cte c
on c.number < t.noofdays
Online Demo
If you don't want to use master..[spt_values], you can also use recursive CTE like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join seq c
on c.number < t.noofdays
Output
+---------+-------------------------+-------------------------+
| leaveid | fromdate | todays |
+---------+-------------------------+-------------------------+
| 1 | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2 | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+
EDIT:
If you want the dates in specific format, you can use CONVERT like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
convert(varchar, Dateadd(day, number, fromdate), 103) fromdate,
convert(varchar, Dateadd(day, number, fromdate), 103) todays
from @table t
inner join seq c
on c.number < t.noofdays
Online Demo
Output
+---------+------------+------------+
| leaveid | fromdate | todays |
+---------+------------+------------+
| 1 | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2 | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+
answered Mar 24 at 7:40
PSKPSK
13.3k31835
The second rowleaveidshould be 2 instead of 1
– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
add a comment |
You can use master..[spt_values] to generate the desired number of rows and use Dateadd to generate the dates like following.
;with cte
as (select number
from master..[spt_values]
where type = 'p')
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join cte c
on c.number < t.noofdays
Online Demo
If you don't want to use master..[spt_values], you can also use recursive CTE like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join seq c
on c.number < t.noofdays
Output
+---------+-------------------------+-------------------------+
| leaveid | fromdate | todays |
+---------+-------------------------+-------------------------+
| 1 | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2 | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+
EDIT:
If you want the dates in specific format, you can use CONVERT like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
convert(varchar, Dateadd(day, number, fromdate), 103) fromdate,
convert(varchar, Dateadd(day, number, fromdate), 103) todays
from @table t
inner join seq c
on c.number < t.noofdays
Online Demo
Output
+---------+------------+------------+
| leaveid | fromdate | todays |
+---------+------------+------------+
| 1 | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2 | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+
answered Mar 24 at 7:40
PSKPSK
13.3k31835
You can use master..[spt_values] to generate the desired number of rows and use Dateadd to generate the dates like following.
;with cte
as (select number
from master..[spt_values]
where type = 'p')
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join cte c
on c.number < t.noofdays
Online Demo
If you don't want to use master..[spt_values], you can also use recursive CTE like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
Dateadd(day, number, fromdate) fromdate,
Dateadd(day, number, fromdate) todays
from @table t
inner join seq c
on c.number < t.noofdays
Output
+---------+-------------------------+-------------------------+
| leaveid | fromdate | todays |
+---------+-------------------------+-------------------------+
| 1 | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2 | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+
EDIT:
If you want the dates in specific format, you can use CONVERT like following.
declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
Select 0 as number
union all
Select number + 1
from seq
where number <= @maxNoOfDay
)
select number+1 as leaveid,
convert(varchar, Dateadd(day, number, fromdate), 103) fromdate,
convert(varchar, Dateadd(day, number, fromdate), 103) todays
from @table t
inner join seq c
on c.number < t.noofdays
Online Demo
Output
+---------+------------+------------+
| leaveid | fromdate | todays |
+---------+------------+------------+
| 1 | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2 | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+
answered Mar 24 at 7:40
PSKPSK
13.3k31835
edited Mar 24 at 7:55
answered Mar 24 at 7:40
PSKPSK
13.3k31835
answered Mar 24 at 7:40
PSKPSK
13.3k31835
answered Mar 24 at 7:40
PSKPSK
13.3k31835
13.3k31835
The second rowleaveidshould be 2 instead of 1
– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
add a comment |
The second rowleaveidshould be 2 instead of 1
– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
The second row
leaveid should be 2 instead of 1– Sami
Mar 24 at 7:45
The second row
leaveid should be 2 instead of 1– Sami
Mar 24 at 7:45
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
@Sami, I have corrected it. Thanks for pointing it out.
– PSK
Mar 24 at 7:56
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
Good that you understand what I mean :)
– Sami
Mar 24 at 8:11
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55321568%2frepeat-row-between-from-and-to-date-range%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
