SQL Query Help - SQL Server Forums

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

SQL Query Help

Author

Topic

balilk4u
Starting Member

3 Posts

Posted - 2010-12-02 : 12:00:40

Hi peeps,

Hope all is going well.

Right I have some serious issue that I am not able to sort out myself.

Basically, I get data from different tables with the join methods.
It all works well but when it returns the result, I also get two records which have the same stockName but different dates. i do not want it to return multiple records which have two different dates. Instead I want it to return only one, whose future date is closer to todays date.

My SQL query is shown below:

SELECT Stock.StockName,(CASE WHEN CONVERT(VARCHAR, EffectiveDate, 103) >= CONVERT(VARCHAR, GETDATE(), 103) THEN CONVERT(VARCHAR, EffectiveDate, 103) ELSE ' ' END) AS Corprate_Action_Date, (CASE WHEN StockData.NextExDate >= GETDATE() THEN CONVERT(VARCHAR, StockData.NextExDate, 103) ELSE ' none' END) AS Next_Ex_Date, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType, StockData.PayoutRatio, CONVERT(VARCHAR, StockData.EarningsDate, 103) AS Earnings_Date, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate
FROM Stock INNER JOIN PortfolioVersion ON PortfolioVersion.StockId = Stock.StockId LEFT OUTER JOIN Circular ON Circular.StockId = Stock.StockId LEFT OUTER JOIN StockData ON Stock.StockId = StockData.StockId WHERE(PortfolioVersion.AccountId = '2f945979-8ab4-494f-8b04-102652a71b04') AND (PortfolioVersion.VersionNumber = '9') AND (StockData.DateModified >= '2010-12-01 00:00:00')
GROUP BY PortfolioVersion.VersionNumber, Stock.StockName, StockData.NextExDate, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType,StockData.PayoutRatio, StockData.EarningsDate, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate, Circular.EffectiveDate

Like I said, the above query works like a gem, but I just need to tweek it so when i get the result, I do not get multiple stocks. Only return one, whos date is closest to todays date.

The result that I currently get is shown below:

ADIDAS 07/12/2010 06/05/2011 EUR 0.650000 Projected 29.80% 02/03/2011 NULL NULL
ADIDAS 09/12/2010 06/05/2011 EUR 0.650000 Projected 29.80% 02/03/2011 NULL NULL
CLP HOLDINGS LTD 02/12/2010 HKD 0.520000 3rd Interim 72.80% 25/02/2011 NULL NULL
General Mills Inc 07/01/2011 USD 0.310000 Projected 42.06% 16/12/2010 NULL NULL
GIVAUDAN N 29/03/2011 CHF 21.600000 Projected 87.96% 08/02/2011 NULL NULL
SUN HUNG KAI PROPERTIES LTD 24/03/2011 HKD 0.850000 Projected 24.73% 11/03/2011 Annual Shareholder 02/12/2010
WEYERHAEUSER CO 26/01/2011 USD 0.120000 Projected NULL 04/02/2011 NULL NULL
WOLSELEY Ldt 23/03/2011 GBp 5.000000 Projected NULL 22/03/2011 NULL NULL

The above has two records of ADIDAS, with two different dates. One has 07/12/2010 and the only 09/12/2010. I only want the 07/12/2010 to show in this list since it is closer to todays date, along with the rest of the result. Therefore, this result should only consist of 7 in total. Not 8.

Thanks guys

Take Care

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 12:06:33

without looking at what this is doing something like

select * from (
select *, seq = ROW_NUMBER() over (partition by StockName order by abs(datediff(dd,Corprate_Action_Date,getdate()))
from (
SELECT Stock.StockName,(CASE WHEN CONVERT(VARCHAR, EffectiveDate, 103) >= CONVERT(VARCHAR, GETDATE(), 103) THEN CONVERT(VARCHAR, EffectiveDate, 103) ELSE ' ' END) AS Corprate_Action_Date, (CASE WHEN StockData.NextExDate >= GETDATE() THEN CONVERT(VARCHAR, StockData.NextExDate, 103) ELSE ' none' END) AS Next_Ex_Date, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType, StockData.PayoutRatio, CONVERT(VARCHAR, StockData.EarningsDate, 103) AS Earnings_Date, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate
FROM Stock INNER JOIN PortfolioVersion ON PortfolioVersion.StockId = Stock.StockId LEFT OUTER JOIN Circular ON Circular.StockId = Stock.StockId LEFT OUTER JOIN StockData ON Stock.StockId = StockData.StockId WHERE(PortfolioVersion.AccountId = '2f945979-8ab4-494f-8b04-102652a71b04') AND (PortfolioVersion.VersionNumber = '9') AND (StockData.DateModified >= '2010-12-01 00:00:00')
GROUP BY PortfolioVersion.VersionNumber, Stock.StockName, StockData.NextExDate, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType,StockData.PayoutRatio, StockData.EarningsDate, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate, Circular.EffectiveDate
) a
) b
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

balilk4u
Starting Member

3 Posts

Posted - 2010-12-02 : 12:46:27

Hi nigelrivett, I do not quite understand what you have written?

I get lots of exceptions when I just copy and paste the query you have written.

Please could you advice.

Thanks

balilk4u
Starting Member

3 Posts

Posted - 2010-12-03 : 09:42:41

Hi nigelrivett, I tried your query but i get no results at all?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 09:50:04

does this return anything?
I thik I missed out a bracket at the end of the rownumber over clause.
If it works try adding the bracket to the above query

select *, seq = ROW_NUMBER() over (partition by StockName order by abs(datediff(dd,Corprate_Action_Date,getdate())))
from (
SELECT Stock.StockName,(CASE WHEN CONVERT(VARCHAR, EffectiveDate, 103) >= CONVERT(VARCHAR, GETDATE(), 103) THEN CONVERT(VARCHAR, EffectiveDate, 103) ELSE ' ' END) AS Corprate_Action_Date, (CASE WHEN StockData.NextExDate >= GETDATE() THEN CONVERT(VARCHAR, StockData.NextExDate, 103) ELSE ' none' END) AS Next_Ex_Date, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType, StockData.PayoutRatio, CONVERT(VARCHAR, StockData.EarningsDate, 103) AS Earnings_Date, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate
FROM Stock INNER JOIN PortfolioVersion ON PortfolioVersion.StockId = Stock.StockId LEFT OUTER JOIN Circular ON Circular.StockId = Stock.StockId LEFT OUTER JOIN StockData ON Stock.StockId = StockData.StockId WHERE(PortfolioVersion.AccountId = '2f945979-8ab4-494f-8b04-102652a71b04') AND (PortfolioVersion.VersionNumber = '9') AND (StockData.DateModified >= '2010-12-01 00:00:00')
GROUP BY PortfolioVersion.VersionNumber, Stock.StockName, StockData.NextExDate, StockData.DividendCcy, StockData.DividendAmt, StockData.DividendType,StockData.PayoutRatio, StockData.EarningsDate, StockData.NextCorpMeetingType, StockData.NextCorpMeetingDate, Circular.EffectiveDate
) a

if not what is the error?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources