Date Range for Join Query
-
Hi
I have a value which I retrieve in a SQL query which is a Booking Date for system.
In a later inner join I need to get more information about this booking as follows:
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
BT.TransactionDate BETWEEN ******DATE RANGE******
GROUP BY B.homeworkerid
) dtHWDiscount
ON
(dtHWDiscount.homeworkerid = AU.userid)
Previously my queries have only needed the date range as sent in by the query so I have used something like:
@StartSerial AND DateAdd(d,1,@EndSerial)
However for this particular query I need to take the date in the main query (which is held as B.BookingDate) and then retrive all information that was recorded in this month.
For example if B.BookingDate is 28/12/2005 then I need the date range in the inner join to become:
01/12/2005 AND 01/01/2006
How do I create a date range from the value I retrieve from the other table?
Thanks in advance for any assistance...
-
Try this:
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, @StartSerial)
GROUP BY B.homeworkerid
) dtHWDiscount
ON
(dtHWDiscount.homeworkerid = AU.userid)
or this:
declare @serialstart datetime
select @serialstart = '20 Dec 2006'
declare @startdate datetime
declare @Enddate datetime
select @StartDate = convert(datetime, convert(varchar, datepart(m, @SerialStart)) + '/01/' + convert(varchar, datepart(yyyy, @SerialStart))),
@Enddate = convert(datetime, convert(varchar, datepart(m, dateadd(m, 1, @SerialStart))) + '/01/' + convert(varchar, datepart(yyyy, dateadd(m, 1, @SerialStart))))
select @StartDate, @Enddate
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
BT.TransactionDate between @startdate and @Enddate
GROUP BY B.homeworkerid
) dtHWDiscount
ON
(dtHWDiscount.homeworkerid = AU.userid)
-
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 place of @StartSerial as I needed to use the retrieved B.BookingDate value as it could be a different month to @StartSerial which is basically just the date range for the report.
Assuming that's OK I seem to get the results I was expecting. Does that all sound right? If so, thanks very much!
-
Just 2 general comments:
Best practice dictates that table names should be singular not plural because on row is a booking, not a bookings.
use INNER JOIN syntax such as:
FROM tbl_hp_bookings_transactions BT WITH (NOLOCK)
INNER JOIN tbl_hp_bookings B WITH (NOLOCK)
ON BT.bookingid = B.bookingid and datepart(m, BT.TransactionDate) = datepart(m, B.BookingDate)
-
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 the correct date range in to the join.
The date range is different for each record that is retrieved in the main SELECT query. The value that holds the date in the main select query is B.BookingID (but it's a different B table to that listed below).
How can I get a date for each record I retrieve and then act on this in the JOIN? I tried @OrigBookingDate = B.BookingID but I get an error about not being able to assign variables at the same time as retrieving data.
Any help is much appreciated as I am tearing my hair out now!
Thanks
LEFT JOIN
(
SELECT
B.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK),
tbl_hp_customers C WITH (NOLOCK)
WHERE
BT.bookingid = B.bookingid
AND
B.CustomerID = C.CustomerID
AND
B.ConfirmTag = 'Y'
AND
C.OwnContact != 'Y'
AND
BT.TransactionDate BETWEEN startdate AND enddate
AND
BT.transactiontypeid = 1000
GROUP BY
B.homeworkerid
) dtHWRevenue
ON
(dtHWRevenue.homeworkerid = AU.userid)
-
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 out of my depth! It's the HWRevenue JOIN which is incorrect as it is taking the input date range @StartSerial but should be taking the B.BookingDate value in the main query:
CREATE PROCEDURE sproc_HP_CMC_Reports_Commission_AdminByHW
(
@Field varchar(50),
@SortDir varchar(5),
@StartSerial varchar(10),
@EndSerial varchar(10),
@HomeworkerID int
)
AS
SELECT
B.BookingID,
ISNULL(UPPER(C.CustomerSurname),'') AS CustomerSurname,
ISNULL(UPPER(C.CustomerFirstname),'') AS CustomerFirstname,
B.CustomerID,
B.YTReference,
B.DepartureDate,
B.TransactionCost,
UPPER(AU.Firstname),
UPPER(AU.Surname),
ISNULL(dtMonthlyRevenue.TransactionAmount,0),
ISNULL(dtMonthlyRevenue.CommissionAmount,0),
ISNULL(dtMonthlyDiscounts.MonthlyDiscount,0),
ISNULL(dtMonthlyDiscounts.CommissionDeductions,0),
ISNULL(dtCardFees.CardFeeTotal,0),
(ISNULL(dtMonthlyRevenue.CommissionAmount,0)-ISNULL(dtMonthlyDiscounts.CommissionDeductions,0)+ISNULL(dtCardFees.CardFeeTotal,0)-B.TransactionCost) AS NetCommission,
ISNULL(dtHWRevenue.transactionamount,0),
ISNULL(dtHWDiscount.transactionamount,0),
ISNULL(dtHWRevenue.transactionamount,0)-ISNULL(dtHWDiscount.transactionamount,0) AS HWBookings,
C.OwnContact,
(CASE
WHEN C.OwnContact = 'Y' THEN 50
WHEN C.OwnContact = 'N' AND (ISNULL(dtHWRevenue.transactionamount,0)-ISNULL(dtHWDiscount.transactionamount,0)) >= 40000 THEN 35
WHEN C.OwnContact = 'N' AND (ISNULL(dtHWRevenue.transactionamount,0)-ISNULL(dtHWDiscount.transactionamount,0)) >= 30000 THEN 30
ELSE 25
END) AS Rate,
((CAST((CASE
WHEN C.OwnContact = 'Y' THEN 50
WHEN C.OwnContact = 'N' AND (ISNULL(dtHWRevenue.transactionamount,0)-ISNULL(dtHWDiscount.transactionamount,0)) >= 40000 THEN 35
WHEN C.OwnContact = 'N' AND (ISNULL(dtHWRevenue.transactionamount,0)-ISNULL(dtHWDiscount.transactionamount,0)) >= 30000 THEN 30
ELSE 25
END) AS DECIMAL(18,2))/100)* (ISNULL(dtMonthlyRevenue.CommissionAmount,0)-ISNULL(dtMonthlyDiscounts.CommissionDeductions,0)+ISNULL(dtCardFees.CardFeeTotal,0)-B.TransactionCost)
) AS HWComm,
B.BookingDate
--
FROM
tbl_hp_bookings B WITH (NOLOCK)
--get booking transactions
INNER JOIN
tbl_hp_bookings_transactions BT WITH (NOLOCK)
ON
B.BookingID = BT.BookingID
AND
BT.TransactionTypeID 1001
--get customer surname
LEFT JOIN
tbl_hp_customers C WITH (NOLOCK)
ON
B.CustomerID = C.CustomerID
--get homeworker name
LEFT JOIN
tbl_hp_adminusers AU WITH (NOLOCK)
ON
B.HomeworkerID = AU.UserID
--get booking totals
LEFT JOIN
(
SELECT
B.bookingid,
SUM(transactionamount) AS TransactionAmount,
SUM(commissionamount) AS CommissionAmount
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK)
WHERE
BT.bookingid = B.bookingid
AND
B.ConfirmTag = 'Y'
AND
B.PaidTag = 'Y'
AND
BT.transactiontypeid = 1000
GROUP BY
B.bookingid
) dtMonthlyRevenue
ON
(dtMonthlyRevenue.bookingid = B.bookingid)
-- get commissions & discounts
LEFT JOIN
(
SELECT
B.bookingid,
SUM(transactionamount) AS MonthlyDiscount,
SUM(commissionamount) AS CommissionDeductions
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK)
WHERE
BT.bookingid = B.bookingid
AND
B.ConfirmTag = 'Y'
AND
B.PaidTag = 'Y'
AND
BT.transactiontypeid = 1002
GROUP BY
B.bookingid
) dtMonthlyDiscounts
ON
(dtMonthlyDiscounts.bookingid = B.bookingid)
-- get commissions & discounts
LEFT JOIN
(
SELECT
B.bookingid,
SUM(transactionamount) AS CardFeeTotal
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK)
WHERE
BT.bookingid = B.bookingid
AND
B.ConfirmTag = 'Y'
AND
B.PaidTag = 'Y'
AND
BT.transactiontypeid = 1003
GROUP BY
B.bookingid
) dtCardFees
ON
(dtCardFees.bookingid = B.bookingid)
--get booking totals per homeworker
LEFT JOIN
(
SELECT
B.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK),
tbl_hp_customers C WITH (NOLOCK)
WHERE
BT.bookingid = B.bookingid
AND
B.CustomerID = C.CustomerID
AND
B.ConfirmTag = 'Y'
AND
C.OwnContact != 'Y'
AND
BT.TransactionDate BETWEEN @StartSerial AND DateAdd(d,1,@EndSerial)
AND
BT.transactiontypeid = 1000
GROUP BY
B.homeworkerid
) dtHWRevenue
ON
(dtHWRevenue.homeworkerid = AU.userid)
LEFT JOIN
(
SELECT
B.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_bookings B WITH (NOLOCK),
tbl_hp_customers C WITH (NOLOCK)
WHERE
B.CustomerID = C.CustomerID
AND
BT.bookingid = B.bookingid
AND
B.ConfirmTag = 'Y'
AND
C.OwnContact != 'Y'
AND
BT.TransactionDate BETWEEN @StartSerial AND DateAdd(d,1,@EndSerial)
AND
BT.transactiontypeid = 1002
GROUP BY
B.homeworkerid
) dtHWDiscount
ON
(dtHWDiscount.homeworkerid = AU.userid)
--where & group by clauses
WHERE
B.PaidTag = 'Y'
AND
B.PaidDate BETWEEN @StartSerial AND DateAdd(d,1,@EndSerial)
AND
B.HomeworkerID = @HomeworkerID
GROUP BY
B.BookingID,
B.YTReference,
B.CustomerID,
B.DepartureDate,
B.TransactionCost,
AU.FirstName,
AU.Surname,
C.CustomerFirstname,
dtMonthlyRevenue.TransactionAmount,
dtMonthlyRevenue.CommissionAmount,
dtMonthlyDiscounts.MonthlyDiscount,
dtMonthlyDiscounts.CommissionDeductions,
dtCardFees.CardFeeTotal,
dtHWRevenue.TransactionAmount,
dtHWDiscount.TransactionAmount,
C.OwnContact,
B.BookingDate,
C.CustomerSurname /*distinct bookings*/
ORDER BY
CASE
WHEN @SortDir = 'a' AND @Field = 'client' THEN C.CustomerSurname
WHEN @SortDir = 'a' AND @Field = 'expert' THEN AU.Firstname
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'client' THEN C.CustomerSurname
WHEN @SortDir = 'd' AND @Field = 'expert' THEN AU.Firstname
END DESC,
--numeric sorts
CASE
WHEN @SortDir = 'a' AND @Field = 'ref' THEN B.ytreference
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'ref' THEN B.ytreference
END DESC,
--currency sorts
CASE
WHEN @SortDir = 'a' AND @Field = 'transchg' THEN B.TransactionCost
WHEN @SortDir = 'a' AND @Field = 'value' THEN dtMonthlyRevenue.TransactionAmount
WHEN @SortDir = 'a' AND @Field = 'grossc' THEN dtMonthlyRevenue.CommissionAmount
WHEN @SortDir = 'a' AND @Field = 'discount' THEN dtMonthlyDiscounts.CommissionDeductions
WHEN @SortDir = 'a' AND @Field = 'ccfee' THEN dtCardFees.CardFeeTotal
WHEN @SortDir = 'a' AND @Field = 'netc' THEN (ISNULL(dtMonthlyRevenue.CommissionAmount,0)-ISNULL(dtMonthlyDiscounts.CommissionDeductions,0)+ISNULL(dtCardFees.CardFeeTotal,0)-B.TransactionCost)
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'transchg' THEN B.TransactionCost
WHEN @SortDir = 'd' AND @Field = 'value' THEN dtMonthlyRevenue.TransactionAmount
WHEN @SortDir = 'd' AND @Field = 'grossc' THEN dtMonthlyRevenue.CommissionAmount
WHEN @SortDir = 'd' AND @Field = 'discount' THEN dtMonthlyDiscounts.CommissionDeductions
WHEN @SortDir = 'd' AND @Field = 'ccfee' THEN dtCardFees.CardFeeTotal
WHEN @SortDir = 'd' AND @Field = 'netc' THEN (ISNULL(dtMonthlyRevenue.CommissionAmount,0)-ISNULL(dtMonthlyDiscounts.CommissionDeductions,0)+ISNULL(dtCardFees.CardFeeTotal,0)-B.TransactionCost)
END DESC,
--date sorts
CASE
WHEN @SortDir = 'a' AND @Field = 'dod' THEN B.DepartureDate
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'dod' THEN B.DepartureDate
END DESC
GO
-
In terms of patience it was surely good exercise to write this procedure.
When I see 'one-size-fits-all' procedures like this I'd suggest you take a side view on some basic oo-design guidelines. We're talking of SQL, clearly, but can you administer this? Will you really be able to adjust this to changing business rules?
A few simple things:
1. Keep the ordering out of the procedure, for example by first creating the result set within a table valued function or a temptable and later applying the ordering.
2. Don't mix up different style join-syntax.
3. Apply 'reader' information AFTER you've calculated the core facts. (e.g. ISNULL(UPPER(C.CustomerSurname),'') AS CustomerSurname, ISNULL(UPPER(C.CustomerFirstname),'') AS CustomerFirstname etc.... ) AND resolve the Core within a derived table for clarity.
I'd rather not have calculated my revenue with this procedure
How about posting the databse design? It's surely questionable.
_/_/_/ paramind _/_/_/ -
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 now if possible.
I have cut the query down below to focus on the part that needs attention.
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
-
-- The basics for your special date range / just to clrify and test
DECLARE @dtmSome DATETIME,
@dtmStart DATETIME,
@dtmEnd DATETIME
SET @dtmSome = '20060223'
SET @dtmStart = CAST(CAST(DATEPART(yyyy,@dtmSome)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtmSome),2),' ','0') + '01' AS DATETIME)
SET @dtmEnd = DATEADD(m,1,@dtmStart)
PRINT @dtmStart
PRINT @dtmEnd
-----------------------------------
-- a few more things:
-- 1. I have removed the NOLOCK hints for clarity only 😉
-- 2. I have changed your join syntax in the inner query (not a must, but good advise)
-- 3. I'd rather not use "between"! Between means, you include the upper end of the interval:
-- if so, you will have to care of the time portion: don't fumble around with milliseconds like lots of people do
-- not concise, not elegant, potentially wrong
-- 4. of course should pass parameters for the dates in the where clause
-- 5. and last: It will slow down performance a bit, but I'd prefer to have the calculation in question
-- in a user defined function with the parameters udf_Calc_dtHWRevenue(@dtminQuestion, @HomeWorkerID)
SELECT
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
ISNULL(dtHWRevenue.transactionamount,0)
FROM tbl_hp_bookings B
LEFT JOIN tbl_hp_bookings_transactions BT
ON B.BookingID = BT.BookingID
LEFT JOIN tbl_hp_adminusers AU
ON B.HomeworkerID = AU.UserID
LEFT JOIN
(SELECT
BK.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM tbl_hp_bookings_transactions BKT
INNER JOIN tbl_hp_bookings BK
ON BKT.bookingid = BK.bookingid
WHERE
BKT.TransactionDate >= CAST(CAST(DATEPART(yyyy,B.BookingDate)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,B.BookingDate),2),' ','0') + '01' AS DATETIME)
AND BKT.TransactionDate < DATEADD(m,1,CAST(CAST(DATEPART(yyyy,B.BookingDate)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,B.BookingDate),2),' ','0') + '01' AS DATETIME))
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
-- I hope this helps
_/_/_/ paramind _/_/_/ -
-- A version with a table valued UDF, just to get the idea (NOT TESTED, care of performance)
CREATE FUNCTION udf_Calc_dtHWRevenue
(@dtminQuestion DATETIME)
RETURNS @tblBookingSum TABLE (HomeworkerID INT PRIMARY KEY,
TransactionAmount MONEY DEFAULT 0)
AS
BEGIN
DECLARE @dtmStart DATETIME,
@dtmEnd DATETIME
SET @dtmStart = CAST(CAST(DATEPART(yyyy,@dtminQuestion)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtminQuestion),2),' ','0') + '01' AS DATETIME)
SET @dtmEnd = DATEADD(m,1,@dtmStart)
INSERT INTO @tblBookingSum
SELECT
BK.homeworkerid,
COALESCE(SUM(transactionamount),0) --COALESCE IS THE ANSI-equivalent OF ISNULL( ...
FROM tbl_hp_bookings_transactions BKT
INNER JOIN tbl_hp_bookings BK
ON BKT.bookingid = BK.bookingid
WHERE
BKT.TransactionDate >= @dtmStart
AND BKT.TransactionDate < @dtmEnd
GROUP BY
BK.homeworkerid
RETURN
END
------------------------------------------------------
SELECT
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
ISNULL(dtHWRevenue.transactionamount,0)
FROM tbl_hp_bookings B
LEFT JOIN tbl_hp_bookings_transactions BT
ON B.BookingID = BT.BookingID
LEFT JOIN tbl_hp_adminusers AU
ON B.HomeworkerID = AU.UserID
LEFT JOIN dbo.udf_Calc_dtHWRevenue(B.BookingDate) AS dtHWRevenue
ON AU.UserID = dtHWRevenue.HomeworkerID
WHERE
B.PaidDate BETWEEN '20060201' AND '20060228'
GROUP BY
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
dtHWRevenue.TransactionAmount
_/_/_/ paramind _/_/_/ -
-- And an inline version of the above function (shoulf perform better)
CREATE FUNCTION udf_Calc_dtHWRevenue_02
(@dtminQuestion DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT
BK.homeworkerid,
COALESCE(SUM(transactionamount),0) AS TransactionAmount
FROM tbl_hp_bookings_transactions BKT
INNER JOIN tbl_hp_bookings BK
ON BKT.bookingid = BK.bookingid
WHERE
BKT.TransactionDate >= CAST(CAST(DATEPART(yyyy,@dtminQuestion)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtminQuestion),2),' ','0') + '01' AS DATETIME)
AND BKT.TransactionDate < DATEADD(m,1,CAST(CAST(DATEPART(yyyy,@dtminQuestion)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtminQuestion),2),' ','0') + '01' AS DATETIME))
GROUP BY
BK.homeworkerid)
_/_/_/ paramind _/_/_/ -
Hello - sorry I completely missed one of the posts in this query. I am looking at the solution now!
Simon
-
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:
-- The basics for your special date range / just to clrify and test
DECLARE @dtmSome DATETIME,
@dtmStart DATETIME,
@dtmEnd DATETIME
SET @dtmSome = '20060223'
SET @dtmStart = CAST(CAST(DATEPART(yyyy,@dtmSome)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtmSome),2),' ','0') + '01' AS DATETIME)
SET @dtmEnd = DATEADD(m,1,@dtmStart)
PRINT @dtmStart
PRINT @dtmEnd
-----------------------------------
-- a few more things:
-- 1. I have removed the NOLOCK hints for clarity only 😉
-- 2. I have changed your join syntax in the inner query (not a must, but good advise)
-- 3. I'd rather not use "between"! Between means, you include the upper end of the interval:
-- if so, you will have to care of the time portion: don't fumble around with milliseconds like lots of people do
-- not concise, not elegant, potentially wrong
-- 4. of course should pass parameters for the dates in the where clause
-- 5. and last: It will slow down performance a bit, but I'd prefer to have the calculation in question
-- in a user defined function with the parameters udf_Calc_dtHWRevenue(@dtminQuestion, @HomeWorkerID)
SELECT
B.BookingID,
AU.Surname,
B.DepartureDate,
B.BookingDate,
ISNULL(dtHWRevenue.transactionamount,0)
FROM tbl_hp_bookings B
LEFT JOIN tbl_hp_bookings_transactions BT
ON B.BookingID = BT.BookingID
LEFT JOIN tbl_hp_adminusers AU
ON B.HomeworkerID = AU.UserID
LEFT JOIN
(SELECT
BK.homeworkerid,
SUM(transactionamount) AS TransactionAmount
FROM tbl_hp_bookings_transactions BKT
INNER JOIN tbl_hp_bookings BK
ON BKT.bookingid = BK.bookingid
WHERE
BKT.TransactionDate >= CAST(CAST(DATEPART(yyyy,B.BookingDate)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,B.BookingDate),2),' ','0') + '01' AS DATETIME)
AND BKT.TransactionDate < DATEADD(m,1,CAST(CAST(DATEPART(yyyy,B.BookingDate)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,B.BookingDate),2),' ','0') + '01' AS DATETIME))
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
I really appreciate your assistance...
-
Unfortunately, I cannot test without the tables at hand. Can you script them (using QA) ? Now that we've gone that far, we should finish it up
Have you tried the second function? You can test it independantly. This will also be good for control:
CREATE FUNCTION udf_Calc_dtHWRevenue_02
(@dtminQuestion DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT
BK.homeworkerid,
COALESCE(SUM(transactionamount),0) AS TransactionAmount
FROM tbl_hp_bookings_transactions BKT
INNER JOIN tbl_hp_bookings BK
ON BKT.bookingid = BK.bookingid
WHERE
BKT.TransactionDate >= CAST(CAST(DATEPART(yyyy,@dtminQuestion)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtminQuestion),2),' ','0') + '01' AS DATETIME)
AND BKT.TransactionDate < DATEADD(m,1,CAST(CAST(DATEPART(yyyy,@dtminQuestion)AS CHAR(4)) + REPLACE(STR(DATEPART(mm,@dtminQuestion),2),' ','0') + '01' AS DATETIME))
GROUP BY
BK.homeworkerid)
-------------------
SELECT * FROM dbo.udf_Calc_dtHWRevenue_02(@someDate)
... should return the sum for every homeworkerid in the specified month.
_/_/_/ paramind _/_/_/ -
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 it all up in 1 query?!
Simon
CREATE TABLE [tbl_hp_bookings] (
[BookingID] [int] IDENTITY (2000, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DestinationID] [int] NOT NULL ,
[DestinationText] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[DepartureDate] [datetime] NOT NULL ,
[BookingDate] [datetime] NULL CONSTRAINT [DF_tbl_hp_bookings_BookingDate] DEFAULT (getdate()),
[Duration] [int] NULL ,
[Supplier] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[GiftTag] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_bookings_GiftTag] DEFAULT ('N'),
[Discount] [money] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_Discount] DEFAULT (0),
[TransactionCost] [money] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_TransactionCost] DEFAULT (0.00),
[HPCardFee] [money] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_HPCardFee] DEFAULT (0),
[ConfirmTag] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_bookings_ConfirmTag] DEFAULT ('N'),
[YTReference] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PaidTag] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_bookings_PaidTag] DEFAULT ('N'),
[CommissionRate] [numeric](18, 0) NOT NULL CONSTRAINT [DF_tbl_hp_bookings_CommissionRate] DEFAULT (25),
[HWContact] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_bookings_HWContact] DEFAULT ('N'),
[PaidDate] [datetime] NULL ,
[HomeworkerID] [int] NOT NULL ,
[QuoteID] [int] NULL CONSTRAINT [DF_tbl_hp_bookings_QuoteID] DEFAULT (0),
[EnquiryID] [int] NULL ,
[HWConfirmTag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_tbl_hp_bookings_HWConfirmTag] DEFAULT ('N'),
[ConfMailSent] [char] (1) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_tbl_hp_bookings_ConfMailSent] DEFAULT ('N'),
CONSTRAINT [PK_tbl_hp_bookings] PRIMARY KEY CLUSTERED
(
[BookingID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tbl_hp_bookings_transactions] (
[TransactionID] [int] IDENTITY (1000, 1) NOT NULL ,
[BookingID] [int] NOT NULL ,
[TransactionDesc] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[TransactionAmount] [money] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_transactions_TransactionAmount] DEFAULT (0),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_transactions_TransactionDate] DEFAULT (getdate()),
[TransactionTypeID] [int] NOT NULL ,
[HomeworkerID] [int] NOT NULL ,
[CommissionAmount] [money] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_transactions_CommissionAmount] DEFAULT (0),
[SupplierID] [int] NOT NULL CONSTRAINT [DF_tbl_hp_bookings_transactions_SupplierID] DEFAULT (0),
CONSTRAINT [PK_tbl_hp_bookings_transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply