/*
REPORT TO GENEATE OLD INVENTORY BY QUARTER SUMMARY
This is working accurately as of 4/8/19.

TODO:
SET TO RUN WITH INPUT VARIABLES RATHER THAN STATIC CODED CLASSES AND DATES
CHECK THAT THE INNER SELECT MATH IS ACCURATE - SOME OF THE NUMBERS LOOKED SLIGHTLY OFF.
THERE IS A VIEW FUNCITON THAT THIS REPORT CHECKS IN THE ANALYSIS SIDE OF THINGS AND IT produces different results
*/

use Celerant
set nocount on
set ansi_warnings off
select

TB_TAXONOMY.DEPT,
tb_taxonomy.typ,
tb_taxonomy.SUBTYP_1,
tb_taxonomy.SUBTYP_2,
tb_taxonomy.SUBTYP_3,
sum(sku_group.qoh *sku_group.PRICE) as total_oh,
sum(case when (datepart(Q, sku_group.LAST_RCVD) = '1' and datepart(YEAR, sku_group.LAST_RCVD) = '2019')then (sku_group.QOH*sku_group.PRICE) else 0 end) as MO_0to3,
sum(case when (datepart(Q, sku_group.LAST_RCVD) = '4' and datepart(YEAR, sku_group.LAST_RCVD) = '2018')then (sku_group.QOH*sku_group.PRICE) else 0 end) as MO_3to6,
sum(case when (datepart(Q, sku_group.LAST_RCVD) = '3' and datepart(YEAR, sku_group.LAST_RCVD) = '2018')then (sku_group.QOH*sku_group.PRICE) else 0 end) as MO_6to9,
sum(case when (datepart(Q, sku_group.LAST_RCVD) = '2' and datepart(YEAR, sku_group.LAST_RCVD) = '2018')then (sku_group.QOH*sku_group.PRICE) else 0 end) as MO_9to12,
sum(case when (sku_group.LAST_RCVD <= '2018-03-31') then (sku_group.QOH*sku_group.PRICE) else 0 end) as MO_12



FROM
(
select
tb_skus.sku_id as sku_id,
sum(TB_SKU_BUCKETS.QOH) as qoh,
avg(tb_sku_buckets.PRICE) as price,
max(TB_SKU_BUCKETS.LAST_RCVD) as last_rcvd
from TB_SKUS
inner join
TB_SKU_BUCKETS on tb_skus.SKU_ID = TB_SKU_BUCKETS.SKU_ID
inner join TB_STYLES on TB_SKUS.STYLE_ID = TB_STYLES.STYLE_ID
group by tb_skus.SKU_ID

)sku_group
left join TB_SKUS on TB_SKUS.sku_id = sku_group.sku_id
left join TB_STYLES on TB_STYLES.STYLE_Id = TB_SKUS.STYLE_ID
left join TB_TAXONOMY on TB_STYLES.TAXONOMY_ID = TB_TAXONOMY.TAXONOMY_ID



where
TB_TAXONOMY.DEPT = 'inline'
and
(TB_TAXONOMY.TYP = 'Aftercare / repair'
or
TB_TAXONOMY.TYP = 'backpking / campng'
or
TB_TAXONOMY.TYP = 'electronics'
or
TB_TAXONOMY.TYP = 'first aid / hygiene'
or
TB_TAXONOMY.TYP = 'food / nutrition'
or
TB_TAXONOMY.TYP = 'storage'
)



GROUP BY

TB_TAXONOMY.DEPT,
tb_taxonomy.typ,
tb_taxonomy.SUBTYP_1,
tb_taxonomy.SUBTYP_2,
tb_taxonomy.SUBTYP_3

;