Re: Didn't know how to name it :(
Quote:
Originally Posted by
selanec
Basically i want to returns all orders no matter if there is Referrer or NO.
So use LEFT JOIN instead of INNER JOIN.
Re: Didn't know how to name it :(
Yeah i just recalled that you advised me the same some time ago. I have asked very similar question and you also told me to use LEFT JOIN. But, i have forgotten it :( unfortunately.
Thank you again. It is much appreciated!
Re: [RESOLVED] Didn't know how to name it :(
Hmm it seems that this time i celebrated too soon :(
Code:
CREATE PROCEDURE SalesReport
(
@Store int,
@StartDate datetime,
@EndDate datetime,
@Domain varchar(1024)
)
AS
SET NOCOUNT ON
DECLARE @IPAddress varchar(20);
SELECT
Ord.order_ID, Ord.DateCreated, Ord.DateShipped,
Ord.OrderTotal, Ord.Subtotall, Ord.StoreStatus,
Ord.StoreProfit, Ord.OrderStatus, Ref.UrlReferrer AS Referrer
FROM
customer_Orders AS Ord
LEFT OUTER JOIN
customer_TrafficStatistics AS Ref
ON
Ord.IPAddress = Ref.IPAddress
WHERE
Ord.StoreOwner = @Store
AND
Ord.OrderCurrentStatus <> 4
AND
(Ord.StoreStatus <> 3 AND Ord.StoreStatus <> 6)
AND
(Ord.DateShipped >= @StartDate AND Ord.DateShipped <= @EndDate)
AND
Ord.StoreOwner = @Store
AND
Ref.UrlReferrer <> @Domain
AND
DATEPART(day, Ord.DateCreated) = DATEPART(day, Ref.EnterDate)
If you throw out the last two conditions it works like a charm but AS IS it does not return anything. return 0 :(
Any ideas? Thanks