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
 filter month end from daily data

Author  Topic 

DanielS
Starting Member

32 Posts

Posted - 2013-07-08 : 22:08:14
Hi, I have a series of daily data and I am looking to filter for month end business days and create a sum.

This piece of code gets what I need, arbitrarily, for the 7th day of every month. How can I amend the code so that rather than 7, I have the last business day of every month?

select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTable
where DATEPART(day,FROM_DATE) = 7
group by FROM_DATE, PORTFOLIO

I have the following code for last business day of month (from a previous question raised on these forums), but when I set DATEPART(day,FROM_DATE) equal to the following piece of code, I get no results.

CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7
WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days
ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)
END

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-09 : 00:35:50
Can you show us example input data and expected output.

The query you are constructing wont return any value because the dates you are comparing will never be equal.

Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 01:04:44
There's way too much data to give you a real slice, but here's an example:

----Data----
FROM_DATE PORTFOLIO SECURITY VALUE
27/06/2013 ZZZ A 2.56
27/06/2013 ZZZ B 9.69
27/06/2013 ZZZ C 1.23
27/06/2013 ZZZ D 1.88
27/06/2013 YYY A 7.61
27/06/2013 YYY B 7.00
27/06/2013 YYY C 6.41
27/06/2013 YYY D 8.08
28/06/2013 ZZZ A 9.89
28/06/2013 ZZZ B 3.63
28/06/2013 ZZZ C 6.34
28/06/2013 ZZZ D 4.05
28/06/2013 YYY A 1.60
28/06/2013 YYY B 3.12
28/06/2013 YYY C 1.39
28/06/2013 YYY D 1.24

----Results----
Last Business Day of Month PORTFOLIO Sum(VALUES)
28/06/2013 ZZZ 23.91
28/06/2013 YYY 7.35



quote:
Originally posted by MuMu88

Can you show us example input data and expected output.

The query you are constructing wont return any value because the dates you are comparing will never be equal.



Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-09 : 01:20:27
Try this:

[CODE]
select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTable
where FROM_DATE = (CASE DATEDIFF(dd, 0, EOMONTH(FROM_DATE))%7
WHEN 5 THEN DATEADD(dd, -1, (EOMONTH(FROM_DATE))) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, (EOMONTH(FROM_DATE))) -- if Sunday subtract two days
ELSE EOMONTH(FROM_DATE) END)
group by FROM_DATE, PORTFOLIO
[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:43:35
do you mean this?


select CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7
WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days
ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)
END
, PORTFOLIO,
SUM(VALUES)
from MyTable
group by CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7
WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days
ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)
END
, PORTFOLIO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:48:59
One question though.
Since your table has data for each business day. Isnt it enough to just do below to get last business day detail for each month?


SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1

Of course if one of portfolio doesnt have data at last business day then it wont show up

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 02:20:11
Hi visakh16 - unfortunately neither of the codes give me the results I'm expecting.

With your first set of code, I seem to get the sum of every day during the month displayed as the last business day of the month, ie if Sum(Values) is 10 for each day the result is 310 as at 31-May-13 rather than just 10. I do however get a value (albeit incorrect) for the last business day of each month for each portfolio throughout the entire history of the table.

With your second piece of code, I don't actually get results for each of the months in the table by Portfolio. I get a result for each month, but there is only one result in each instance. eg for 31-May-13 I might get Portfolio ZZZ and for 30-Jun-13 I might get Portfolio YYY. It doesn't actually provide the last business day of the month either, so I see 30-Jun-13 rather than 28-Jun-13. However, the Sum(Values) figure does seem to be correct.


quote:
Originally posted by visakh16

One question though.
Since your table has data for each business day. Isnt it enough to just do below to get last business day detail for each month?


SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1

Of course if one of portfolio doesnt have data at last business day then it wont show up

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 03:02:21
Ok if your attempt is to get records for each portfolio use this

SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 03:07:40
Does the order of the PARTITION matter?


quote:
Originally posted by visakh16

Ok if your attempt is to get records for each portfolio use this

SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 03:28:24
One further question.
How can I filter out a PORTFOLIO which ceases to exist sometime during the month?
So for example, I have values for Portfolio ZZZ and YYY as at 31-May-13, but I also have a value for Portfolio AAA on 15-May-13 which is the day it ceased to exist. I only care about what exists as at 31-May-13. How can I ensure nothing appears in my results for anything other than the last business day of the month?



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 03:38:55
quote:
Originally posted by DanielS

Does the order of the PARTITION matter?


quote:
Originally posted by visakh16

Ok if your attempt is to get records for each portfolio use this

SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





Nope it wont but order in ORDER BY do matter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 03:41:08
quote:
Originally posted by DanielS

One further question.
How can I filter out a PORTFOLIO which ceases to exist sometime during the month?
So for example, I have values for Portfolio ZZZ and YYY as at 31-May-13, but I also have a value for Portfolio AAA on 15-May-13 which is the day it ceased to exist. I only care about what exists as at 31-May-13. How can I ensure nothing appears in my results for anything other than the last business day of the month?







SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1
AND FROM_DATE >= DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)-1
AND FROM_DATE < DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 03:47:27
Great, thank you.

Do you mind just explaining what the line
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
is actually doing?
So you're creating some sort of count sequence based on the Portfolio/Date and then later on referring to that sequence?


quote:
Originally posted by visakh16

quote:
Originally posted by DanielS

One further question.
How can I filter out a PORTFOLIO which ceases to exist sometime during the month?
So for example, I have values for Portfolio ZZZ and YYY as at 31-May-13, but I also have a value for Portfolio AAA on 15-May-13 which is the day it ceased to exist. I only care about what exists as at 31-May-13. How can I ensure nothing appears in my results for anything other than the last business day of the month?







SELECT FROM_DATE,PORTFOLIO,TotalValue
FROM
(
SELECT FROM_DATE,PORTFOLIO,SUM(SECURITY_VALUE) AS TotalValue,
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
FROM Table
GROUP BY FROM_DATE,PORTFOLIO
)t
WHERE Seq=1
AND FROM_DATE >= DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)-1
AND FROM_DATE < DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 03:53:33
quote:
Originally posted by DanielS

Great, thank you.

Do you mind just explaining what the line
ROW_NUMBER() OVER (PARTITION BY PORTFOLIO,DATEDIFF(mm,0,FROM_DATE) ORDER BY FROM_DATE DESC) AS Seq
is actually doing?
So you're creating some sort of count sequence based on the Portfolio/Date and then later on referring to that sequence?



yep...PARTITION BY divides table into groups based on columns PORTFOLIO and DATEDIFF(mm,0,FROM_DATE) (which is to get month from date). So for each portfolio each month it takes a group and orders the data inside it in descending order of FROM_DATE. Then applying Row_Number will number them as 1,2,3 etc with last date of month getting 1, previous 2 etc.
Then in outside query Seq=1 will ensure you always get last records of the month for each of the PORTFOLIO. this is then checked against date interval to make sure you take only ones as on 31st (or the last date of month)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DanielS
Starting Member

32 Posts

Posted - 2013-07-09 : 03:59:18
Cool, thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 04:10:10
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -