Query to List of non duplicate values by date

Depends a bit on what you do with the year, here are 2 to try

select 
  [month], count(distinct itemid)
from orderinfo
group by
  [month]

Select all Open in new window

select 
  year(orderdate), [month], count(distinct itemid)
from orderinfo
group by
  year(orderdate), [month]

Select all Open in new window

Thanks so much for the response!

I reread my question and I don't think I was as clear as I should have been

I was hoping the query results would be

March 3
April 1


Here's why


OrderDate      Month      itemid
2018-03-05      March      110
2018-03-05      March      112
2018-03-06      March      112
2018-03-06      March      113
2018-03-07      March      113
2018-04-05      April      110
2018-04-05      April      112
2018-04-06      April      112
2018-04-06      April      115
2018-04-07      April      113

The unique item id's for march are 110,112,113

Therefore the entry for march I am looking for is

march   3


The unique item id's for march are 110,112,113, 115

However,  item id's  110,112,113 were already found in march so the only unique item id for march and april is 115

Therefore  the entry I was hoping to see for April would be

April  1

Like wise if may had Item ids of 110, 116, 116,117, 118 for days in may

I would look for an entry for may to be

May  3

since 110 did exist in march and april but 116, 117,118 does not exist in march or april and 116 exists twice in may so would only want to count that once.  117 and 118 only exist once for may

Hopefully this make sense

Thanks so much

forgot to mention that I am only looking for year 2018 so not worried about year

guess I shouldn't try and type after midnight :-)

just noticed in my comment that I said  

The unique item id's for march are 110,112,113, 115

I meant

The unique item id's for april are 110,112,113, 115

couldn't find way to edit my comment

Ah, ok I see there is a difference from the original question. For the updated question I suggest using row_number() over() so for each first reference to a new itemid [rn] will be equal to 1, so all you then have to do it count the number of  1's in each month.

CREATE TABLE Table1    (OrderDate timestamp, Month varchar(5), itemid int)
;
    
INSERT INTO Table1     (OrderDate, Month, itemid) VALUES
    ('2018-03-05 00:00:00', 'March', 110),    ('2018-03-05 00:00:00', 'March', 112),    ('2018-03-06 00:00:00', 'March', 112),
    ('2018-03-06 00:00:00', 'March', 113),    ('2018-03-07 00:00:00', 'March', 113),    ('2018-04-05 00:00:00', 'April', 110),
    ('2018-04-05 00:00:00', 'April', 112),    ('2018-04-06 00:00:00', 'April', 112),    ('2018-04-06 00:00:00', 'April', 115),
    ('2018-04-07 00:00:00', 'April', 113)
;

select
    month, count(rn)
from (
      select
      month, itemid, row_number() over(partition by itemid order by orderdate) rn
      from table1
      ) derived
where rn = 1
group by month
;

| month | count |
|-------|-------|
| April |     1 |
| March |     3 |

Select all Open in new window

This is my solution, it could be improved but you are going to achive your goal

CREATE TABLE #Data (
	OrderDate DATETIME,
	OrderMonth VARCHAR(255),
	ItemId INTEGER
)

CREATE TABLE #Result (
  [Month] VARCHAR(255),
  NewOrders INTEGER
)

CREATE TABLE #UsedOrders(
  ItemId INTEGER
)

INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-03-05', 'March', 110)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-03-05', 'March', 112)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-03-06', 'March', 112)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-03-06', 'March', 113)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-03-07', 'March', 113)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-04-05', 'April', 110)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-04-05', 'April', 112)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-04-06', 'April', 112)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-04-06', 'April', 115)
INSERT INTO #Data(OrderDate,  OrderMonth,  ItemId) VALUES ('2018-04-07', 'April', 113)


SELECT 
CAST(
 CAST(YEAR(MIN(OrderDate)) AS VARCHAR) + '-'  + CAST(MONTH(MIN(OrderDate)) AS VARCHAR) + '-01' AS DATETIME) AS DateNormalized
, MIN(OrderMonth) AS OrderMonth 
, ItemId
INTO #DataGrouped
FROM #Data
GROUP BY OrderMonth, ItemId

DECLARE @LastDate DATETIME
SELECT @LastDate = MIN(DateNormalized) FROM #DataGrouped

INSERT INTO #Result
SELECT MIN(OrderMonth) AS [Month], COUNT(1) AS NewOrders 
FROM #DataGrouped d
LEFT JOIN #UsedOrders uo
ON d.ItemId = uo.ItemId
WHERE DateNormalized = @LastDate
AND uo.ItemId IS NULL
 

INSERT INTO #UsedOrders
SELECT DISTINCT d.ItemId  
FROM #DataGrouped d
LEFT JOIN #UsedOrders uo
ON d.ItemId = uo.ItemId
WHERE DateNormalized = @LastDate
AND uo.ItemId IS NULL


DELETE FROM #DataGrouped WHERE DateNormalized = @LastDate


WHILE((SELECT COUNT(1) FROM #DataGrouped) > 0)
BEGIN
SELECT @LastDate = MIN(DateNormalized) FROM #DataGrouped

INSERT INTO #Result
SELECT MIN(OrderMonth) AS [Month], COUNT(1) AS NewOrders 
FROM #DataGrouped d
LEFT JOIN #UsedOrders uo
ON d.ItemId = uo.ItemId
WHERE DateNormalized = @LastDate
AND uo.ItemId IS NULL
 

INSERT INTO #UsedOrders
SELECT DISTINCT d.ItemId  
FROM #DataGrouped d
LEFT JOIN #UsedOrders uo
ON d.ItemId = uo.ItemId
WHERE DateNormalized = @LastDate
AND uo.ItemId IS NULL

DELETE FROM #DataGrouped WHERE DateNormalized = @LastDate
END

Select all Open in new window

Best Regards,
JPP

Try this query


Select ordermonth, count(itemid) items from
(SELECT itemid, DATENAME(month,  orderdate) ordermonth from orderinfo
group by itemid
) x
where year(ordermonth)=2018
group by ordermonth

Thanks All!