How to split date range in to two using sql query?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

this is the table

DECLARE @Product TABLE (Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values
('20110320', '20110330', 101)

DECLARE @Beg Datetime, @End Datetime
SET @Beg = '20110324'
SET @End = '20110326'

i wand to split my date range 20 to 30 by 24 to 26

I need my out put like this

20/03/2011  23/03/2011 101

27/03/2011  30/03/2011 101

what is the sql query for this

Posted 23-Mar-11 11:01am

amtechq8

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-03-23T11:12:00

Solution 1

Have to ask: Was there something wrong/not working with the answer in How to update a date range?[^] or is this a different situation?

[Addition]
Since this was a new case, perhaps something like this:

SQL

DECLARE @Product TABLE (Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values ('20110320', '20110330', 101)
DECLARE @Beg Datetime, @End Datetime
SET @Beg = '20110324'
SET @End = '20110326'

DECLARE @Temp TABLE (DateVal  Datetime, Date_Type int, Product_ID INT)

-- insert each date to temp tabl
insert into @Temp
select Date_From, 1, Product_ID
from @Product
union all
select Date_To, 2, Product_ID
from @Product

-- add the date variables to temp table NOTE fixed product so it possibly comes from a variable etc.
insert into @Temp
select @Beg, 2, 101
union all
select @End, 1, 101

-- remove existing rows, most likely here should be a WHERE clause restricting the deletion
delete from @Product;

-- recreate the product table based on temp table
insert into @Product
select  q1.dateval,
        ( select min(q2.dateval)
          from @Temp q2
          where q2.Date_type = 2
          and q2.dateval > q1.dateval),
        q1.Product_ID
from @Temp q1
where q1.Date_type = 1

SELECT * FROM @Product ORDER BY Date_From

Most likely you have to add different kinds of where clauses etc to make this fit into your needs but since I had only 1 test case data I verified the behaviour against it.

Hopefully it helps.

Posted 23-Mar-11 11:12am

Wendelius

Updated 23-Mar-11 12:11pm

v3

Comments

amtechq8 23-Mar-11 17:33pm

1. already get the old issue problem only remaining this
2. now i have only one issue that is this...

Wendelius 23-Mar-11 17:41pm

Ok, just a note: voting or marking the solution as an answer would help to know if the solution provided was helpful.

About this issue, can there be several rows in the db that cover the date range in the variables? For example could you have rows:
- 20110320 20110330
- 20110325 20110330 etc.

amtechq8 23-Mar-11 17:47pm

This is the first issue

DECLARE @Product TABLE ( Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values ('20110323', '20110324', 101);
INSERT INTO @Product Values ('20110325', '20110326', 101);
INSERT INTO @Product Values ('20110327', '20110329', 101);
DECLARE @Beg Datetime, @End Datetime
-- test case 1
SET @Beg = '20110323'
SET @End = '20110323'
-- test case 2
--SET @Beg = '20110324'
--SET @End = '20110327'
-- Set in range dates to null
UPDATE @Product SET
Date_From = CASE
WHEN Date_From BETWEEN @Beg AND @End THEN NULL
ELSE Date_From
END,
Date_To = CASE
WHEN Date_To BETWEEN @Beg AND @End THEN NULL
ELSE Date_To
END;
-- remove rows having no dates
DELETE FROM @Product
WHERE Date_From IS NULL
AND Date_To IS NULL;
--update the missing ends to beginning/end correspondingly
UPDATE @Product
SET Date_From = CASE
WHEN Date_From IS NULL THEN DATEADD(day, +1, @End)
ELSE Date_From
END,
Date_To = CASE
WHEN Date_To IS NULL THEN DATEADD(day, -1, @Beg)
ELSE Date_To
END;

SELECT * FROM @Product
ORDER BY Date_From

this working fine...
------------------------------------

Issue 2
INSERT INTO @Product Values ('20110323', '20110330', 101);
SET @Beg = '20110325'
SET @End = '20110327'

i need out put like this

2011-03-23 2011-03-24 101
2011-03-28 2011-03-30 101

Wendelius 23-Mar-11 18:11pm

Solution updated

amtechq8 23-Mar-11 18:32pm

thanks for the solution...

is it possible to fix this both issues in one sql code

Issue 1
INSERT INTO @Product Values ('20110323', '20110324', 101);
INSERT INTO @Product Values ('20110325', '20110326', 101);
INSERT INTO @Product Values ('20110327', '20110331', 101);

DECLARE @Beg Datetime, @End Datetime
SET @Beg = '20110328'
SET @End = '20110329'

amtechq8 23-Mar-11 19:02pm

in the above i add new field called New_Date_Format as int

DECLARE @Product TABLE (New_Date_Format INT,Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values ('20110323', '20110324', 101);
INSERT INTO @Product Values ('20110325', '20110326', 101);
INSERT INTO @Product Values ('20110327', '20110331', 101);

--
Date_From = CASE
WHEN Date_From BETWEEN @Beg AND @End THEN NULL
ELSE Date_From
END,
Date_To = CASE
WHEN Date_To BETWEEN @Beg AND @End THEN NULL
ELSE Date_To
END,

New_Date_Format =convert(varchar(8),(Date_From),112)

but the out put is not coming good when i add "New_Date_Format"

it coming like this

20110323 2011-03-23 00:00:00.000 2011-03-23 00:00:00.000 101
20110325 2011-03-26 00:00:00.000 2011-03-26 00:00:00.000 101
20110327 2011-03-27 00:00:00.000 2011-03-31 00:00:00.000 101

but i need out put like this.. same "Date_From" in "New_Date_Format"

20110323 2011-03-23 00:00:00.000 2011-03-23 00:00:00.000 101
20110326 2011-03-26 00:00:00.000 2011-03-26 00:00:00.000 101
20110327 2011-03-27 00:00:00.000 2011-03-31 00:00:00.000 101