SQL Code/view creation

I have a table with the following headings which records stock movements in and out which is called dbo.Stock_Record

ID Part_ID Qty_Required Date_Destocked Action Deleted

The Action field can be either “Delivery”, “Destock” or “Adjustment”, the deleted field is true or false, the date_destocked is a datetime field.


I have a second table which records stock counts at the month end called dbo.Stock_Count. The count_date is always the last day of each month
ID Part_ID Count_Date Stock_Count



I need a sql view which will calculate the stock total on the last day of each month as a sum of the Qty_required field called EOM_Stock. I then need this in a pivot table with the following totals as at the last day of each month, combining the data from the two tables. If there is no data recorded in a month then it needs to show the same EOM_Stock as the previous month and zero for the other fields. If there is no stock_count data then it needs to show zero

Part_ID Month_End_Date Delivery Destock Adjustment EOM_Stock Stock_Count