Styjun

Are there any individual aliens that have gained superpowers in the Marvel universe?

What is the highest power supply a Raspberry pi 3 B can handle without getting damaged?

In Street Fighter, what does the M stand for in M Bison?

How could I create a situation in which a PC has to make a saving throw or be forced to pet a dog?

How can the US president give an order to a civilian?

Kelvin type connection

How is linear momentum conserved in circular motion?

How do I find which software is doing an SSH connection?

Boundaries and Buddhism

Why things float in space, though there is always gravity of our star is present

How "fast" do astronomical events occur?

Why does a Force divides equally on a Multiple Support/Legs?

What kind of chart is this?

How is the idea of "girlfriend material" naturally expressed in Russian?

Definition of 'vrit'

I found a password with hashcat but it doesn't work

Should I include fillets on my 3d printed parts?

Why can't I craft scaffolding in Minecraft 1.14?

How can I prevent a user from copying files on another hard drive?

Does there exist a non-trivial group that is both perfect and complete?

Bent arrow under a node

Print the new site header

How would you explain #1 and #2 below using standard quotes?

Time at 1 g acceleration to travel 100 000 light years

Calculate Refill on Daily Usage

Calculate a Running Total in SQL ServerQuery to pull sum from 2 different tables, is this possible in one query?SQL Join Inventory Count Table to Date tableSQL - daily change in a value with business day into considerationCalculating Average over datePostgreSQL: Aggregate daily sum over list of durationsSQL: Dividing daily data by a monthly indexSQL-How to Sum Data of Clients Over Time?Query returning individuals most recent order, date of the order, number of products in the order and the total amountDaily average not calculating correctly as a result of Group By

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

3

I have data for daily usage like below.

I need to refill the product for every 2000 usage. This is expected result.

I try to use SUM OVER query to get daily total previous usage and try to divide to get multiple 2000.

SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY Date) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY Date) / 2000 AS INT) Div
FROM Transaction

But, I still cannot find at what date I have to refill. How can I do something like this?

sql sql-server

share|improve this question

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

add a comment | 

3

I have data for daily usage like below.

I need to refill the product for every 2000 usage. This is expected result.

I try to use SUM OVER query to get daily total previous usage and try to divide to get multiple 2000.

SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY Date) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY Date) / 2000 AS INT) Div
FROM Transaction

But, I still cannot find at what date I have to refill. How can I do something like this?

sql sql-server

share|improve this question

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

add a comment | 

I have data for daily usage like below.

I need to refill the product for every 2000 usage. This is expected result.

I try to use SUM OVER query to get daily total previous usage and try to divide to get multiple 2000.

SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY Date) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY Date) / 2000 AS INT) Div
FROM Transaction

But, I still cannot find at what date I have to refill. How can I do something like this?

sql sql-server

share|improve this question

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY Date) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY Date) / 2000 AS INT) Div
FROM Transaction

sql sql-server

sql sql-server

share|improve this question

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

share|improve this question

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

asked Mar 25 at 6:01

Viki TheoloradoViki Theolorado

445210

2 Answers
2

active

oldest

votes

3

You can try to use LAG window function with a subquery.

MS SQL Server 2017 Schema Setup:

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

Query 1:

SELECT Date, 
 Usage,
 DailyTotal,
 (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
 SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
 FROM [Transaction]
)t1

Results:

| Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 | 1373 | 1373 | 0 |
| 2017-01-02 | 1387 | 2760 | 1 |
| 2017-01-03 | 1509 | 4269 | 1 |
| 2017-01-04 | 1523 | 5792 | 0 |
| 2017-01-05 | 1537 | 7329 | 1 |

share|improve this answer

edited Mar 25 at 7:00

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have no Refill Field. Refill is the expected field I need.
  
  – Viki Theolorado
  Mar 25 at 6:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's your expect result from your sample data?
  
  – D-Shih
  Mar 25 at 6:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second Image, Table with additional Refill column.
  
  – Viki Theolorado
  Mar 25 at 6:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado Ok I got it you can try my edit answer :)
  
  – D-Shih
  Mar 25 at 6:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, LAG Function is working. Thank You. But your example use LEAD instead of LAG and its not working.
  
  – Viki Theolorado
  Mar 25 at 6:59
  

  
  
  
  

add a comment | 

1

try below using a row_number() and comparison

DEMO

select *,case when DailyTotal<2000*(case when rn=1 then 1 else rn-1 end) then 0 else 1 end
as refill from
(
SELECT Dateval, Usage,
 SUM(Usage) OVER(ORDER BY Dateval) AS DailyTotal,
 row_number() over(order by dateval) rn
FROM t1
)AA

OUTPUT:

Dateval Usage DailyTotal rn refill
01/01/2017 00:00:00 1373 1373 1 0
02/01/2017 00:00:00 1387 2760 2 1
03/01/2017 00:00:00 1509 4269 3 1
04/01/2017 00:00:00 1523 5792 4 0

share|improve this answer

edited Mar 25 at 6:36

answered Mar 25 at 6:22

fa06fa06

22.3k41119

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, this doesn't works. This works if refill happen everyday. But I want the refill only done if the total usage (today and prev day) pass 2000.
  
  – Viki Theolorado
  Mar 25 at 6:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado, I've updated the answer - you can check now
  
  – fa06
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry @fa06, but it still not complete. At first and second refill it works, but not at third refill which have to happen at the fifth day.
  
  – Viki Theolorado
  Mar 25 at 6:42
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55332012%2fcalculate-refill-on-daily-usage%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

You can try to use LAG window function with a subquery.

MS SQL Server 2017 Schema Setup:

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

Query 1:

SELECT Date, 
 Usage,
 DailyTotal,
 (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
 SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
 FROM [Transaction]
)t1

Results:

| Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 | 1373 | 1373 | 0 |
| 2017-01-02 | 1387 | 2760 | 1 |
| 2017-01-03 | 1509 | 4269 | 1 |
| 2017-01-04 | 1523 | 5792 | 0 |
| 2017-01-05 | 1537 | 7329 | 1 |

share|improve this answer

edited Mar 25 at 7:00

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have no Refill Field. Refill is the expected field I need.
  
  – Viki Theolorado
  Mar 25 at 6:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's your expect result from your sample data?
  
  – D-Shih
  Mar 25 at 6:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second Image, Table with additional Refill column.
  
  – Viki Theolorado
  Mar 25 at 6:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado Ok I got it you can try my edit answer :)
  
  – D-Shih
  Mar 25 at 6:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, LAG Function is working. Thank You. But your example use LEAD instead of LAG and its not working.
  
  – Viki Theolorado
  Mar 25 at 6:59
  

  
  
  
  

add a comment | 

3

You can try to use LAG window function with a subquery.

MS SQL Server 2017 Schema Setup:

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

Query 1:

SELECT Date, 
 Usage,
 DailyTotal,
 (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
 SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
 FROM [Transaction]
)t1

Results:

| Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 | 1373 | 1373 | 0 |
| 2017-01-02 | 1387 | 2760 | 1 |
| 2017-01-03 | 1509 | 4269 | 1 |
| 2017-01-04 | 1523 | 5792 | 0 |
| 2017-01-05 | 1537 | 7329 | 1 |

share|improve this answer

edited Mar 25 at 7:00

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have no Refill Field. Refill is the expected field I need.
  
  – Viki Theolorado
  Mar 25 at 6:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's your expect result from your sample data?
  
  – D-Shih
  Mar 25 at 6:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second Image, Table with additional Refill column.
  
  – Viki Theolorado
  Mar 25 at 6:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado Ok I got it you can try my edit answer :)
  
  – D-Shih
  Mar 25 at 6:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, LAG Function is working. Thank You. But your example use LEAD instead of LAG and its not working.
  
  – Viki Theolorado
  Mar 25 at 6:59
  

  
  
  
  

add a comment | 

You can try to use LAG window function with a subquery.

MS SQL Server 2017 Schema Setup:

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

Query 1:

SELECT Date, 
 Usage,
 DailyTotal,
 (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
 SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
 FROM [Transaction]
)t1

Results:

| Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 | 1373 | 1373 | 0 |
| 2017-01-02 | 1387 | 2760 | 1 |
| 2017-01-03 | 1509 | 4269 | 1 |
| 2017-01-04 | 1523 | 5792 | 0 |
| 2017-01-05 | 1537 | 7329 | 1 |

share|improve this answer

edited Mar 25 at 7:00

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

SELECT Date, 
 Usage,
 DailyTotal,
 (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
 SELECT Date, Usage,
 SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
 CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
 FROM [Transaction]
)t1

| Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 | 1373 | 1373 | 0 |
| 2017-01-02 | 1387 | 2760 | 1 |
| 2017-01-03 | 1509 | 4269 | 1 |
| 2017-01-04 | 1523 | 5792 | 0 |
| 2017-01-05 | 1537 | 7329 | 1 |

share|improve this answer

edited Mar 25 at 7:00

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

answered Mar 25 at 6:07

D-ShihD-Shih

28.8k61535

1

try below using a row_number() and comparison

DEMO

select *,case when DailyTotal<2000*(case when rn=1 then 1 else rn-1 end) then 0 else 1 end
as refill from
(
SELECT Dateval, Usage,
 SUM(Usage) OVER(ORDER BY Dateval) AS DailyTotal,
 row_number() over(order by dateval) rn
FROM t1
)AA

OUTPUT:

Dateval Usage DailyTotal rn refill
01/01/2017 00:00:00 1373 1373 1 0
02/01/2017 00:00:00 1387 2760 2 1
03/01/2017 00:00:00 1509 4269 3 1
04/01/2017 00:00:00 1523 5792 4 0

share|improve this answer

edited Mar 25 at 6:36

answered Mar 25 at 6:22

fa06fa06

22.3k41119

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, this doesn't works. This works if refill happen everyday. But I want the refill only done if the total usage (today and prev day) pass 2000.
  
  – Viki Theolorado
  Mar 25 at 6:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado, I've updated the answer - you can check now
  
  – fa06
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry @fa06, but it still not complete. At first and second refill it works, but not at third refill which have to happen at the fifth day.
  
  – Viki Theolorado
  Mar 25 at 6:42
  

  
  
  
  

add a comment | 

1

try below using a row_number() and comparison

DEMO

select *,case when DailyTotal<2000*(case when rn=1 then 1 else rn-1 end) then 0 else 1 end
as refill from
(
SELECT Dateval, Usage,
 SUM(Usage) OVER(ORDER BY Dateval) AS DailyTotal,
 row_number() over(order by dateval) rn
FROM t1
)AA

OUTPUT:

Dateval Usage DailyTotal rn refill
01/01/2017 00:00:00 1373 1373 1 0
02/01/2017 00:00:00 1387 2760 2 1
03/01/2017 00:00:00 1509 4269 3 1
04/01/2017 00:00:00 1523 5792 4 0

share|improve this answer

edited Mar 25 at 6:36

answered Mar 25 at 6:22

fa06fa06

22.3k41119

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, this doesn't works. This works if refill happen everyday. But I want the refill only done if the total usage (today and prev day) pass 2000.
  
  – Viki Theolorado
  Mar 25 at 6:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VikiTheolorado, I've updated the answer - you can check now
  
  – fa06
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry @fa06, but it still not complete. At first and second refill it works, but not at third refill which have to happen at the fifth day.
  
  – Viki Theolorado
  Mar 25 at 6:42
  

  
  
  
  

add a comment | 

try below using a row_number() and comparison

DEMO

select *,case when DailyTotal<2000*(case when rn=1 then 1 else rn-1 end) then 0 else 1 end
as refill from
(
SELECT Dateval, Usage,
 SUM(Usage) OVER(ORDER BY Dateval) AS DailyTotal,
 row_number() over(order by dateval) rn
FROM t1
)AA

OUTPUT:

Dateval Usage DailyTotal rn refill
01/01/2017 00:00:00 1373 1373 1 0
02/01/2017 00:00:00 1387 2760 2 1
03/01/2017 00:00:00 1509 4269 3 1
04/01/2017 00:00:00 1523 5792 4 0

share|improve this answer

edited Mar 25 at 6:36

answered Mar 25 at 6:22

fa06fa06

22.3k41119

select *,case when DailyTotal<2000*(case when rn=1 then 1 else rn-1 end) then 0 else 1 end
as refill from
(
SELECT Dateval, Usage,
 SUM(Usage) OVER(ORDER BY Dateval) AS DailyTotal,
 row_number() over(order by dateval) rn
FROM t1
)AA

Dateval Usage DailyTotal rn refill
01/01/2017 00:00:00 1373 1373 1 0
02/01/2017 00:00:00 1387 2760 2 1
03/01/2017 00:00:00 1509 4269 3 1
04/01/2017 00:00:00 1523 5792 4 0

share|improve this answer

edited Mar 25 at 6:36

answered Mar 25 at 6:22

fa06fa06

22.3k41119

answered Mar 25 at 6:22

fa06fa06

22.3k41119

answered Mar 25 at 6:22

fa06fa06

22.3k41119