Select records weekly based - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select records weekly based

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-21 : 00:57:59

Dear Friends,

I have two table called 1. Pricing 2.Booking

Please find the below sample records

Pricing
--------

HotelId From To Price
1 7/3/2009 28/3/2009 1000

This table contain 4 weeks pricing from ( 7/3/2009 to 28/3/2009), Per weekly price is 1000

The booking is only available in weekly based

Booking
---------

ID From TO Price
1 14/3/2009 21/3/2009 1000

This table contain the booked week, so the hotel is booked in from 14/3/2009 to 21/3/2009

Expected OutPut
----------------

I want to display the weekly availability / booked hotel details, SO my expected output is

From To Available Price
7/3/2009 14/3/2009 YES 1000
14/3/2009 21/3/2009 NO 1000
21/3/2009 28/3/2009 YES 1000

Please help me to get the expected output,

Thanks and Regards
Dhina

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-21 : 06:26:55

U CHECK THIS ONCE.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '2/1/2009'
SELECT @EndDate = '2/28/2009'

DECLARE @TEMP TABLE ( ID INT IDENTITY(1,1), DATEFROM DATETIME,DATETO DATETIME)
INSERT INTO @TEMP
SELECT DISTINCT CASE WHEN DATEPART(DW,DATEADD(DAY, NUMBER, @STARTDATE))%7 = 0
THEN DATEADD(DAY, NUMBER, @STARTDATE) END,NULL
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P'
AND DATEADD(DAY, NUMBER, @STARTDATE) <= @ENDDATE

UPDATE T
SET DATETO = T1.DATEFROM
FROM @TEMP T
LEFT JOIN @TEMP T1 ON T1.ID - 1 = T.ID

SELECT * FROM @TEMP WHERE DATEFROM IS NOT NULL AND DATETO IS NOT NULL

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-21 : 06:50:32

Nageswar ,

Thank you for your reply,

Sorry please find my sample table and expected table details.

Pricing
--------

HotelId From To Price
1 7/3/2009 28/3/2009 1000

This table contain 4 weeks pricing from ( 7/3/2009 to 28/3/2009), Per weekly price is 1000

The booking is only available in weekly based

Booking
---------

HotelID From TO Price
1 14/3/2009 21/3/2009 1000

This table contain the booked week, so the hotel is booked in from 14/3/2009 to 21/3/2009

Expected OutPut
----------------

I want to display the weekly availability / booked hotel details, SO my expected output is

From To Available Price
7/3/2009 14/3/2009 YES 1000
14/3/2009 21/3/2009 NO 1000
21/3/2009 28/3/2009 YES 1000

Please help me to get the expected result.

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-22 : 23:55:38

Friends,

I am not able to get the expected output, and i have no idea about working in this.

Please help me to get the expected output

Thanks in advance

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 00:54:35

DECLARE @Pricing TABLE
(
HotelId int,
FromDate datetime,
ToDate datetime,
Price money
)
insert @Pricing
select 1, '3/7/2009', '3/28/2009' ,1000

DECLARE @Booking TABLE
(
HotelId int,
FromDate datetime,
ToDate datetime,
Price money
)
insert @Booking
select 1, '3/14/2009', '3/21/2009', 1000

;
WITH cte(HotelId,fromDate,ToDate,Price)
AS
(
SELECT HotelId,FromDate,FromDate + 7,Price FROM @Pricing WHERE HotelId = 1
UNION ALL
SELECT c.HotelId,c.ToDate,c.ToDate + 7,c.Price
FROM cte c JOIN @Pricing p ON c.HotelId = p.HotelId WHERE c.ToDate < p.ToDate
)

SELECT c.fromDate , c.ToDate , CASE WHEN b.HotelId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM cte c
LEFT JOIN @Booking b
ON c.HotelId = b.HotelId
AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 03:39:55

Matty,

Thank you for your reply.

I want to pass the Hotel ID in the where condition.

Please let me know is not possible geting the expected value using a single select statment?

I dont know what is the use of DECLARE, and you are trying to insert the value for the tables , I dont understand this.

Please help me with the select statement in where condition i have to pass the HotelID

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 03:45:25

If i use the above query i am getting error message

Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WITH'.

ANd i have no idea about the query used.

Please help me to sort out this

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 04:12:07

I declared table variables and inserted sample data just for reference.
;
WITH cte(HotelId,fromDate,ToDate,Price)
AS
(
SELECT HotelId,FromDate,FromDate + 7,Price FROM @Pricing WHERE HotelId = @HotelId
UNION ALL
SELECT c.HotelId,c.ToDate,c.ToDate + 7,c.Price
FROM cte c JOIN @Pricing p ON c.HotelId = p.HotelId WHERE c.ToDate < p.ToDate
)

SELECT c.fromDate , c.ToDate , CASE WHEN b.HotelId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM cte c
LEFT JOIN @Booking b
ON c.HotelId = b.HotelId
AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate

quote:
Originally posted by dhinasql

Matty,

Thank you for your reply.

I want to pass the Hotel ID in the where condition.

Please let me know is not possible geting the expected value using a single select statment?

I dont know what is the use of DECLARE, and you are trying to insert the value for the tables , I dont understand this.

Please help me with the select statement in where condition i have to pass the HotelID

Thanks in Advance

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 04:15:57

Did you miss the ';' before WITH?
;
WITH cte(HotelId,fromDate,ToDate,Price)
AS

quote:
Originally posted by dhinasql

If i use the above query i am getting error message

Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WITH'.

ANd i have no idea about the query used.

Please help me to sort out this

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 04:23:21

quote:
Originally posted by dhinasql

If i use the above query i am getting error message

Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WITH'.

ANd i have no idea about the query used.

Refer BOL for Common Table Expressions(CTE).

Please help me to sort out this

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 04:33:03

Thanks Matty,

I am working on SQL SEVER 2000, i have no idea about common Table Expressions..

I have tables and values for Pricing and Booking.

Please help me what statement exactly i have to use.

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 05:41:15

Dear Friends,

Please help me to get the expected output,

Thanks in advance

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 06:33:07

SELECT c.fromDate , c.ToDate , CASE WHEN b.HotelId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM
(
SELECT HotelId,Price,FromDate,FromDate + 7 AS ToDate
FROM
(
SELECT Hotelid,Price,ToDate,
CASE WHEN DATEPART(DW,DATEADD(day,Number,FromDate))%7 = 0 THEN DATEADD(day,Number,FromDate) END AS FromDate
FROM @Pricing , (SELECT number FROM MASTER..SPT_VALUES WHERE TYPE = 'P') n
WHERE HotelId = 1 AND DATEADD(day,Number,FromDate) < ToDate
)t
WHERE FromDate IS NOT NULL
) c
LEFT JOIN @Booking b
ON c.HotelId = b.HotelId
AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 06:44:06

Matty thank you for your quick response,

When i use the below query i am not getting any result

SELECT c.fromDate , c.ToDate , CASE WHEN b.HotelId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM
(
SELECT HotelId,Price,FromDate,FromDate + 7 AS ToDate
FROM
(
SELECT Hotelid,Price,ToDate,
CASE WHEN DATEPART(DW,DATEADD(day,Number,FromDate))%7 = 0 THEN DATEADD(day,Number,FromDate) END AS FromDate
FROM Pricing , (SELECT number FROM MASTER..SPT_VALUES WHERE TYPE = 'P') n
WHERE HotelId = 1 AND DATEADD(day,Number,FromDate) < ToDate
)t
WHERE FromDate IS NOT NULL
) c
LEFT JOIN Booking b
ON c.HotelId = b.HotelId
AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate

Please help me to get a expected output

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-23 : 06:50:05

Matty,

Thank you so much

I got the expected result.

Thanks again

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 06:53:21

I am getting results using this sample data.Did you try for this set od data?
DECLARE @Pricing TABLE
(
HotelId int,
FromDate datetime,
ToDate datetime,
Price money
)
insert @Pricing
select 1, '3/7/2009', '3/28/2009' ,1000

DECLARE @Booking TABLE
(
HotelId int,
FromDate datetime,
ToDate datetime,
Price money
)
insert @Booking
select 1, '3/14/2009', '3/21/2009', 1000
DECLARE @HotelId int

quote:
Originally posted by dhinasql

Matty thank you for your quick response,

When i use the below query i am not getting any result

SELECT c.fromDate , c.ToDate , CASE WHEN b.HotelId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM
(
SELECT HotelId,Price,FromDate,FromDate + 7 AS ToDate
FROM
(
SELECT Hotelid,Price,ToDate,
CASE WHEN DATEPART(DW,DATEADD(day,Number,FromDate))%7 = 0 THEN DATEADD(day,Number,FromDate) END AS FromDate
FROM Pricing , (SELECT number FROM MASTER..SPT_VALUES WHERE TYPE = 'P') n
WHERE HotelId = 1 AND DATEADD(day,Number,FromDate) < ToDate
)t
WHERE FromDate IS NOT NULL
) c
LEFT JOIN Booking b
ON c.HotelId = b.HotelId
AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate

Please help me to get a expected output

Thanks in advance

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 07:03:20

You are welcome.

quote:
Originally posted by dhinasql

Matty,

Thank you so much

I got the expected result.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources