I ussualy like to see during the year wich product do sell better… so In my case this report is usefull. Hpe this would be usefull for others too!!!
This report will give you a list of inventory items and a 12 column report month by month quantities.
Please note that the SQL has a CASE:
CASE WHEN [UnitName] <> 'UN' THEN
CONVERT(INT,(CONVERT(numeric(6,2),[Consumption])*[UnitMultiplier]))
ELSE
CONVERT(INT,[Consumption])
END as Consumo
This is because I recently changed UNITS to avoid decimal of bottles and pizzas… but I imagine that you can just use:
CONVERT(INT,[Consumption])
if your meassure unit is UNITS
or
CONVERT(INT,(CONVERT(numeric(6,2),[Consumption])*[UnitMultiplier]))
if your meassure units is something else.
Report:
[Consumos MES a MES:3,1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
>ITEM|UN|Ene|Feb|Mar|Abr|May|Jun|Jul|Ago|Sep|Oct|Nov|Dic
@@ConsumosAnuales
>|
SQL:
SELECT [InventoryItemName], [UnitName],
ISNULL([January],0),ISNULL([February],0),ISNULL([March],0),ISNULL([April],0),ISNULL([May],0),ISNULL([June],0),
ISNULL([July],0),ISNULL([August],0),ISNULL([September],0),ISNULL([October],0),ISNULL([November],0),ISNULL([December],0)
FROM (SELECT [InventoryItemName], [UnitName],
DATENAME(Month,[StartDate]) as MES, DATEPART(Year,[StartDate]) as ANO,
CASE WHEN [UnitName] <> 'UN' THEN
CONVERT(INT,(CONVERT(numeric(6,2),[Consumption])*[UnitMultiplier]))
ELSE
CONVERT(INT,[Consumption])
END as Consumo
FROM [PeriodicConsumptionItems]
LEFT OUTER JOIN [PeriodicConsumptions] on [WarehouseConsumptionId] = [PeriodicConsumptions].[Id]
) as SOURCE_TABLE
PIVOT (
sum(Consumo)
FOR MES in ([January],[February],[March],[April],[May],[June],
[July],[August],[September],[October],[November],[December])
) as PIVOT_TABLE
WHERE ANO = YEAR(GETDATE())
ORDER BY [InventoryItemName]
Thanks!!!
G.
PD: Dear @emre, could you tell me why the last column is bigger that the rest, even tho all are 1
?
Thanks!!!