September 29, 2003 at 8:09 am
I need to retrieve the data of the last day of every month. I wrote a SP that does the job using cursor but wondering if there is a more elegant way to do it.
I think in MS access you can use last to get the last row of a group of records. but can not find an equivalence in MS SQL. otherwise I could write something like:
select datepart(yyyy, saleDate), datepart(m,saleDate), last(salesAmount)
from sales
group by datepart(yyyy, saleDate), datepart(m,saleDate)
Please advise.
TIA
September 29, 2003 at 8:41 am
SELECT CONVERT(char(7),SaleDate,120), SalesAmount
FROM Sales
WHERE SaleDate = DATEADD(m,1,SaleDate) - DAY(DATEADD(m,1,SaleDate))
GROUP BY CONVERT(char(7),SaleDate,120)
--Jonathan
Edited by - jonathan on 09/29/2003 08:41:49 AM
--Jonathan
September 29, 2003 at 11:37 am
Thanks for the quick response.
It works. ( but I need to comment out the last line, otherwise it gives error about SalesAmount).
It works fine if there are data for the very last day of a month. However sometimes the data is not ideal. due to the fact that the last day is on weekend, holiday. Any suggestion to deal with this "incomplete" data case?
September 29, 2003 at 11:50 am
quote:
Thanks for the quick response.It works. ( but I need to comment out the last line, otherwise it gives error about SalesAmount).
Oh, yeah. Make that SUM(SalesAmount) unless you've got SaleDate in a unique index.
quote:
It works fine if there are data for the very last day of a month. However sometimes the data is not ideal. due to the fact that the last day is on weekend, holiday. Any suggestion to deal with this "incomplete" data case?
SELECT s.SaleDate, s.SalesAmount
FROM Sales s JOIN
(SELECT MAX(SaleDate) LastSaleDate
FROM Sales
GROUP BY CONVERT(char(6),SaleDate,112)) l
ON s.SaleDate = l.LastSaleDate
--Jonathan
Edited by - jonathan on 09/29/2003 11:50:27 AM
--Jonathan
September 30, 2003 at 3:40 am
in relation to the 2nd part of your question...."It works fine if there are data for the very last day of a month. However sometimes the data is not ideal. due to the fact that the last day is on weekend, holiday. Any suggestion to deal with this "incomplete" data case?"
One tack would be to "left join" each actual day in your current dataset with a list of all possible days in the month (a tally table of numbers 1-31 should suffice).
This should allow you identify the last actual "working day" in the month...because as you've found out...there's no figures when it's not a working day.
A bit of experimentation will be required.
September 30, 2003 at 5:42 am
Do you want each last day of each month or the last entry for each month?
e.g if date is
saleDate salesAmount
2003-01-31 35.92
2003-02-28 77.23
2003-03-28 145.88
do you want it to show
2003-01-31 35.92
2003-02-28 77.23
2003-03-28 145.88
or
2003-01-31 35.92
2003-02-28 77.23
2003-03-31 0.00
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply