patternsqlMinor
Counting number of occurences in a time period
Viewed 0 times
numbercountingperiodoccurencestime
Problem
I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time period?
For example, I have 5 cars. on car #1, I changed the spark plugs on the 21st and again on the 29th, meaning I changed them more than once in a given period (2 weeks for this example). car 3 had its plugs changed on the 1st and then on the 21st, but the previous month it had its plugs changed on the 1st and the 4th, so it shows up on the list.
I'm fairly certain this will have to be a stored procedure that compares each row, but I was hoping there would be a way to do it in plain-jane SQL without all the loops.
For example, I have 5 cars. on car #1, I changed the spark plugs on the 21st and again on the 29th, meaning I changed them more than once in a given period (2 weeks for this example). car 3 had its plugs changed on the 1st and then on the 21st, but the previous month it had its plugs changed on the 1st and the 4th, so it shows up on the list.
order_date machine_reference_number item_number quantity
-----------------------------------------------------------------
'2016-05-21' 1 21 1
'2016-05-29' 1 21 2
'2016-04-01' 3 13 4
'2016-04-04' 3 13 1
'2016-05-01' 3 13 4
'2016-05-21' 3 13 4I'm fairly certain this will have to be a stored procedure that compares each row, but I was hoping there would be a way to do it in plain-jane SQL without all the loops.
Solution
Edited based on your sample data.
For an item number to have a total quantity of two or more in a given date range:
For an item number to have two or more records in a given data range:
For an item number to have a total quantity of two or more in a given date range:
Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems
From Table
Where OrderDate Between DateRangeStart And DateRangeEnd
Group By MachineRefNo, ItemNumber
Having Sum(Quantity) > 1For an item number to have two or more records in a given data range:
Select MachineRefNo, ItemNumber, Count(*) NumberOfOccurances
From Table
Where OrderDate Between DateRangeStart And DateRangeEnd
Group By MachineRefNo, ItemNumber
Having Count(*) > 1Code Snippets
Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems
From Table
Where OrderDate Between DateRangeStart And DateRangeEnd
Group By MachineRefNo, ItemNumber
Having Sum(Quantity) > 1Select MachineRefNo, ItemNumber, Count(*) NumberOfOccurances
From Table
Where OrderDate Between DateRangeStart And DateRangeEnd
Group By MachineRefNo, ItemNumber
Having Count(*) > 1Context
StackExchange Database Administrators Q#137660, answer score: 4
Revisions (0)
No revisions yet.