Styjun

Python - Fishing Simulator

60's-70's movie: home appliances revolting against the owners

Loose spokes after only a few rides

Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?

Working through the single responsibility principle (SRP) in Python when calls are expensive

Keeping a retro style to sci-fi spaceships?

The following signatures were invalid: EXPKEYSIG 1397BC53640DB551

Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?

Didn't get enough time to take a Coding Test - what to do now?

Deal with toxic manager when you can't quit

Could an empire control the whole planet with today's comunication methods?

Simulating Exploding Dice

should truth entail possible truth

Using dividends to reduce short term capital gains?

Sort list of array linked objects by keys and values

What to do when moving next to a bird sanctuary with a loosely-domesticated cat?

Does Parliament hold absolute power in the UK?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Do ℕ, mathbbN, BbbN, symbbN effectively differ, and is there a "canonical" specification of the naturals?

How to read αἱμύλιος or when to aspirate

Example of compact Riemannian manifold with only one geodesic.

1960s short story making fun of James Bond-style spy fiction

How to support a colleague who finds meetings extremely tiring?

Does Parliament need to approve the new Brexit delay to 31 October 2019?

Last Day of Each Month Calculation

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

-1

I have a table X which holds data for each day for a brand. The data for each day is cumulative i.e. sales data for 3 will have data for 1, 2 and 3. Thus data for the last day of each month will be the sales for that month for that brand and company. I want to get the sum of all the sales for that brand for the last 3 months excluding the current month on the last day of each month.
i.e for March: I want sales from 31st Jan 2019 + 28th Feb 2019 + 31st Dec 2018 for each brand and company.
How can I achieve this?

sql sql-server date

share|improve this question

asked Mar 22 at 5:49

rahulrahul

1

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  If you want all the code to be written by someone, you can bargain freelancers hopefully. Otherwise, please share what you've tried.
  
  – vahdet
  Mar 22 at 6:11
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Include sample data and expected output.
  
  – Joakim Danielson
  Mar 22 at 7:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to SO! Could you please show us what you've tried so far? This seems like a homework question.
  
  – WynDiesel
  Mar 22 at 11:41
  

  
  
  
  

add a comment | 

-1

I have a table X which holds data for each day for a brand. The data for each day is cumulative i.e. sales data for 3 will have data for 1, 2 and 3. Thus data for the last day of each month will be the sales for that month for that brand and company. I want to get the sum of all the sales for that brand for the last 3 months excluding the current month on the last day of each month.
i.e for March: I want sales from 31st Jan 2019 + 28th Feb 2019 + 31st Dec 2018 for each brand and company.
How can I achieve this?

sql sql-server date

share|improve this question

asked Mar 22 at 5:49

rahulrahul

1

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  If you want all the code to be written by someone, you can bargain freelancers hopefully. Otherwise, please share what you've tried.
  
  – vahdet
  Mar 22 at 6:11
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Include sample data and expected output.
  
  – Joakim Danielson
  Mar 22 at 7:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to SO! Could you please show us what you've tried so far? This seems like a homework question.
  
  – WynDiesel
  Mar 22 at 11:41
  

  
  
  
  

add a comment | 

I have a table X which holds data for each day for a brand. The data for each day is cumulative i.e. sales data for 3 will have data for 1, 2 and 3. Thus data for the last day of each month will be the sales for that month for that brand and company. I want to get the sum of all the sales for that brand for the last 3 months excluding the current month on the last day of each month.
i.e for March: I want sales from 31st Jan 2019 + 28th Feb 2019 + 31st Dec 2018 for each brand and company.
How can I achieve this?

sql sql-server date

share|improve this question

asked Mar 22 at 5:49

rahulrahul

1

sql sql-server date

sql sql-server date

share|improve this question

asked Mar 22 at 5:49

rahulrahul

1

share|improve this question

asked Mar 22 at 5:49

rahulrahul

1

asked Mar 22 at 5:49

rahulrahul

1

asked Mar 22 at 5:49

rahulrahul

1

2 Answers
2

active

oldest

votes

0

if you are using MSSQL you can use EOMONTH function, example is as under

DECLARE @date VARCHAR(255) = '2/24/2019'; 
SELECT EOMONTH ( @date ) AS Result; 

for MySQL you can use LAST_DAY function

SELECT LAST_DAY('2019-02-24');

share|improve this answer

answered Mar 22 at 7:32

VirenViren

11

add a comment | 

0

Let's say name of your column representing the sales date is "sales_date", then the following predicate will give you the days you're interested in:

sales_date in (
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 2, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 1, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)

share|improve this answer

answered Mar 22 at 10:52

hanspohanspo

16

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%2f55293611%2flast-day-of-each-month-calculation%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

if you are using MSSQL you can use EOMONTH function, example is as under

DECLARE @date VARCHAR(255) = '2/24/2019'; 
SELECT EOMONTH ( @date ) AS Result; 

for MySQL you can use LAST_DAY function

SELECT LAST_DAY('2019-02-24');

share|improve this answer

answered Mar 22 at 7:32

VirenViren

11

add a comment | 

0

if you are using MSSQL you can use EOMONTH function, example is as under

DECLARE @date VARCHAR(255) = '2/24/2019'; 
SELECT EOMONTH ( @date ) AS Result; 

for MySQL you can use LAST_DAY function

SELECT LAST_DAY('2019-02-24');

share|improve this answer

answered Mar 22 at 7:32

VirenViren

11

add a comment | 

if you are using MSSQL you can use EOMONTH function, example is as under

DECLARE @date VARCHAR(255) = '2/24/2019'; 
SELECT EOMONTH ( @date ) AS Result; 

for MySQL you can use LAST_DAY function

SELECT LAST_DAY('2019-02-24');

share|improve this answer

answered Mar 22 at 7:32

VirenViren

11

DECLARE @date VARCHAR(255) = '2/24/2019'; 
SELECT EOMONTH ( @date ) AS Result; 

SELECT LAST_DAY('2019-02-24');

share|improve this answer

answered Mar 22 at 7:32

VirenViren

11

answered Mar 22 at 7:32

VirenViren

11

answered Mar 22 at 7:32

VirenViren

11

0

Let's say name of your column representing the sales date is "sales_date", then the following predicate will give you the days you're interested in:

sales_date in (
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 2, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 1, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)

share|improve this answer

answered Mar 22 at 10:52

hanspohanspo

16

add a comment | 

0

Let's say name of your column representing the sales date is "sales_date", then the following predicate will give you the days you're interested in:

sales_date in (
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 2, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 1, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)

share|improve this answer

answered Mar 22 at 10:52

hanspohanspo

16

add a comment | 

Let's say name of your column representing the sales date is "sales_date", then the following predicate will give you the days you're interested in:

sales_date in (
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 2, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 1, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)

share|improve this answer

answered Mar 22 at 10:52

hanspohanspo

16

sales_date in (
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 2, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) - 1, 0)),
 dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)

share|improve this answer

answered Mar 22 at 10:52

hanspohanspo

16

answered Mar 22 at 10:52

hanspohanspo

16

answered Mar 22 at 10:52

hanspohanspo

16