sql ocde picking last date of month value
-
Hi guys
hope you are all well
I am struggling to write a sql code which will pick up the sales value of the last date of each month.
I have this below example table showing sales which accumulate daily for each month, the late day of the month is the final sales value ( this is just example table in reality there are much more months)
from the table I need to write a query ( select query etc) that will show the below
The past sales is the sales number of the last day of the previous month ( for this case we start at November so the past sales for November is blank, for December past sales is the value you see for 31/11/2020 ) current sales is the last day of the month in question.
Can this be done? can anyone help me do this?
thank you
-
Would be a case for LAG (https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15)
create table #rollingtotals (totdate date, totamt int)
insert into #rollingtotals (totdate,totamt) values
('2020-11-10',30),
('2020-11-21',60),
('2020-11-30',75),
('2020-12-20',110),
('2020-12-31',130),
('2021-01-27',140),
('2021-01-28',195),
('2021-01-31',250)
;with cte as(
select datepart(month,totdate) as mnt, datepart(year, totdate) as yr, max(totamt) as amt
from #rollingtotals
group by datepart(month,totdate), datepart(year, totdate)
)
select mnt, yr, lag(amt,1,0) over (order by yr, mnt) AS LastMonth, amt as ThisMonth
from cte
order by yr,mnt -
Edit: Had noted LAST VALUE, but that's not helpful here.
-
This code will work for the data provided by the OP. But what if sales does in fact decrease on the last of the month (due to some credit notes for example)? Then the MAX(totamt) will not work.
I would suggest a small change:
create table #rollingtotals (totdate date, totamt int)
insert into #rollingtotals (totdate,totamt) values
('2020-11-10',30),
('2020-11-21',60),
('2020-11-30',75),
('2020-12-20',110),
('2020-12-31',105),
('2021-01-27',140),
('2021-01-28',195),
('2021-01-31',250)
;with cte as(
select datepart(month,totdate) as mnt, datepart(year, totdate) as yr, max(totdate) as lmyd
from #rollingtotals
group by datepart(month,totdate), datepart(year, totdate)
)
select mnt, yr, lag(totamt,1,0) over (order by yr, mnt) AS LastMonth, totamt as ThisMonth
from cte
inner join #rollingtotals rt on rt.totdate=cte.lmyd
order by yr,mnt;
drop table #rollingtotals; - imranazam wrote:
Hi guys
hope you are all well
I am struggling to write a sql code which will pick up the sales value of the last date of each month.
I have this below example table showing sales which accumulate daily for each month, the late day of the month is the final sales value ( this is just example table in reality there are much more months)
from the table I need to write a query ( select query etc) that will show the below
The past sales is the sales number of the last day of the previous month ( for this case we start at November so the past sales for November is blank, for December past sales is the value you see for 31/11/2020 ) current sales is the last day of the month in question.
Can this be done? can anyone help me do this?
thank you
Hello and welcome.
A lot of people like to test their code with test data before they post it. It also answers most questions folks would have about the data for your problem.
With that in mind, it would help us help you more quickly in the future if you posted your data in a manner that's readily consumable in T-SQL like the following. For more complicated data, please see the first link in my signature line below. Thanks.
Here's some test data for your stated problem. I matched all the given column names but had to make assumptions about the actual datatypes you might we using.
DROP TABLE IF EXISTS #TestTable;
CREATE TABLE #TestTable (Date DATE, Sales INT)
INSERT INTO #TestTable
(Date,Sales)
VALUES ('2020-11-10',30)
,('2020-11-21',60)
,('2020-11-30',75)
,('2020-12-20',110)
,('2020-12-31',105)
,('2021-01-27',140)
,('2021-01-28',195)
,('2021-01-31',250)
;Up next... I agree with @kaj above... While it looks like the value in Sales always increases, it's better to "Bullet Proof" your code and make it so that your code actually finds the last entry for each month. I'll also state that it's also usually best to keep the "Data Layer" and the "Presentation Layer" separate even for a single query. That also makes formatting and sorting in the correct order a whole lot easier. It also prevents wasting a whole lot of time formatting stuff that won't actually be displayed.
With that, the following creates exactly the output you asked for where none of the above do.
WITH cteEoM AS
(--==== Mark the last entry of every month with a "1". It's the "Data Layer".
SELECT EoM = ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,[Date]) ORDER BY Date DESC)
,RawDate = [Date] --Change the name of the original column so it doesn't complicate display
,Sales
FROM #TestTable
)--==== This is the "Display Layer"
SELECT [Date] = REPLACE(STUFF(CONVERT(VARCHAR(10),RawDate,6),1,3,''),' ','-')
,[Past Sales] = RIGHT(SPACE(10)+ISNULL(CONVERT(VARCHAR(10),LAG(Sales,1,NULL) OVER (ORDER BY RawDate)),''),10)
,[Current Sales] = RIGHT(SPACE(13)+CONVERT(VARCHAR(13),Sales),13)
FROM cteEOM
WHERE EoM = 1
ORDER BY RawDate
;Here's the output from the "grid" mode.
If you have any questions on the functions I used for either layer, please don't hesitate to ask. I'll tell you up front to never use the newer "FORMAT" function in T-SQL because it's 43 times slower than most anything you can do with CONVERT, STUFF, etc.
--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) -
....With that, the following creates exactly the output you asked for where none of the above do.
Acknowledged! 🙂
Adapting the code... -- though I'm still assuming that the OP has used Excel or similar to right-align his example output.
create table #rollingtotals (totdate date, totamt int)
insert into #rollingtotals (totdate,totamt) values
('2020-11-10',30),
('2020-11-21',60),
('2020-11-30',75),
('2020-12-20',110),
('2020-12-31',105),
('2021-01-27',140),
('2021-01-28',195),
('2021-01-31',250)
;with cte as(
select datepart(month,totdate) as mnt, datepart(year, totdate) as yr, max(totdate) as lmyd
from #rollingtotals
group by datepart(month,totdate), datepart(year, totdate)
)
select
substring('JanFebMarAprMayJunJulAugSepOctNovDec',mnt*3-2,3)+' ' + right(cast(yr as varchar(4)),2) as [Date],
lag(totamt,1,0) over (order by yr, mnt) AS [Past Sales],
totamt as [Current Sales]
from cte
inner join #rollingtotals rt on rt.totdate=cte.lmyd
order by yr,mnt;
drop table #rollingtotals; -
You're still hitting the table twice, though. 😉
And, yeah... I don't normally do the "right alignment" junk. I was just demonstrating that it could be done if SQL Server was necessarily the "Presentation Layer", which it usually should NOT be.
--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) -
The EOMONTH() function returns the last day of the month of a specified date, with an optional offset. The EOMONTH() function accepts two arguments: start_date is a date expression that evaluates to a date. The EOMONTH() function returns the last day of the month for this date.
- Windler wrote:
The EOMONTH() function returns the last day of the month of a specified date, with an optional offset. The EOMONTH() function accepts two arguments: start_date is a date expression that evaluates to a date. The EOMONTH() function returns the last day of the month for this date.
Sure, but how would your solution to the problem look like, using the EOMONTH() function?
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
- kaj wrote:Windler wrote:
The EOMONTH() function returns the last day of the month of a specified date, with an optional offset. The EOMONTH() function accepts two arguments: start_date is a date expression that evaluates to a date. The EOMONTH() function returns the last day of the month for this date.
Sure, but how would your solution to the problem look like, using the EOMONTH() function?
In this case, I see it being of no use because you're not looking for the last day of a given month... you're trying to find the data with the highest date of the data in each month.
And I have no idea who did it but it looks like the post was deleted because of some now unseen SPAM.
--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)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply