March 2, 2006 at 1:29 am
This date range/join query was discussed in another thread but got fairly messy so I have cut down the query in the hope somebody can help... I hope this is OK.
The date range in the main WHERE clause is passed in to the query by a parameter (shown as 20060201 to 20060228) and so this is not a problem to calculate.
For each booking row that is retrieved by this query it will have a field which holds the original booking date. What I am then trying to do from this is take that booking date and get the value of all bookings that occurred in the month of that booking date. This figure then tells me the performance for the user in that month and from there I can calculate the commission payment due.
I can't figure out how I can use the figure B.BookingDate in the JOIN later in the query (i.e. to create the date range shown below as 20051201 to 20051231). Can anybody offer any suggestions?
SELECT
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
ISNULL(dtHWRevenue.transactionamount,0)
FROM
tbl_hp_bookings B WITH (NOLOCK)
--join to get all matching transactions for retrieved bookings
LEFT JOIN
tbl_hp_bookings_transactions BT WITH (NOLOCK)
ON
B.BookingID = BT.BookingID
--get the id of the adminuser who placed the booking
LEFT JOIN
tbl_hp_adminusers AU WITH (NOLOCK)
ON
B.HomeworkerID = AU.UserID
--get total of all bookings for the month when the individual booking was made - not the same as current report range
LEFT JOIN
(
SELECT
BK.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions BKT WITH (NOLOCK),
tbl_hp_bookings BK WITH (NOLOCK)
WHERE
BKT.bookingid = BK.bookingid
AND
BKT.TransactionDate BETWEEN '20051201' AND '20051231'
GROUP BY
BK.homeworkerid
) dtHWRevenue
ON
(dtHWRevenue.homeworkerid = AU.userid)
WHERE
B.PaidDate BETWEEN '20060201' AND '20060228'
GROUP BY
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
dtHWRevenue.TransactionAmount
March 2, 2006 at 5:03 am
There are several ways how to manipulate dates, this is one of them:
-- 1st day of the current month
SELECT CONVERT(char(6), @date, 112) + '01'
-- 1st day of the next month
SELECT DATEADD(month, 1, CONVERT(char(6), @date, 112) + '01')
So then, if you have a date (B.BookingDate) and want to use it in WHERE condition in such a way that all records with PaidDate from the same month are included, you can write:
WHERE B.PaidDate >= CONVERT(char(6), B.BookingDate, 112) + '01'
AND B.PaidDate < DATEADD(month, 1, CONVERT(char(6), B.BookingDate, 112) + '01')
Of course, there is also the possibility to compare using
WHERE MONTH(B.PaidDate) = MONTH(B.BookingDate) AND YEAR(B.PaidDate) = YEAR(B.BookingDate)
which will yield the correct result, but as far as I know, will perform worse.
I'm not sure whether this is what you were asking... if not, please elaborate a bit on what should be the result.
March 2, 2006 at 7:02 am
?messy. I posted a solution there. That's really not how it's meant to be ....
_/_/_/ paramind _/_/_/
March 2, 2006 at 7:24 am
Sorry - didn't mean to say your solution was messy! Meant that my query was so messy I thought it might be better to cut it right down to a simple query to try and understand where I was going wrong with it as I was struggling to understand the solution posted.
Apologies again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply