Inventory Running Total & calculations
-
Good Morning,
I've been tasked with converting an excel inventory spreadsheet into a ssrs report. I'm having trouble figuring out how to write the query to produce the results i need.
Fortunately the data comes from one table , where I created a VIEW with a UNION ALL to present the data I need, using a where clause to differentiate the product, but now that I need to add the various calculations I'm stuck.
I have to calculate our running Inventory by day , using an incremental running total , reduce the Wet product by 0.87 and subtract the Dry product from the total. I've inserted an example from Excel. that shows the calc.
This is the query so far, I've tried various other things to no avail.
---- Total Tons produced -------
declare @start date
declare @end date
declare @prod nvarchar(255)
declare @inv int
SET @inv = '10000'
set @start = '2020-12-01 00:00:00.000'
set @end = '2020-12-07 00:00:00.000'
select
DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
end as DW,
DATEPART (m, datetime) AS Month,
DATEPART (YYYY, datetime) AS Year,
case when meshtype = '100 mesh' then '50/140'
when meshtype = 'Raw Infeed Coarse' then 'coarse'
when meshtype = 'Raw Infeed Fines' then 'Fines'
else meshtype end as meshtype
,sum(tonsperhour) TonsProduced
from tons_per_hour
where
datetime between @start and @end
--datetime >= dateadd(day, datediff(day, 0, dbo.GetDateCDT(GETDATE())), 0)
and datepart(mi, datetime)in (0)
and datepart(ss, datetime)=0
and meshtype <> '100 mesh'
and meshtype <> 'Raw Sand A'
and meshtype <> 'Raw Sand B'
and meshtype <> 'Raw Infeed Coarse'
and whsecd=210
group by
meshtype,DATEPART (d, datetime), DATEPART (m, datetime) , DATEPART (yyyy, datetime), DATEPART(DW, datetime)
UNION ALL
select
DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
end as DW,
DATEPART (m, datetime) AS Month,
DATEPART (YYYY, datetime) AS Year,
case meshtype
when 'Raw A' then 'Dry Feed'
when 'Raw B' then 'Dry Feed' end Meshtype
,sum(tonsperhour) TonsProduced
from tons_per_hour
where datetime between @start and @end
--datetime >= dateadd(day, datediff(day, 0, dbo.GetDateCDT(GETDATE())), 0)
and datepart(mi, datetime)in (0)
and datepart(ss, datetime)=0
and meshtype not in ('Raw Infeed Fines','Raw Infeed Coarse')
and meshtype not like '%washed%'
and whsecd=210
group by
meshtype,DATEPART (d, datetime), DATEPART (m, datetime) , DATEPART (yyyy, datetime), DATEPART(DW, datetime)
order by 1,3,2I tried without a view and a select with the below , but the columns each of these created put me in a tail spin because I didnt know how to filter out the irrelavant values it produced.
sum(tonsperhour) 'Wet Feed',
sum((tonsperhour) * 0.87 ) 'Wet Percentage',
sum((tonsperhour) * 0.87 ) + @inv InvThank you for your guidance and help.
-
It's not fully explained imo. The SELECT above the UNION ALL is summarizing 'Wet Feed' tons per hour? The SELECT below the UNION ALL is 'Dry Feed'? It appears you're looking for a crosstab query where the columns are (across) days. A good step 1 would be to summarize the data into 3 columns: 1) 'Day' (cast(datetime as date)), 2) 'Wet Feed' (sum(tonsperhour)), and 3) 'Dry Feed' (sum(tonsperhour)).
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I tried my best to explain with screenshots - sorry.
Below you see the result I'm getting , identify my meshtype, but i need to add additional calculated columns to this.
Here is the result from another select query where I was able to add the wet product calculated fields. but dont know how to introduce the Dry product and running total. maybe a sub query ?
this is the query Im using.
---- WET Total Tons produced Today-------
declare @start date
declare @end date
declare @prod nvarchar(255)
declare @inv int
SET @inv = '10000'
set @start = '2020-12-01 00:00:00.000'
set @end = '2020-12-07 00:00:00.000'
select
--datetime,
DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
end as DW
,DATEPART (m, datetime) AS Month,
DATEPART (YYYY, datetime) AS Year,
sum(tonsperhour) 'wet feed',
sum((tonsperhour) * 0.87 ) 'Wet Perc',
sum((tonsperhour) * 0.87 ) + @inv Inv
from tons_per_hour
where datetime between @start and @end
--datetime >= dateadd(day, datediff(day, 0, dbo.GetDateCDT(GETDATE())), 0)
and datepart(mi, datetime)in (0)
and datepart(ss, datetime)=0
--and datediff(d, datetime, getdate()) <=60
and meshtype <> '100 mesh'
and meshtype <> 'Raw Sand A'
and meshtype <> 'Raw Sand B'
and meshtype not in ('40/140','40/150', '40/70', '50/140', 'Washed Sand A', 'Washed Sand B', 'Washed Sand C' )
and meshtype <> 'Raw Infeed Coarse'
and whsecd=210
group by
DATEPART (d, datetime), DATEPART (m, datetime) , DATEPART (yyyy, datetime), DATEPART(DW, datetime)
--, tonsperhour, datetimeIs a crosstab query still necessary to achieve this ?
-
Help from anyone Please !
-
The second post makes the first one clearer. In the first post the 2nd query in the statement containing UNION ALL is the only place where 'Dry Feed' is defined and it's not an existing meshtype? It seems like yes.
Not sure what's happening with the date handling... I removed these two lines from each of the queries if they're truly necessary then they would need to be added back:
and datepart(mi, datetime) in (0)
and datepart(ss, datetime)=0Regarding storing dates as datetime... I converted the @start and @end to datetime and added '23:59:59:999' to the @end so that BETWEEN ought to work. Since the columns in a crosstab are defined with explicit references to data elements it's not possible to make the range of days variable without dynamic SQL. The code makes the day range fixed from Sunday to Saturday (which aligns with SQL Server's default DW assignments).
Before getting into the pivoting and calculation does the following query correctly summarize Wet/Dry Feed tons per day?
with tons_cte(day_dt, dw, mo, yr, meshtype, tonsperhour) as (
select datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime]),
case when meshtype in('Raw Sand A', 'Raw Sand B') then 'Dry Feed'
when meshtype not in('100 mesh', 'Raw Sand A',
'Raw Sand B', '40/140',
'40/150', '40/70',
'50/140', 'Washed Sand A',
'Washed Sand B', 'Washed Sand C',
'Raw Infeed Coarse') then 'Wet Feed'
else meshtype end,
tonsperhour
from tons_per_hour
where [datetime] between @start and @end
and whsecd=210
group by datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime]),
case when meshtype in('Raw Sand A', 'Raw Sand B') then 'Dry Feed'
when meshtype not in('100 mesh', 'Raw Sand A',
'Raw Sand B', '40/140',
'40/150', '40/70',
'50/140', 'Washed Sand A',
'Washed Sand B', 'Washed Sand C',
'Raw Infeed Coarse') then 'Wet Feed'
else meshtype end)
select *
from tons_cte
where meshtype in('Dry Feed', 'Wet Feed');Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Just as a bit of a drive by shooting, the following code...
select
DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
end as DW,... can be replaced by the following...
SELECT DATEPART (dd, [datetime]) AS Day,
LEFT(DATENAME(dw,[datetime]),3) AS DW--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Steve,
Yes the CTE works. The only thing is its not picking up the dry feed. only wet was returned.
Here is the where clause that filters out the correct meshtypes to get both wet and dry....
BTW the below datepart that was in there only return records on the hour , since we have them coming in every 15 mins. but that not really relevant here.
and datepart(mi, datetime) in (0)
and datepart(ss, datetime)=0thanks
-
Sorry it's been a busy day! Please give this a try
declare
@start datetime='2020-11-29 00:00:00.000',
@end datetime='2020-12-05 23:59:59:999',
@inv int=10000;
with
days_cte(dw) as (
select * from (values (1),(2),(3),(4),(5),(6),(7)) v(dw)),
dry_cte(dw, day_wk, mo, yr, meshtype, tons) as (
select datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime]),
'Dry Feed',
sum(tonsperhour)
from tons_per_hour
where [datetime] between @start and @end
and datepart(mi, datetime) in (0)
and datepart(ss, datetime)=0
and meshtype not in('100 mesh', '40/140', '40/150',
'40/70', '50/140', 'Raw Infeed Fines',
'Raw Infeed Coarse', 'Non Frac')
and meshtype not like '%washed%'
and whsecd=210
group by datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime])),
wet_cte(dw, day_wk, mo, yr, meshtype, tons) as (
select datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime]),
'Wet Feed',
sum(tonsperhour)
from tons_per_hour
where [datetime] between @start and @end
and datepart(mi, datetime) in (0)
and datepart(ss, datetime)=0
and meshtype not in('100 mesh', 'Raw Sand A', 'Raw Sand B',
'40/140', '40/150', '40/70', '50/140',
'Non Frac', 'Washed Sand A', 'Washed Sand B',
'Washed Sand C', 'Raw Infeed Coarse')
and whsecd=210
group by datepart(dw, [datetime]),
left(datename(dw, [datetime]), 3),
month([datetime]),
year([datetime])),
inv_cte(dw, calc_inv) as (
select d.dw, sum(case when d.dw=1 then @inv+(isnull(wet.tons, 0)*.87)-isnull(dry.tons, 0)
else (isnull(wet.tons, 0)*.87)-isnull(dry.tons, 0) end)
over (order by d.dw) calc_inv
from days_cte d
left join dry_cte dry on d.dw=dry.dw
left join wet_cte wet on d.dw=wet.dw),
pre_pvt_cte(dw, order_num, meshtype, tons) as (
select d.dw, 1, 'Wet Feed', isnull(wet.tons, 0)
from days_cte d
left join wet_cte wet on d.dw=wet.dw
union all
select d.dw, 2, 'Dry Feed', isnull(dry.tons, 0)
from days_cte d
left join dry_cte dry on d.dw=dry.dw
union all
select dw, 3, 'Inventory', calc_inv
from inv_cte)
select meshtype.
max(case when dw=1 then tons else 0 end) Sun,
max(case when dw=2 then tons else 0 end) Mon,
max(case when dw=3 then tons else 0 end) Tue,
max(case when dw=4 then tons else 0 end) Wed,
max(case when dw=5 then tons else 0 end) Thu,
max(case when dw=6 then tons else 0 end) Fri,
max(case when dw=7 then tons else 0 end) Sat
from pre_pvt_cte
group by meshtype, order_num
order by order_num;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Totally understand , it been a busy week. but thank you for taking the time to help me on this. - much appreciated !
So , the pivot on the DW is working, I had to comment out the order by , was complaining about that. For some reason the Dry Tons still not coming through.
-
To fix the first issue you could add 'order_num' to the GROUP BY clause.
Not sure why 'Dry Feed' doesn't show. It's using an equivalent of the 'Dry Feed' WHERE clause you provided, no?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
yeeha. - Ok, I corrected the where, I'm getting dry feed. - thanks !
So now that the pivot is working. The tricky calculation is to get the previous days total , apply the 0.87% on todays wet feed and subtract todays Dry feed as per the spreadsheet expression at top of the post. I'm thinking is some type of running total , but not usre how to capture that ?
-
Steve, I miss spoke - you nailed it on the inventory. calc as well. - Wow a big - thank you !!
I was way out of my depth here, thanks for your time and the help from this forum is invaluable.
-Greg
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply