Perpetual Total - calculate difference between first and last value

Question

Perpetual Total - calculate difference between first and last value

gjoelson 29755

SSC Eights!

Points: 848
More actions
September 7, 2021 at 4:44 pm

#3927567
Hi,
I'm having to calculate a daily total tons from a continually incrementing number for a few products.
I capture these increasing totals every 15 mins ,
I'm look at the best way to use a select query to achieve this, and thinking I need to use the first entry for the day at Datetime ('2021-09-07 00:00:00.000') and subtract it from the last value (Top 1) of the same day. or do some type on min /max
eg: (Last Value = 1500) - (Value at '00:00:00.000' = 1000) = 500
Ive tried this and not getting the results.
any guidance would be great. - Thanks
```
---- Total Tons produced Today-------

select 
Case when product = 'Prod-1' then  max(Tonsproduced) - Min(Tonsproduced) 
Case when product = 'Prod-2' then  max(Tonsproduced) - Min(Tonsproduced) 
Case when product = 'Prod-3' then  max(Tonsproduced) - Min(Tonsproduced) 
else 0 end as tonproduced 
FROM [dbo].[tons_produced]
  where meshtype in ( 'Prod-1', 'Prod-2', 'Prod-2')
  and datetime >= dateadd(day, datediff(day, 0, dbo.GetDateCDT(GETDATE())), 0)
  group by product, tonsproduced
--order by 3,2, 1
```

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2069 More actions · Answer 1

The function dbo.GetDateCDT(GETDATE())) returns the 'Central Standard Time' (which adjusts for DST afaik) as date? These lines are equivalent?

-- returns Central Time truncated to date?
select dateadd(day, datediff(day, 0, dbo.GetDateCDT(getdate())), 0);

-- returns Central Time truncated to date
select cast(getdate() at time zone 'Central Standard Time' as date);

In the WHERE clause 'Prod-2' is listed twice in the condition applied to 'meshtype'. It should be 'Prod-3'?

Maybe something like this

select product, max(Tonsproduced)-min(Tonsproduced) as tonproduced 
from [dbo].[tons_produced]
where meshtype in ('Prod-1', 'Prod-2', 'Prod-3')
      and datetime >= cast(getdate() at time zone 'Central Standard Time' as date)
group by product
order by product;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 2

yes, that GetDateCDT is a function to get CST.

My question was really more about how to deal with the start and end totals. and how to to work CASE WHEN with the various products.

so if I use the min/max , how would that work if I query a range of days like ,

where datetime between @start and @end

How would we group them by Day ? , would min/max work in this case ?

thanks

Steve Collins SSCrazy Points: 2069 More actions · Answer 3

Suppose an offset day variable '@day_offset' is declared and then subtracted from GETDATE(). To get a daily report you could CAST the 'datetime' column into 'date' using CROSS APPLY (VALUES ...) DY(DT) and add it to the GROUP BY clause. If this doesn't work then some sample data would be helpful 🙂

declare
  @day_offset       int=14,
  @tz               sysname=N'Central Standard Time';

select tp.product, dy.dt, max(tp.Tonsproduced)-min(tp.Tonsproduced) as tonproduced 
from [dbo].[tons_produced] tp
     cross apply (values (cast(tp.[datetime] as date))) dy(dt)
where tp.meshtype in ('Prod-1', 'Prod-2', 'Prod-3')
      and tp.[datetime] >= cast(getdate() -@day_offset at time zone @tz as date)
group by tp.product, dy.dt
order by tp.product, dy.dt;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Ken McKelvey SSCoach Points: 18768 More actions · Answer 4

I suspect you need to use the FIRST_VALUE() and LAST_VALUE() windowed functions but without test data it is difficult to say.

With over 800 points you should know how to post questions by now.

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 5

Agreed, I should have given data and the Table code please see below...

Thanks !

CREATE TABLE [dbo].[tons_produced](
[datetime] [datetime] NOT NULL,
[whsecd] [int] NOT NULL,
[plant] [varchar](20) NOT NULL,
[meshtype] [varchar](20) NOT NULL,
[tonsproduced] [decimal](18, 10) NULL,
 CONSTRAINT [PK_tons_produced] PRIMARY KEY CLUSTERED 
(
[datetime] ASC,
[whsecd] ASC,
[plant] ASC,
[meshtype] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

here is some table data to help....

SELECT 'Sep  8 2021 11:45AM','211','San Antonio','40/140','314.5415649414', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','40/70','0.0000000000', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','Non Frac','2944.8012695311', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-40/140','12738.6035156250', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-40/70','5402.2673339843', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-50/140','13100.4667968750', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-Raw Sand A','23137.7656250000', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-Raw Sand B','19721.0429687500', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-Reject A','1020.1834716796', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','P-Reject B','678.4140625000', UNION ALL
SELECT 'Sep  8 2021 11:45AM','211','San Antonio','Waste','59.5177726745', UNION ALL

pietlinden SSC Guru Points: 63428 More actions · Answer 6

Your data is a bit wonky, but maybe something along these lines?

SELECT meshtype,

[datetime] as stamp,

tonsProduced,

theDate = CAST([datetime] AS DATE),

theTime = CAST([datetime] AS TIME)

-- fv = first_value([datetime]) OVER (PARTITION BY meshtype ORDER BY [datetime]),

-- lv = last_value([datetime]) OVER (PARTITION BY meshtype ORDER BY [datetime])

FROM tons_produced

ORDER BY meshtype, [datetime];

If you split date and time, you can get the first value and last value for each date, and then this is trivial.

lv - fv is your daily amount. But there must be a catch because that looks waaaay too simple.

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 7

Thanks , I ran your query and it doesn't capture exactly what I need.....

So obviously there are multiple records per day for each product, in fact every 15 mins the total increments.

The challenge is trying to get the LV (TonsProduced) minus FV (TonsProduced) Total for the day per product that can span over a range of days (where datetime between @start and @end)

Ideally, Id like to put it in my current below format similar to what I have now. except I basically need to figure out how to get that Total TonsProduced LV-FV calculated.

The below query is something I use currently on a different set of data table.

---- Total Tons produced -------

select
--datetime, 
DATEPART (d, datetime) AS Day,
DATEPART (m, datetime) AS Month,
DATEPART (YYYY, datetime) AS Year,
case when meshtype = '100 mesh' then '50/140'  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 datediff(d, datetime, getdate()) <=60

group by
 
meshtype, DATEPART (d, datetime), 
DATEPART (m, datetime) , 
DATEPART (yyyy, datetime)
 --, tonsperhour, datetime
 --order by 3,2, 1

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 8

Got it .....

I ended up using the min/max totals and some grouping

thanks for the help.

pietlinden SSC Guru Points: 63428 More actions · Answer 9

Great! Could you post the solution you came up with?