A
Amy
Hi,
The query below returns sales made in 2007 and 2006 and works fine. However,
if a client didn't have sells in 2006, the query returns empty fields (for
2007 as well).
Can anyone pls tell me why? I think the problem is with the WHERE statement
but cannot see it.
TIA
Amy
SELECT *
FROM (SELECT ROUND(SUM(CASE DATEPART(M, SELL_DATE) WHEN 1 THEN
PRICE ELSE 0 END), 0) AS Jan,
ROUND(SUM(PRICE), 0) AS TOT07,
CLIENT_ID, COUNT(PRICE) AS ORDERS07
FROM dbo.SELLS
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE))
= DATEPART(YYYY, GETDATE()))
GROUP BY CLIENT_ID) AS A INNER JOIN
(SELECT ROUND(SUM(PRICE), 0) AS TOT06,
COUNT(PRICE) AS ORDERS06, CLIENT_ID
FROM dbo.SELLS AS SELLS_1
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE)) = 2006)
GROUP BY CLIENT_ID) AS B ON A.CLIENT_ID =
B.CLIENT_ID
WHERE (A.CLIENT_ID = 100)
///The date format is yyyymmdd thus the datetime convertion
Access03, sql05
The query below returns sales made in 2007 and 2006 and works fine. However,
if a client didn't have sells in 2006, the query returns empty fields (for
2007 as well).
Can anyone pls tell me why? I think the problem is with the WHERE statement
but cannot see it.
TIA
Amy
SELECT *
FROM (SELECT ROUND(SUM(CASE DATEPART(M, SELL_DATE) WHEN 1 THEN
PRICE ELSE 0 END), 0) AS Jan,
ROUND(SUM(PRICE), 0) AS TOT07,
CLIENT_ID, COUNT(PRICE) AS ORDERS07
FROM dbo.SELLS
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE))
= DATEPART(YYYY, GETDATE()))
GROUP BY CLIENT_ID) AS A INNER JOIN
(SELECT ROUND(SUM(PRICE), 0) AS TOT06,
COUNT(PRICE) AS ORDERS06, CLIENT_ID
FROM dbo.SELLS AS SELLS_1
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE)) = 2006)
GROUP BY CLIENT_ID) AS B ON A.CLIENT_ID =
B.CLIENT_ID
WHERE (A.CLIENT_ID = 100)
///The date format is yyyymmdd thus the datetime convertion
Access03, sql05