Return max values between zeroes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return max values between zeroes

Author

Topic

Stevan23
Starting Member

15 Posts

Posted - 2013-04-13 : 22:41:39

Hi all,

We're using MSSQL 2008 and our table data has a datetime datatype (column called reading_date_time) and an int datatype (column called tank_Shell). It basically records the weight of shell egg waste placed in a bin and is weighed on some load cells that record every minute. When emptied, the user replaces the bin and tares the weight taking the weight back to zero.

We are needing to calculate the egg shell weight for the day. There can be an opening balance of shell waste for the day and the bin can be emptied more than once in a day. Now I won't post all the data (as we have over 2000 rows, but will provide a small sample).

Sample Data:

Please note: O/B means Opening Balance and C/B means Closing Balance

Reading_date_time [] tank_Shell
08/04/2013 08:04:00.000 [] 855 --this is the O/B for 08/04
08/04/2013 08:08:00.000 [] 855 --no change to weight
08/04/2013 08:15:00.000 [] 851 --data moves slighty sometimes
08/04/2013 08:16:00.000 [] 0 --this is the bin being emptied
08/04/2013 08:58:00.000 [] 22 --the bin has started to fill up
08/04/2013 23:59:00.000 [] 534 --this is the C/B for 08/04
09/04/2013 00:00:00.000 [] 534 --this is the O/B for 09/04
09/04/2013 13:03:00.000 [] 1,177 --this is the bin full
09/04/2013 13:03:04.000 [] 0 --bin has been emptied
09/04/2013 13:18:00.000 [] 11 --bin has started to fill up
09/04/2013 14:20:00.000 [] 51 --bin continues to fill up
09/04/2013 23:59:00.000 [] 614 --this is the C/B for 09/04

Expected Results:
Shell produced for 08/04/2013 = 534 (as 855 was for previous day)
Shell produced for 09/04/2013 = (1,177 - 534) + 614 = 1,257
Total for both days = 1,791

Now I have a query to pick up opening balances for the day, but I do not know how to pick up the max values between the zeroes. Any help would be greatly appreciated.

Many thanks,
Stevan23

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-14 : 19:16:37

Here is something to get you started - this may not be the exact thing you are looking for in regards to the data moving without it going down to zero.

The hardest thing about posting a solution for me and probably for many others who respond to questions on this forum, is creating some sample data that is easily consumed by a query. So if you post your question with sample table(s) and data like I created below, which someone can then copy and use to write and test a query, you would get faster and more accurate answers.

create table #tmp(reading_datetime datetime, tank_shell int);

insert into #tmp values

('08/04/2013 08:04:00.000',855), 
('08/04/2013 08:08:00.000',855),
('08/04/2013 08:15:00.000',851 ),
('08/04/2013 08:16:00.000',0),
('08/04/2013 08:58:00.000',22),
('08/04/2013 23:59:00.000',534), 
('09/04/2013 00:00:00.000',534 ),
('09/04/2013 13:03:00.000',1177),
('09/04/2013 13:03:04.000',0),
('09/04/2013 13:18:00.000',11),
('09/04/2013 14:20:00.000',51),
('09/04/2013 23:59:00.000',614 )


;with cte as
(
select
	row_number() over (partition by cast(reading_datetime as date) order by reading_datetime asc ) as FirstVal,
	row_number() over (partition by cast(reading_datetime as date) order by reading_datetime desc) as LastVal,
	cast(reading_datetime as date) as dt,
	tank_shell
from
	#tmp
)
select
	a.dt,
	sum(case when b.tank_shell = 0 then a.tank_shell else 0 end)
	+ sum(case when a.LastVal = 1 then a.tank_shell else 0 end)
	- sum(case when a.FirstVal = 1 then a.tank_shell else 0 end)
from
	cte a
	left join cte b on 
		b.FirstVal = a.FirstVal + 1 and a.dt = b.dt
group by
	a.dt;

drop table #tmp;

Stevan23
Starting Member

15 Posts

Posted - 2013-04-14 : 20:28:02

Hi James K,

Thanks for the advice with writing on creating the sample data table(s) in my posting of the question. I will make sure I do this in future.

Thanks also for the query. I ran it in our SQL database and it only came up with 3 kgs of difference (I'm thinking this is due to the fluctuation our data gets as it seems to move up and down sometimes - we're not sure exactly why this happens).

I think this has solved the issue we were having with the query.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-15 : 08:27:55

The difference you see has to do with the fluctuations that you mentioned in your original post. In your example, the beginning of day value changes from 855 to 851. The query is considering only the initial value of 855.

If you do want to use the 851, that requires several questions to be answered, and the code modified. Questions such as: instead of changing from 855 to 851, if it were to change from 855 to 858, would you still consider that as a fluctuation to be ignored, or is it part of the addition to the day. Or, if you had no instances where the value went down to zero and would a change from 855 to 858 be considered an addition or a change etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources