Query to List of non duplicate values by date

I have a sample table with the following data

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


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-03-05',	'March',	'110')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-03-05',	'March',	'112')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-03-06',	'March',	'112')

INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-03-06',	'March',	'113')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-03-07',	'March',	'113')



-----------------------------------------------


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-04-05',	'April',	'110')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-04-05',	'April',	'112')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-04-06',	'April',	'112')

INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-04-06',	'April',	'115')


INSERT INTO [dbo].[Orderinfo]
           ([OrderDate]
           ,[Month]
           ,[itemid])
     VALUES
        	
('2018-04-07',	'April',	'113')

Open in new window

Trying to write a query to get of unique order ids by month

Definition of unique order id is that it does not appear in any day of month

given sample data

I am looking for the following output

Month    items

march      3
April          1

Ideas ???
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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]

Open in new window


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

Open in new window

0
johnnyg123Author Commented:
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
0
johnnyg123Author Commented:
forgot to mention that I am only looking for year 2018 so not worried about year
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

johnnyg123Author Commented:
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
0
PortletPaulfreelancerCommented:
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 |

Open in new window

0
jppampinCommented:
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

Open in new window


Best Regards,
JPP
0
G Trurab KhanSnr. Development ManagerCommented:
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
0
G Trurab KhanSnr. Development ManagerCommented:
Sorry the above code needs correction. Try this

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
Thanks All!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.