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.
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 MyTablewhere DATEPART(day,FROM_DATE) = 7group by FROM_DATE, PORTFOLIOI 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. |
|
|
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 VALUE27/06/2013 ZZZ A 2.5627/06/2013 ZZZ B 9.6927/06/2013 ZZZ C 1.2327/06/2013 ZZZ D 1.8827/06/2013 YYY A 7.6127/06/2013 YYY B 7.0027/06/2013 YYY C 6.4127/06/2013 YYY D 8.0828/06/2013 ZZZ A 9.8928/06/2013 ZZZ B 3.6328/06/2013 ZZZ C 6.3428/06/2013 ZZZ D 4.0528/06/2013 YYY A 1.6028/06/2013 YYY B 3.1228/06/2013 YYY C 1.3928/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.
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-09 : 01:20:27
|
Try this:[CODE]select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTablewhere 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] |
|
|
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))%7WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one dayWHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two daysELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1) END, PORTFOLIO, SUM(VALUES) from MyTablegroup by CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one dayWHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two daysELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1) END, PORTFOLIO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1 Of course if one of portfolio doesnt have data at last business day then it wont show up------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1 Of course if one of portfolio doesnt have data at last business day then it wont show up------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
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 thisSELECT FROM_DATE,PORTFOLIO,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 thisSELECT FROM_DATE,PORTFOLIO,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
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? |
|
|
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 thisSELECT FROM_DATE,PORTFOLIO,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Nope it wont but order in ORDER BY do matter------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1AND FROM_DATE >= DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)-1AND FROM_DATE < DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 Seqis 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,TotalValueFROM(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 SeqFROM TableGROUP BY FROM_DATE,PORTFOLIO)tWHERE Seq=1AND FROM_DATE >= DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0)-1AND FROM_DATE < DATEADD(mm,DATEDIFF(mm,0,FROM_DATE)+1,0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
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 Seqis 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
DanielS
Starting Member
32 Posts |
Posted - 2013-07-09 : 03:59:18
|
Cool, thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-09 : 04:10:10
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|