We help IT Professionals succeed at work.

Query to List of non duplicate values by date

johnnyg123
johnnyg123 asked
on
9 Comments
1 Solution
104 Views
Last Modified: 2018-09-07
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 ???
Comment
Watch Question

View Solution Only

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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

johnnyg123

Author

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
johnnyg123

Author

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

Author

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
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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

jppampin

Commented:
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
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
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
Snr. Development Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnnyg123

Author

Commented:
Thanks All!