January 23, 2013 at 5:05 am
Hi there,
I've a BillDate as date, and a Mark as bit column in First table.
(Mark=0 by default)
In Second table I've FromDate as date, and ToDate as date Column.
I want to set Mark=1 if BillDate is exists between FromDate & ToDate
Let Say In First Table the data is
----------------------------
BillDate | Mark
----------------------------
2012-11-10 11:15:30 | 0
2012-12-12 09:00:00 | 0
In Second Table the data is
---------------------------------------------
FromDate | ToDate
---------------------------------------------
2012-11-01 07:00:00 | 2012-11-09 23:59:59
2012-12-08 07:00:00 | 2012-12-15 23:59:59
So in the above scenario only the second row from First table
which is having, BillDate->2012-12-12 09:00:00 will be Mark as 1
because it comes between second row of second table
I hope I've explained my scenario,
January 23, 2013 at 5:48 am
Please take a moment to read through the second link in my signature on posting code and data for the best help.
But based on your post, you need something like this
[cod="sql"]
DECLARE @bill TABLE (BillDate DATETIME, Mark BIT)
INSERT INTO @bill VALUES
('2012-11-10 11:15:30',0),
('2012-12-12 09:00:00',0)
DECLARE @Dates TABLE (FromDate DATETIME, ToDate DATETIME)
INSERT INTO @Dates VALUES
('2012-11-01 07:00:00','2012-11-09 23:59:59'),
('2012-12-08 07:00:00','2012-12-15 23:59:59')
UPDATE
B
SET
Mark = 1
FROM
@bill B
INNER JOIN
@Dates d
ON
b.BillDate >= d.FromDate
AND
b.BillDate <= d.ToDate
SELECT * FROM @bill
[/code]
January 23, 2013 at 5:58 am
Thank you so much Tunnel.
I've tried it and its working fine.
How can I mark your post as an answer?
January 23, 2013 at 6:00 am
You cannot mark them as answered on SSC, as it is primarily a discussion forum so topics are open for all to see and people may come back with their own take on how to solve your problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply