working with date - 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

working with date

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-27 : 15:23:47

Dear Friends,

I have two tables called Pricing and Booking , i have the below sample data,

Pricing
----------

BoatId From To PricePerDay
1 7/3/2009 13/3/2009 10
1 14/3/2009 3/4/2009 5
1 4/4/2009 24/4/2009 6
2 7/3/2009 31/3/2009 20
2 1/4/2009 15/4/2009 10
3 7/3/2009 25/3/2009 50

Description : The pricing table will contain the price for the boat in different date

Booking
----------

BoatId From To TotalPrice
1 21/3/2009 27/3/2009 35
1 4/4/2009 17/4/2009 84
2 7/3/2009 11/3/2009 100
2 28/3/2009 4/4/2009 120
3 9/3/2009 17/3/2009 450

Description : The booking table contain the booking details of various boat in the various date

My Expected output is

Where BoatId=2
---------------

From To PricePerDay Available
7/3/2009 11/3/2009 20 No
12/3/2009 27/3/2009 20 Yes
28/3/2009 31/3/2009 20 No
1/4/2009 4/4/2009 10 No
5/4/2009 15/4/2009 10 Yes

Where BoatId=1
----------------

From To PricePerDay Available
7/3/2009 13/3/2009 10 Yes
14/3/2009 20/3/2009 5 Yes
21/3/2009 27/3/2009 5 No
28/3/2009 31/3/2009 5 Yes
4/4/2009 17/4/2009 6 No
18/4/2009 24/4/2009 6 Yes

Where BoatId=3
---------------

From To PricePerDay Available
7/3/2009 8/3/2009 50 Yes
9/3/2009 17/3/2009 50 NO
18/3/2009 25/3/2009 5 Yes

Please help me to get the expected output.

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-27 : 23:09:19

Dear Friends,

Please help out to get the expected out.

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-28 : 07:37:20

Friends,

I am could not able to find the solution for this.

Please help me to get the expected output.

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 01:25:47

Hello Friends,

I am still looking for the query to get expected output

Thanks in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 03:37:39

Friends,

Whether this is possible to get the expected output, or am i missing something,

Please let me know if i need to provide some more details, I am sorry if my question is wrong, Please let me know

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:46:44

Are you using sql 2005?

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 06:24:29

Dear Visakh,

Thank you for your response.

No, i am using SQL server 2000

Please help me to get the expected output for me

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 13:56:01

Hello Friends,

I am still waiting for your reply,

Hope i can get a expected output with your help here

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-31 : 07:34:16

Friends,

could any one please help me to get the expected output

Thanks in Advance

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 08:19:00

What have you tried so far?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-04-13 : 01:00:04

Friends,

I have tried with below query but not yet i got the expected output

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

Help me out to get the expected output,

Thanks in advance.

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-04-13 : 16:18:01

is it possible to get the expected output using sql query, or i need to change the table design please let me know

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources