May 24, 2012 at 1:06 am
Hi..All,
I have an issue , Kindly any 1 help me on this
I have a table which stores the dates as follows
Product| FridayDate| MondayDate
ABC | 2012-06-15|2012-06-18
ABC | 2012-06-22|2012-06-25
ABC | 2012-06-29|2012-07-02
I need to check in which range this date "2012-06-27" falls. I have to get the following as output
as the date falls b/w 2012-06-25 and 2012-06-29, need to compare monday date and friday date
Product| FridayDate| MondayDate
ABC | 2012-06-22|2012-06-25
ABC | 2012-06-29|2012-07-02
Thanks in advance 🙂
May 24, 2012 at 1:27 am
I believe you are looking for something like this
CREATE TABLE #MyProductDates(Product VARCHAR(30), FridayDate DATE, MondayDate DATE)
INSERT INTO #MyProductDates(Product, FridayDate, MondayDate)
SELECT 'ABC' , '2012-06-15' , '2012-06-18'
UNION ALL
SELECT 'ABC' , '2012-06-22', '2012-06-25'
UNION ALL
SELECT 'ABC' , '2012-06-29', '2012-07-02'
SELECT * FROM #MyProductDates
DECLARE @MyCheckDate DATE = '2012-06-27'
DECLARE @PreviousMondayDate DATE
DECLARE @NextFridayDate DATE
SELECT *
FROM #MyProductDates
WHERE
MondayDate = (
SELECT MAX(MondayDate)
FROM #MyProductDates
WHERE
MondayDate < @MyCheckDate
)
UNION
SELECT *
FROM #MyProductDates
WHERE
FridayDate = (
SELECT MIN(FridayDate)
FROM #MyProductDates
WHERE
FridayDate > @MyCheckDate
)
DROP TABLE #MyProductDates
May 24, 2012 at 2:36 am
How about this?
DECLARE @QueryDate DATETIME = '2012-07-01'
SELECT mpd.Product
, mpd.FridayDate
, mpd.MondayDate
FROM #MyProductDates mpd
WHERE ( @QueryDate BETWEEN DATEADD(DD,-4,mpd.FridayDate) AND mpd.FridayDate )
OR ( @QueryDate BETWEEN mpd.MondayDate AND DATEADD(DD,4,mpd.MondayDate) )
OR ( @QueryDate BETWEEN mpd.FridayDate AND mpd.MondayDate )
May 24, 2012 at 6:05 am
Hi,
Tried using both the queries
It helped....me 🙂
Thanks a lot.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply