How to calculate price For particular dates

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

How to calculate price For particular dates

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 13:19:47

Hi Friends

I had one table named Booking which has PId ,Fromdate,ToDate,Price as Fields

HId FromDate ToDate Price
1 05/26/2009 05/28/2009 10
2 05/29/2009 05/30/2009 20
3 05/31/2009 06/04/2009 30
4 05/20/2009 05/25/2009 40

Booking table has Fromdate and ToDate which means that Hotels can be
booked for that particular dates only.

Price is for one day only not for Fromdate to toDate

Here when user tries to book hotel from 05/26/2009 to
05/28/2009 i need to calculate the price as 3days(FromDate - Todate) * 10 = 30
and also when user tries book hotel from 05/26/2009 to 05/29/2009
i need to calculate price as 3days * 10=30 and 1day * 20 =20
So Total as 50.

And also when user try to book from 06/20/2009 to 06/22/2009
i need to return as 'No Booking Available'

Please help me regarding the query.

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:27:17

[code]
DECLARE @Price int

SELECT @Price=SUM(Price)
FROM
(
SELECT t.HId,DATEADD(dd,v.number,t.FromDate) AS BookingDate,t.Price
FROM YourTable t
CROSS JOIN master.spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.FromDate)<=t.ToDate
)m
WHERE BookingDate BETWEEN @FromDate AND @ToDate

IF @Price > 0
SELECT @Price AS TotalPrice
ELSE
SELECT 'No Booking Available'
[/code]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 13:37:03

Hi visakh

Thank you for your quick reply.

I am getting error as "Invalid object name 'master.spt_values'."

Please help me regarding this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:38:43

quote:
Originally posted by Kotti

Hi visakh

Thank you for your quick reply.

I am getting error as "Invalid object name 'master.spt_values'."

Please help me regarding this.

thats a typo it should be master..spt_values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:45:18

if sql 2005, you can do like this also


DECLARE @Price int

;With Booking_CTE (HId,BookingDt,EndDt,Price)
AS
(SELECT HId,FromDate,ToDate,Price
FROM YourTable
UNION ALL
SELECT HId,DATEADD(dd,1, BookingDt), EndDt,Price
FROM Booking_CTE
WHERE DATEADD(dd,1, BookingDt)<= EndDt
)

SELECT @Price=SUM(Price)
FROM Booking_CTE
WHERE BookingDt BETWEEN @FromDate AND @ToDate

OPTION (MAXRECURSION 0)

IF @Price > 0
SELECT @Price AS TotalPrice
ELSE
SELECT 'No Booking Available'

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 13:46:21

Hi Visakh
I am working on SQLSERVER 2000 and Sorry i don't know about
"Invalid object name 'master.spt_values"

Please help me regarding this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:48:12

[code]DECLARE @Price int

SELECT @Price=SUM(Price)
FROM
(
SELECT t.HId,DATEADD(dd,v.number,t.FromDate) AS BookingDate,t.Price
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.FromDate)<=t.ToDate
)m
WHERE BookingDate BETWEEN @FromDate AND @ToDate

IF @Price > 0
SELECT @Price AS TotalPrice
ELSE
SELECT 'No Booking Available'
[/code]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 13:57:51

Hi visakh

i am getting error like this
"The sum or average aggregate operation cannot take a varchar data type as an argument."
I kept the price field as Varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:59:45

whats the datatype of price field?

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 14:07:03

I kept it has Varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:09:13

why? its storing price value which is always numeric. then why use varchar as datatype? in that case, you should replace SUM(Price) by SUM(CAST(Price AS int))

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 14:19:06

visakh,

Is there any possibility to do with out using Declare method?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:29:18

the declare was only to get the value in a variable.why whats the problem with it?

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 14:43:05

I was unable to execute the query using C# Asp.Net

Thats why i asked .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:46:13

so you're trying to call this from .net? why not put this in a stored procedure and then call it in .net?

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 14:52:55

Ok I executed the query in .Net.

Here i need some change in query

when user books hotel from 05/16/2009 to 05/20/2009 ,it sholud return 'No Booking Available'.
Since there is No price for the dates 05/16/2009 ,05/17/2009 ,05/18/2009 ,05/19/2009

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-26 : 23:48:19

Hi

Any one help me on this Query.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-27 : 05:27:47

Hi visakh

Please help me regarding the query.

Your help will be Much help full for me.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-27 : 07:38:05

Hi visakh

I used your code and Modified the int to decimal but i am getting as integer only.

DECLARE @Price decimal

SELECT @Price=SUM(CAST(Price AS decimal))
FROM
(
SELECT t.HId,DATEADD(dd,v.number,t.FromDate) AS BookingDate,t.Price
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.FromDate)<=t.ToDate
)m
WHERE BookingDate BETWEEN @FromDate AND @ToDate

IF @Price > 0
SELECT @Price AS TotalPrice
ELSE
SELECT 'No Booking Available'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 09:46:24

you have not declared scale and precision for decimal variable. try like this and see:-


DECLARE @Price decimal(15,2)

SELECT @Price=SUM(CAST(Price AS decimal(15,2)))
FROM
(
SELECT t.HId,DATEADD(dd,v.number,t.FromDate) AS BookingDate,t.Price
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.FromDate)<=t.ToDate
)m
WHERE BookingDate BETWEEN @FromDate AND @ToDate

IF @Price > 0
SELECT @Price AS TotalPrice
ELSE
SELECT 'No Booking Available'

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-27 : 12:11:52

Hi Visakh

Once again thank you for your help.I am getting the value in decimal.

Can you make a another one change in the query ,i asked this previously.
"when user books hotel from 05/16/2009 to 05/20/2009 ,it sholud return 'No Booking Available'.
Since there is No price for the dates 05/16/2009 ,05/17/2009 ,05/18/2009 ,05/19/2009 "

Thanks your help will be much appreciated.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources