Issue w/ Unmatch query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Issue w/ Unmatch query

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-09-27 : 15:30:09

I have 2 tables [tbl.Product] and [tbl.ProductPricing]. Each week, I need to make sure I create a new record for each product in the product pricing table.

I have a query which will find the unmatched products if at least one record exists in the product pricing table. I have the following query which provides the properly until I add additional where clause criteria. Any idea what is causing this?

Start Stored Procedure
Declare @StartDate datetime
Declare @EndDate datetime

--Get Monday's date
SELECT @StartDate = DATEADD(day, 2-DATEPART(dw, GETDATE()), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

--Get Sunday's Date
SELECT @EndDate = DATEADD(day, 8-DATEPART(dw, GETDATE()), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

SELECT
p.product_id, p.product_name, p.product_active

FROM
tbl_product p
INNER JOIN
tbl_productpricing pp ON p.product_id <> pp.product_id

WHERE
(p.product_active = 1)

End Stored Procedure

If I add additional items to my WHERE clause, I get nothing in return.
Additional WHERE Clause Items
AND (pp.price_date >= @StartDate)
AND (pp.price_date <= @EndDate)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 15:40:18

Add PRINT @StartDate and PRINT @EndDate after they are set. Make sure they are correct. And this might be a stupid question, but are you sure you have data that meets the WHERE clause criteria?

Tara

davidliv
Starting Member

45 Posts

Posted - 2004-09-27 : 15:44:57

Thanks for the reply Tara. The @StartDate and @EndDate query is works properly. Initially at the beginning of the week, there will be no product records in the ProductPricing table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 15:47:04

So you shouldn't get anything in return then when are no product records matching the WHERE clause criteria. I'm confused.

Tara

davidliv
Starting Member

45 Posts

Posted - 2004-09-27 : 15:50:04

Okay. Let me try to re-explain my issue. At the beginning of the week, I need to query my ProductPricing table and return a list of Products which do not have records in the ProductPricing table.

David

davidliv
Starting Member

45 Posts

Posted - 2004-09-27 : 15:55:26

I got it to work. Moved my criteria to the JOIN.

Declare @StartDate datetime
Declare @EndDate datetime

SELECT @StartDate = DATEADD(day, 2-DATEPART(dw, GETDATE()), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
SELECT @EndDate = DATEADD(day, 8-DATEPART(dw, GETDATE()), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

SELECT
p.product_id, p.product_name, p.product_active

FROM
tbl_product p
LEFT OUTER JOIN
tbl_productpricing pp ON p.product_id = pp.product_id AND (pp.price_date >= @StartDate) AND (pp.price_date <= @EndDate)

WHERE
(pp.product_id is NULL) AND (p.product_active = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources