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')
Trying to write a query to get of unique order ids by monthCREATE 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 |
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
Open in new window