Counting Products

Transact-SQL
1

Hi there,

Once again I am in need of your help.
I have a table "Products" and in that table there are a number of fields but I need to filter using 3 of them.

  1. ProductCode 2. DateReceived 3. DateUsed
    When the products are entered into the Products table there can be a number of them all with the same ProductCode and DateReceived but DateUsed is Null. as the system is being used and stock being used a date is entered in DateUsed.
    I am using 2 date parameters Date1 & Date2.
    Using DateReceived, I need to list all products including, if it has the same ProductCode, a count.
    Using Date1 & Date2 I need to see the difference in stock from the 2 date parameters.
    So if Date1 = "10/08/2018" and Date2 = "20/08/2018" I am trying to see on the 10/08/2018 there are 23 x Hogan Beds and on the 20/08/2018 the were 14 x Hogan Beds.

I hope that makes some kind of sense.
Thanks for the help.

Best Regards,

Steve.

2

please provide sample data

create table #Products(ProductCode varchar(15), DateReceived date, DateUsed Date);
insert into #Products
select 'Hogan Beds', '2018-08-10', null union all
select 'Hogan Beds', '2018-08-10', null union all
select 'Hogan Beds', '2018-08-10', null union all
select 'Hogan Beds', '2018-08-10', null union all
select 'Hogan Beds', '2018-08-20' , null union all


select 'Chicken Beds', '2018-08-10', null union all
select 'Chicken Beds', '2018-08-10', null union all
select 'Chicken Beds', '2018-08-10', null union all
select 'Chicken Beds', '2018-08-10', null union all
select 'Chicken Beds', '2018-08-20' , null 

declare @Date1 date = '2018-08-10', @Date2 date = '2018-08-20'
select count1, d2.count2, d1.ProductCode
  From (
		  select count(1) as count1, ProductCode
			From #Products
			where DateReceived = @Date1
			group by ProductCode
		  ) d1
left join (
		  select count(1) as count2, ProductCode
		   From #Products 
		   where DateReceived = @Date2
		  group by ProductCode
)  d2 on d1.ProductCode = d2.ProductCode
 

drop table #Products