sub query error - 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

sub query error

Author

Topic

maqza
Starting Member

7 Posts

Posted - 2011-02-15 : 01:47:55

HI!
i have a problem in sql sub query it is showing multple records
SELECT MAX(dbo.VLnAssHis.VlnDte) AS vdated, dbo.VLnAssHis.FndCde, dbo.VLnAssHis.MktCde, dbo.VLnAssHis.SymCde, dbo.VLnAssHis.OwnBal,
dbo.SymHis.MktPrc, dbo.SymHis.PrcDte
FROM dbo.VLnAssHis INNER JOIN
dbo.SymHis ON dbo.VLnAssHis.InsTyp = dbo.SymHis.InsTyp AND dbo.VLnAssHis.SymCde = dbo.SymHis.SymCde AND
dbo.VLnAssHis.VlnDte < dbo.SymHis.PrcDte
WHERE (dbo.VLnAssHis.FndCde = '200') AND (dbo.VLnAssHis.SymCde = '2010') AND (dbo.SymHis.PrcDte = CONVERT(DATETIME, '2010-08-04 00:00:00', 102))
GROUP BY dbo.VLnAssHis.FndCde, dbo.VLnAssHis.MktCde, dbo.VLnAssHis.SymCde, dbo.VLnAssHis.OwnBal, dbo.SymHis.SymCde, dbo.SymHis.MktPrc,
dbo.SymHis.PrcDte

****************
result showing multiple records instead of one

8/5/2004 12:00:00 AM 200 SA 2010 6500.0000000 88.2500000 8/4/2010 12:00:00 AM

8/12/2004 12:00:00 AM 200 SA 2010 9545.0000000 88.2500000 8/4/2010 12:00:00 AM

8/19/2004 12:00:00 AM 200 SA 2010 9895.0000000 88.2500000 8/4/2010 12:00:00 AM

10/21/2004 12:00:00 AM 200 SA 2010 10459.0000000 88.2500000 8/4/2010 12:00:00 AM

9/2/2004 12:00:00 AM 200 SA 2010 10795.0000000 88.2500000 8/4/2010 12:00:00 AM

9/23/2004 12:00:00 AM 200 SA 2010 11095.0000000 88.2500000 8/4/2010 12:00:00 AM

10/14/2004 12:00:00 AM 200 SA 2010 11459.0000000 88.2500000 8/4/2010 12:00:00 AM

10/28/2004 12:00:00 AM 200 SA 2010 11974.0000000 88.2500000 8/4/2010 12:00:00 AM

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 02:57:08

Check this if works?

SELECT
MAX(dbo.VLnAssHis.VlnDte) AS vdated
,dbo.VLnAssHis.FndCde
,dbo.VLnAssHis.MktCde
,dbo.VLnAssHis.SymCde
,dbo.VLnAssHis.OwnBal
,dbo.SymHis.MktPrc
,dbo.SymHis.PrcDte
FROM dbo.VLnAssHis
INNER JOIN dbo.SymHis
ON dbo.VLnAssHis.InsTyp = dbo.SymHis.InsTyp AND dbo.VLnAssHis.SymCde = dbo.SymHis.SymCde
AND dbo.VLnAssHis.VlnDte < dbo.SymHis.PrcDte
WHERE (dbo.VLnAssHis.FndCde = '200') AND (dbo.VLnAssHis.SymCde = '2010')
AND (dbo.SymHis.PrcDte = CONVERT(DATETIME, '2010-08-04 00:00:00', 102))
GROUP BY ,dbo.VLnAssHis.FndCde,dbo.VLnAssHis.MktCde,dbo.VLnAssHis.SymCde,dbo.VLnAssHis.OwnBal,dbo.SymHis.MktPrc,dbo.SymHis.PrcDte

If not then come up with some sample data from both tables (dbo.VLnAssHis and dbo.SymHis) and the desired output as per Sampled Data.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources