Hi

That's correct 1 booking with multiple transactions within that booking.

The reason for requiring the full monthly amount of bookings for the month in which each booking was made is that...

Hi

Yes it exists in both tables - it's always the same so that a separate transaction report can be run apparently... left over from a feature that never quite made...

Hi

I have scripted the two tables involved (I think!!) - see below.

The 2nd function certainly seems to retrieve broadly what I expect it to. Now just need to wrap...

Hello

I got this response from Query Analyzer:

The column prefix 'B' does not match with a table name or alias name used in the query. The query I tried was:

--...

Hello - sorry I completely missed one of the posts in this query. I am looking at the solution now!

Simon

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...

Yep it's not the best query in the world for sure. I didn't design the database so it's a case of trying to get the data out of it...

For reference the whole procedure is shown below - I am sure it is not very well optimised but as mentioned in previous posts I am filling in and somewhat...

Ok - end of month suggests I didn't quite get this right. The snippet below is a JOIN from my main query and I am having a problem feeding...

Hi

I went for this first one:

LEFT JOIN

(

SELECT

B.homeworkerid,

SUM(transactionamount) AS TransactionAmount

FROM

tbl_hp_bookings_transactions BT WITH (NOLOCK),

tbl_hp_bookings B WITH (NOLOCK)

WHERE

BT.bookingid = B.bookingid

AND

datepart(m, BT.TransactionDate) = datepart(m, B.BookingDate)

GROUP BY B.homeworkerid

) dtHWDiscount

ON

(dtHWDiscount.homeworkerid = AU.userid)

but subbed in B.BookingDate in...

That's what I ended up using. The final VBscript code (off topic alert!) went something like:

For fnum = 0 To rsRRS.Fields.Count-1

strReturn = strReturn & rsRRS.Fields(fnum).Name & ","

Next

strReturn = strReturn...

Thanks!

Yep sorry - had actually been using - but using it in the outer joins. I have got the procedure running a treat now which just leaves me with...

Thanks guys - as you can see I'm a bit out of my depth here - covering for a colleague on annual leave.

I was previously retrieving a figure for gross...

Hi

Thanks for the advice - point taken. So the 2nd derived table would go like this:

INNER JOIN

(

SELECT

b.homeworkerid, COUNT(DISTINCT(b.bookingid)) AS MonthlyBookings

FROM

tbl_hp_bookings_transactions bt WITH (NOLOCK),

tbl_hp_bookings b WITH (NOLOCK)

WHERE

bt.bookingid...