declare @Start12Date datetime; declare @End12Date datetime; declare @Start24Date datetime; declare @End24Date datetime; declare @Start36Date datetime; declare @End36Date datetime; declare @OnHandDate datetime; --declare @END_DATE datetime = '09/30/2023 23:59:59'; SET @End12Date = @END_DATE select @End12Date=DateAdd(ss, -1, Convert(DateTime, DateAdd(DAY,1,EOMONTH(@End12Date, 0)))) select @Start12Date=DateAdd(DAY,1,EOMONTH(DateAdd(m, -12, @End12Date),0)) select @Start24Date=DateAdd(DAY,1,EOMONTH(DateAdd(m, -24, @End12Date),0)) select @End24Date=EOMONTH(DateAdd(m, -12, @End12Date),0) select @Start36Date=DateAdd(DAY,1,EOMONTH(DateAdd(m, -36, @End12Date),0)) select @End36Date=EOMONTH(DateAdd(m, -24, @End12Date),0) select @OnHandDate = convert(date, DATEADD(d, 1, EOMONTH(@End12Date))) declare @COMPANY_ID VARCHAR(8) = '10'; declare @START datetime = '1/1/1900 00:00:00'; declare @END datetime = '12/31/2200 23:59:59'; declare @START_INVOICE_NO varchar(10) = ' '; declare @END_INVOICE_NO varchar(10) = 'ZZZZZZZZZZ'; --declare @START_ITEM_ID varchar(40) = '8N50SMT0B'; declare @START_ITEM_ID varchar(40) = ' '; --declare @END_ITEM_ID varchar(40) = '8N50STS'; declare @END_ITEM_ID varchar(40) = 'ZZZZZZZZZZZZZZZZZZZZZZ'; declare @START_LOCATION_ID decimal(19,0) = 0; declare @END_LOCATION_ID decimal(19,0) = 9999999; declare @START_CUSTOMER_ID decimal(19,0) = 0; declare @END_CUSTOMER_ID decimal(19,0) = 9999999999; declare @START_PO_NO decimal(19,0) = 0; declare @END_PO_NO decimal(19,0) = 9999999999; declare @START_RECEIPT_NUMBER decimal(19,0) = 0; declare @END_RECEIPT_NUMBER decimal(19,0) = 9999999999; select @OnHandDate as OnHandDate, @Start12Date as Start12Date, @End12Date as End12Date, @Start24Date as Start24Date, @End24Date as End24Date, @Start36Date as Start36Date, @End36Date as End36Date, inv_mast.item_id, inv_mast.item_desc, product_group.product_group_desc, inv_mast.purchasing_weight, ISNULL(sales.[sales_total_qty_last_12_mo],0) sales_total_qty_last_12_mo, ISNULL(sales.[sales_total_qty_last_24_mo],0) sales_total_qty_last_24_mo, ISNULL(sales.[sales_total_qty_last_36_mo],0) sales_total_qty_last_36_mo, ISNULL(sales.[sales_total_last_12_mo],0) sales_total_last_12_mo, ISNULL(sales.[sales_total_last_24_mo],0) sales_total_last_24_mo, ISNULL(sales.[sales_total_last_36_mo],0) sales_total_last_36_mo, ISNULL(sales.[sales_total_cogs_last_12_mo],0) sales_total_cogs_last_12_mo, ISNULL(sales.[sales_total_cogs_last_24_mo],0) sales_total_cogs_last_24_mo, ISNULL(sales.[sales_total_cogs_last_36_mo],0) sales_total_cogs_last_36_mo, ISNULL((sales.[sales_total_last_12_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_12_mo],0), 1)),0) as [avg_unit_price_last_12_mo], ISNULL((sales.[sales_total_last_24_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_24_mo],0), 1)),0) as [avg_unit_price_last_24_mo], ISNULL((sales.[sales_total_last_36_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_36_mo],0), 1)),0) as [avg_unit_price_last_36_mo], ISNULL((sales.[sales_total_cogs_last_12_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_12_mo],0), 1)),0) as [avg_sales_cost_last_12_mo], ISNULL((sales.[sales_total_cogs_last_24_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_24_mo],0), 1)),0) as [avg_sales_cost_last_24_mo], ISNULL((sales.[sales_total_cogs_last_36_mo]/ISNULL(NULLIF(sales.[sales_total_qty_last_36_mo],0), 1)),0) as [avg_sales_cost_last_36_mo], sales.first_ship_date, sales.last_ship_date, ISNULL(purchase.[qty_vouched_last_12_mo],0) qty_vouched_last_12_mo, ISNULL(purchase.[qty_vouched_last_24_mo],0) qty_vouched_last_24_mo, ISNULL(purchase.[qty_vouched_last_36_mo],0) qty_vouched_last_36_mo, ISNULL(purchase.[qty_received_last_12_mo],0) qty_received_last_12_mo, ISNULL(purchase.[qty_received_last_24_mo],0) qty_received_last_24_mo, ISNULL(purchase.[qty_received_last_36_mo],0) qty_received_last_36_mo, ISNULL((purchase.[vouched_unit_cost_last_12_mo]/ISNULL(NULLIF(qty_vouched_last_12_mo,0), 1)),0) avg_vouched_unit_cost_last_12_mo, ISNULL((purchase.[vouched_unit_cost_last_24_mo]/ISNULL(NULLIF(qty_vouched_last_24_mo,0), 1)),0) avg_vouched_unit_cost_last_24_mo, ISNULL((purchase.[vouched_unit_cost_last_36_mo]/ISNULL(NULLIF(qty_vouched_last_36_mo,0), 1)),0) avg_vouched_unit_cost_last_36_mo, ISNULL((purchase.received_unit_cost_last_12_mo/ISNULL(NULLIF(qty_received_last_12_mo,0), 1)),0) avg_received_unit_cost_last_12_mo, ISNULL((purchase.received_unit_cost_last_24_mo/ISNULL(NULLIF(qty_received_last_24_mo,0), 1)),0) avg_received_unit_cost_last_24_mo, ISNULL((purchase.received_unit_cost_last_36_mo/ISNULL(NULLIF(qty_received_last_36_mo,0), 1)),0) avg_received_unit_cost_last_36_mo, purchase.first_receipt_date, purchase.last_receipt_date, ISNULL(usage.[qty_usage_12_mo_ago],0) qty_usage_12_mo_ago, ISNULL(usage.[qty_usage_24_mo_ago],0) qty_usage_24_mo_ago, ISNULL(usage.[qty_usage_36_mo_ago],0) qty_usage_36_mo_ago, on_hand.qty_on_hand, on_hand.extended_on_hand_cost, (on_hand.extended_on_hand_cost/ISNULL(NULLIF(on_hand.qty_on_hand,0), 1)) on_hand_unit_cost, CASE WHEN datediff(m,inv_mast.date_created,getdate()) < 12 THEN 'Y' ELSE '' END AS inv_mast_date_less_year from inv_mast WITH (NOLOCK) left join product_group WITH (NOLOCK) on inv_mast.default_product_group = product_group.product_group_id AND product_group.company_id = @COMPANY_ID left join (select invoicedetail.inv_mast_uid, invoicedetail.item_id, item_desc, sum(case when invoicedetail.invoice_date >= @Start12Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.qty_shipped else 0 end) as [sales_total_qty_last_12_mo], sum(case when invoicedetail.invoice_date >= @Start24Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.qty_shipped else 0 end) as [sales_total_qty_last_24_mo], sum(case when invoicedetail.invoice_date >= @Start36Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.qty_shipped else 0 end) as [sales_total_qty_last_36_mo], sum(case when invoicedetail.invoice_date >= @Start12Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.extended_price else 0 end) as [sales_total_last_12_mo], sum(case when invoicedetail.invoice_date >= @Start24Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.extended_price else 0 end) as [sales_total_last_24_mo], sum(case when invoicedetail.invoice_date >= @Start36Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.extended_price else 0 end) as [sales_total_last_36_mo], sum(case when invoicedetail.invoice_date >= @Start12Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.total_cogs_amount else 0 end) as [sales_total_cogs_last_12_mo], sum(case when invoicedetail.invoice_date >= @Start24Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.total_cogs_amount else 0 end) as [sales_total_cogs_last_24_mo], sum(case when invoicedetail.invoice_date >= @Start36Date AND invoicedetail.invoice_date <= @End12Date then invoicedetail.total_cogs_amount else 0 end) as [sales_total_cogs_last_36_mo], min(invoicedetail.first_sales_date) first_ship_date, max(invoicedetail.last_sales_date) last_ship_date from (SELECT inv_mast.item_id, inv_mast.item_desc, invoice_hdr.sales_location_id, invoice_line.invoice_no, CAST(COALESCE(invoice_line.qty_shipped / coalesce(invoice_line.sales_unit_size,item_uom.unit_size,1), 0) AS Decimal(19,9)) qty_shipped, COALESCE(invoice_line.unit_price, 0) unit_price, invoice_line.extended_price, COALESCE(invoice_line.cogs_amount / NULLIF(invoice_line.qty_shipped, 0), 0) cogs_amount, invoice_line.order_no, invoice_line.customer_part_number, invoice_line.unit_of_measure, invoice_line.pricing_unit, invoice_line.pricing_unit_size, invoice_hdr.order_date, invoice_hdr.invoice_date, invoice_hdr.customer_id, invoice_hdr.ship2_name, invoice_hdr.po_no, invoice_hdr.ship_to_id, invoice_hdr.ship_date, customer.customer_name, job_price_hdr.contract_no, currency_line.exchange_rate, invoice_line.oe_line_number, job_price_line.job_price_hdr_uid, invoice_line.commission_cost, invoice_line.other_cost, 0 cost_basis, invoice_line.inv_mast_uid, invoice_line.sales_unit_size, CASE invoice_hdr.record_type_cd WHEN 2594 THEN invoice_hdr.record_type_reference_no ELSE '' END record_type_reference_no, CASE COALESCE( (SELECT value FROM system_setting WHERE name = 'enable_gallons_display'), 'N') WHEN 'N' THEN 0 ELSE CASE COALESCE(item_uom_gallon.unit_size,0) WHEN 0 THEN 0 ELSE ((invoice_line.qty_shipped ) / item_uom_gallon.unit_size) END END gallons_shipped, code_p21_line_type.code_description 'invoice_line_type_desc', invoice_line.invoice_line_type, CASE WHEN invoice_line.invoice_line_type = 981 AND invoice_line.unit_price = 0 THEN 0 ELSE invoice_line.cogs_amount END 'total_cogs_amount', CASE WHEN invoice_line.invoice_line_type = 981 AND invoice_line.unit_price = 0 THEN 0 ELSE (invoice_line.commission_cost * invoice_line.qty_shipped) END 'total_commission_cost' , CASE WHEN invoice_line.invoice_line_type = 981 AND invoice_line.unit_price = 0 THEN 0 ELSE (invoice_line.other_cost * invoice_line.qty_shipped) END 'total_other_cost', first_sales_date, last_sales_date FROM invoice_line WITH (NOLOCK) INNER JOIN invoice_hdr WITH (NOLOCK) ON invoice_hdr.invoice_no = invoice_line.invoice_no INNER JOIN inv_mast WITH (NOLOCK) ON invoice_line.inv_mast_uid = inv_mast.inv_mast_uid LEFT JOIN currency_line WITH (NOLOCK) ON currency_line.currency_line_uid = invoice_hdr.currency_line_uid INNER JOIN customer WITH (NOLOCK) ON customer.customer_id = invoice_hdr.customer_id AND customer.company_id = invoice_hdr.company_no LEFT JOIN job_price_line WITH (NOLOCK) ON job_price_line.job_price_line_uid = invoice_line.job_price_line_uid LEFT JOIN job_price_hdr WITH (NOLOCK) ON job_price_hdr.job_price_hdr_uid = job_price_line.job_price_hdr_uid LEFT JOIN item_uom WITH (NOLOCK) ON item_uom.inv_mast_uid = invoice_line.inv_mast_uid and item_uom.unit_of_measure = invoice_line.unit_of_measure LEFT Join system_setting system_setting_Gallon WITH (NOLOCK) ON system_setting_Gallon.configuration_id = 0 AND system_setting_Gallon.name = 'Gallon_UOM' LEFT JOIN item_uom item_uom_gallon WITH (NOLOCK) ON item_uom_gallon.inv_mast_uid = invoice_line.inv_mast_uid AND item_uom_gallon.unit_of_measure = system_setting_gallon.value LEFT OUTER JOIN oe_hdr ON invoice_hdr.order_no = oe_hdr.order_no LEFT OUTER JOIN oe_line ON invoice_line.oe_line_number = oe_line.line_no AND invoice_line.order_no = oe_line.order_no LEFT JOIN code_p21 AS code_p21_line_type WITH (NOLOCK) ON code_p21_line_type.code_no = invoice_line.invoice_line_type LEFT JOIN (select p21_view_invoice_line.inv_mast_uid, max(invoice_hdr.invoice_date) last_sales_date, min(invoice_hdr.invoice_date) first_sales_date from invoice_hdr left join p21_view_invoice_line on invoice_hdr.invoice_no = p21_view_invoice_line.invoice_no where invoice_hdr.approved = 'Y' -- AND p21_view_invoice_line.item_id >= @START_ITEM_ID -- AND p21_view_invoice_line.item_id <= @END_ITEM_ID AND invoice_hdr.invoice_class <> 'FINANCE' AND invoice_hdr.invoice_adjustment_type NOT IN ('B', 'T', 'X', 'R', 'W', 'A', 'P', 'Z', 'Y') AND invoice_hdr.consolidated <> 'C' AND p21_view_invoice_line.tax_item = 'N' AND p21_view_invoice_line.invoice_line_type NOT IN (928, 929, 930, 3) AND invoice_hdr.original_document_type <> 'T' AND p21_view_invoice_line.item_id NOT IN ('DOWNPAYMENT','PREPAYMENT') -- AND invoice_hdr_salesrep.primary_salesrep = 'Y' AND (p21_view_invoice_line.qty_shipped > 0.0000 OR p21_view_invoice_line.qty_shipped < 0.0000 -- JAB - 11/26/12 - Scopus 1072443 - Include line when hours_worked is on a service order line -- GGR - 11/06/13 - Scopus 1180138 - Include line when extended_price is on a service order line OR (p21_view_invoice_line.invoice_line_type = 1577 AND (COALESCE(p21_view_invoice_line.hours_worked, 0) <> 0 OR p21_view_invoice_line.extended_price <> 0))) AND p21_view_invoice_line.item_id <> 'ADVANCE BILL AMOUNT' -- AND ((COALESCE(oe_line.lot_bill,'N') = 'N') -- OR (oe_line.lot_bill = 'Y' -- AND p21_view_invoice_line.invoice_line_uid_parent = 0 -- AND p21_view_invoice_line.invoice_line_type = 4)) group by p21_view_invoice_line.inv_mast_uid) as sales_date on inv_mast.inv_mast_uid = sales_date.inv_mast_uid WHERE invoice_hdr.approved = 'Y' AND invoice_hdr.invoice_class <> 'FINANCE' AND invoice_hdr.invoice_adjustment_type NOT IN ('B', 'T', 'X', 'R', 'W', 'A', 'P', 'Z', 'Y') AND invoice_hdr.consolidated <> 'C' AND invoice_line.tax_item = 'N' AND invoice_line.invoice_line_type NOT IN (928, 929, 930, 3) AND invoice_hdr.original_document_type <> 'T' AND invoice_line.item_id NOT IN ('DOWNPAYMENT','PREPAYMENT') AND -- invoice_hdr_salesrep.primary_salesrep = 'Y' AND -- (invoice_line.qty_shipped > 0.0000 OR -- invoice_line.qty_shipped < 0.0000 OR -- (invoice_line.invoice_line_type = 1577 AND (COALESCE(invoice_line.hours_worked, 0) <> 0 OR invoice_line.extended_price <> 0))) AND -- invoice_line.item_id <> 'ADVANCE BILL AMOUNT' AND -- ((COALESCE(oe_line.lot_bill,'N') = 'N') OR (oe_line.lot_bill = 'Y' AND invoice_line.invoice_line_uid_parent = 0 AND invoice_line.invoice_line_type = 4)) AND invoice_hdr.ship_date >= @Start36Date AND invoice_hdr.ship_date <= @End12Date AND invoice_hdr.invoice_no >= @START_INVOICE_NO AND invoice_hdr.invoice_no <= @END_INVOICE_NO AND customer.customer_id >= @START_CUSTOMER_ID AND customer.customer_id <= @END_CUSTOMER_ID AND invoice_hdr.sales_location_id >= @START_LOCATION_ID AND invoice_hdr.sales_location_id <= @END_LOCATION_ID) as invoicedetail group by invoicedetail.inv_mast_uid, invoicedetail.item_id, invoicedetail.item_desc) as sales on inv_mast.inv_mast_uid = sales.inv_mast_uid left join (select inv_mast_uid, min(first_receipt_date) first_receipt_date, max(last_receipt_date) last_receipt_date, sum(case when purchase_report.inv_receipts_date_created >= @Start12Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_received else 0 end) as [qty_received_last_12_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start24Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_received else 0 end) as [qty_received_last_24_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start36Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_received else 0 end) as [qty_received_last_36_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start12Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_vouched else 0 end) as [qty_vouched_last_12_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start24Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_vouched else 0 end) as [qty_vouched_last_24_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start36Date AND purchase_report.inv_receipts_date_created <= @End12Date then inv_receipts_line_qty_vouched else 0 end) as [qty_vouched_last_36_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start12Date AND purchase_report.inv_receipts_date_created <= @End12Date then (inv_receipts_line_unit_cost*inv_receipts_line_qty_received) else 0 end) as [received_unit_cost_last_12_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start24Date AND purchase_report.inv_receipts_date_created <= @End12Date then (inv_receipts_line_unit_cost*inv_receipts_line_qty_received) else 0 end) as [received_unit_cost_last_24_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start36Date AND purchase_report.inv_receipts_date_created <= @End12Date then (inv_receipts_line_unit_cost*inv_receipts_line_qty_received) else 0 end) as [received_unit_cost_last_36_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start12Date AND purchase_report.inv_receipts_date_created <= @End12Date then (vouched_unit_cost*inv_receipts_line_qty_vouched) else 0 end) as [vouched_unit_cost_last_12_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start24Date AND purchase_report.inv_receipts_date_created <= @End12Date then (vouched_unit_cost*inv_receipts_line_qty_vouched) else 0 end) as [vouched_unit_cost_last_24_mo], sum(case when purchase_report.inv_receipts_date_created >= @Start36Date AND purchase_report.inv_receipts_date_created <= @End12Date then (vouched_unit_cost*inv_receipts_line_qty_vouched) else 0 end) as [vouched_unit_cost_last_36_mo] from nutech_fn_inventory_received_report(@COMPANY_ID, @Start36Date, @End12Date, @START, @END, @START_ITEM_ID, @END_ITEM_ID, @START_PO_NO, @END_PO_NO, @START_LOCATION_ID, @END_LOCATION_ID, @START_RECEIPT_NUMBER, @END_RECEIPT_NUMBER, 0, 999999999999) as purchase_report group by inv_mast_uid) as purchase on inv_mast.inv_mast_uid = purchase.inv_mast_uid left join (select nh_inv_loc.inv_mast_uid, sum(ISNULL(nh_inv_loc.qty_on_hand,0)) qty_on_hand, sum(ISNULL(nh_inv_loc.qty_on_hand*nh_inv_loc.moving_average_cost,0)) extended_on_hand_cost from msbs.dbo.nutech_historical_inv_loc nh_inv_loc where convert(date, nh_inv_loc.date_created) = convert(date, @OnHandDate) group by nh_inv_loc.inv_mast_uid) as on_hand on inv_mast.inv_mast_uid = on_hand.inv_mast_uid left join (select inv_period_usage.inv_mast_uid, sum(case when demand_period.beginning_date >= @Start12Date AND demand_period.beginning_date <= @End12Date then (inv_period_usage.inv_period_usage+inv_period_usage.scheduled_usage) else 0 end) as [qty_usage_12_mo_ago], sum(case when demand_period.beginning_date >= @Start24Date AND demand_period.beginning_date <= @End12Date then (inv_period_usage.inv_period_usage+inv_period_usage.scheduled_usage) else 0 end) as [qty_usage_24_mo_ago], sum(case when demand_period.beginning_date >= @Start36Date AND demand_period.beginning_date <= @End12Date then (inv_period_usage.inv_period_usage+inv_period_usage.scheduled_usage) else 0 end) as [qty_usage_36_mo_ago] from inv_period_usage WITH (NOLOCK) left join demand_period WITH (NOLOCK) on inv_period_usage.demand_period_uid = demand_period.demand_period_uid where demand_period.beginning_date >= @Start36Date and demand_period.ending_date <= @End12Date group by inv_period_usage.inv_mast_uid) as usage on inv_mast.inv_mast_uid = usage.inv_mast_uid where inv_mast.delete_flag = 'N' AND NOT(on_hand.qty_on_hand = 0 AND sales.[sales_total_qty_last_12_mo] = 0 AND sales.[sales_total_qty_last_24_mo] = 0 AND sales.[sales_total_qty_last_36_mo] = 0 AND purchase.[qty_vouched_last_12_mo] = 0 AND purchase.[qty_vouched_last_24_mo] = 0 AND purchase.[qty_vouched_last_36_mo] = 0 AND usage.qty_usage_12_mo_ago = 0 AND usage.qty_usage_24_mo_ago = 0 AND usage.qty_usage_36_mo_ago = 0) order by inv_mast.item_id left join (select inv_tran.inv_mast_uid, sum(case when inv_tran.date_created >= @Start12Date AND inv_tran.date_created <= @End12Date then (inv_tran.quantity) else 0 end) as [qty_usage_12_mo_ago], sum(case when inv_tran.date_created >= @Start24Date AND inv_tran.date_created <= @End12Date then (inv_tran.quantity) else 0 end) as [qty_usage_24_mo_ago], sum(case when inv_tran.date_created >= @Start36Date AND inv_tran.date_created <= @End12Date then (inv_tran.quantity) else 0 end) as [qty_usage_36_mo_ago] from inv_tran WITH (NOLOCK) where inv_tran.date_created >= @Start36Date and inv_tran.date_created <= @End12Date and inv_tran.trans_type in ('PROP','MP') group by inv_tran.inv_mast_uid) as usage on inv_mast.inv_mast_uid = usage.inv_mast_uid