Current demo: http://sqlfiddle.com/#!18/7acdc/17
I am looking for a total of how many items were added in a specific month but then a total of how many were never ordered after that.
Tables:
CREATE TABLE Item (
ItemNo varchar(10)
,DateAdded varchar(10)
);
CREATE TABLE Order1 (
OrderNo int,
ItemNo varchar(10),
OrderDate varchar(10)
);
INSERT INTO Item (ItemNo, DateAdded)
VALUES ('111', 'Jan-17'),
('222', 'Jan-17'),
('333', 'Jan-17'),
('444', 'Feb-17'),
('555', 'Feb-17'),
('666', 'Feb-17');
INSERT INTO Order1 (ItemNo, OrderDate)
VALUES ('111', 'Jan-17'),
('111', 'Feb-17'),
('222', 'May-17'),
('333', 'Jan-17'),
('333', 'March-17'),
('444', 'Jan-17');
Currently i have:
SELECT
-- b.OrderDate,
A.DateAdded,
COUNT(DISTINCT A.ItemNo) AS [Items Added],
COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END) as [Items Never Ordered]
FROM Item a
CROSS JOIN (SELECT DISTINCT OrderDate FROM Order1) b
LEFT JOIN Order1 c
ON a.ItemNo = c.ItemNo
AND b.OrderDate = c.OrderDate
GROUP BY A.DateAdded
Which produces:
| DateAdded | Items Added | Items Never Ordered |
|-----------|-------------|---------------------|
| Feb-17 | 3 | 11 |
| Jan-17 | 3 | 7 |
But i am looking for a result set such as:
| DateAdded | Items Added | Items Never Ordered |
|-----------|-------------|---------------------|
| Feb-17 | 3 | 2 |
| Jan-17 | 3 | 0 |
I am struggling to get this working. Do i need a sub-query or something to match the items individually. Can anyone push me in the right direction? Thanks