Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query - Pulling Data from another table Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
wafw1971 (2/21/2013)
Hi Kingston

I didn't think of that and I should have, each price is a daily price so if the price for a pitch is £9.00 on the 31/01/2010 and the price of the pitch is £9.70 for the 01/01/2011 then the total should be £18.70

And after looking at the data that has not happened.

How do we amend the code to compensate for that?


This will be a bit complex
I will try and post a solution as soon as I get some time for that
Meanwhile I think somebody else might help as well..



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422617
Posted Thursday, February 21, 2013 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
The below code will help you handle the overlapping conditions

SELECT	Bookings.Booking_Skey, 
SUM (
(
DATEDIFF(
DAY,
CASE
WHEN PitchValues.StartDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.StartDate
ELSE Bookings.ArrivalDate
END,
CASE
WHEN PitchValues.EndDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.EndDate
ELSE Bookings.DepartureDate
END
) + 1
) * PitchValues.Value
) AS BookingValue, PitchValues.PitchType_Skey
FROM Bookings
INNER JOIN PitchValues
ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
AND (
Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate OR
Bookings.DepartureDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate OR
PitchValues.StartDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate OR
PitchValues.EndDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate
)
WHERE (Bookings.Booking_Skey = 1)
GROUP BY Bookings.Booking_Skey, PitchValues.PitchType_Skey




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422701
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse