Sql-server – Counting number of occurences in a time period

sql server

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.

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              4

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.

Best Answer

Edited based on your sample data.

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) > 1

For 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(*) > 1

Related Solutions

Sql-server – SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert

I made a couple comments to this effect, but I'm not sure you're getting the desired results when you combine Repeatable Read transaction isolation level with Read Committed Snapshot.

The TIL reported in your deadlock list is repeatable read, which is even more restrictive than Read Committed, and given the flow you describe, is likely leading to deadlocks.

What you might be trying to do is to have your DB TIL remain repeatable read, but set the transaction to use the snapshot TIL explicitly with a set transaction isolation level statement. Reference: https://msdn.microsoft.com/en-us/library/ms173763.aspx If so, I think you must have something incorrect. I'm not familiar with nHibernate, but it looks like there's a reference here: http://www.anujvarma.com/fluent-nhibernate-setting-database-transaction-isolation-level/

If your app's architecture will allow it, an option would be to try read committed snapshot at the db level, and if you still get deadlocks, enable snapshot with row versioning. NOTE that, if you do this, you need to re-think your tempdb setup if you enable snapshot (row versioning). I can get you all sorts of material on this if you need it - let me know.

Sql-server – Writing and reading date ranges for a million items

It looks like you're on the right path. Your datebit pattern idea is an interesting option, I'd like to hear if you make any more progress towards that design.

My suggestion is a pretty simple one. Why not split the ItemOfType out to their own tables and reference them with a surrogate id?. I know it isn't always a popular idea if you have good natural keys available, but in terms of space saving it could reduce your storage requirement a bit.

This was too much for a comment, please don't crucify me.

create table dbo.ItemOfType1 (
    ItemOfType1Id int identity(1,1) not null
  , ItemOfType1Code char(12) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType1 primary key clustered (ItemOfType1Id)
  , constraint uq_ItemOfType1_ItemOfType1Code_ReferenceType unique nonclustered (ItemOfType1Code, ReferenceType)
);
create table dbo.ItemOfType2 (
    ItemOfType2Id int identity(1,1) not null
  , ItemOfType2Code char(7) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType2 primary key clustered (ItemOfType2Id)
  , constraint uq_ItemOfType2_ItemOfType2Code_ReferenceType unique nonclustered (ItemOfType2Code, ReferenceType)
);
create table dbo.ItemOfType1Dates (
    ItemOfType1Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType1Dates primary key clustered (ReferenceDate,ItemOfType1Id)
  , constraint fk_ItemOfType1Dates_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2Dates (
    ItemOfType2Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType2Dates primary key clustered (ReferenceDate,ItemOfType2Id)
  , constraint fk_ItemOfType2Dates_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);
/*  -- date ranges alternate
create table dbo.ItemOfType1DateRanges (
    ItemOfType1Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType1DateRanges primary key clustered (FromBusinessDate,ItemOfType1Id)
  , constraint fk_ItemOfType1DateRanges_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2DateRanges (
    ItemOfType2Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType2DateRanges primary key clustered (FromBusinessDate,ItemOfType2Id)
  , constraint fk_ItemOfType2DateRanges_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);    
*/

Best Answer

Related Solutions

Sql-server – SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert

Sql-server – Writing and reading date ranges for a million items

Related Question