This gotta be simple ? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

This gotta be simple ?

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-30 : 01:07:23

table A(id int,value int,Date datetime)

ID   VALUE   DATE
1     34     3/20/2007
1     33     3/21/2007
1     34     3/22/2007
1     34     3/23/2007
2     40     3/20/2007
2     45     3/23/2007

I want to return only the records from both ID =1,2 where they have the dates in common.

Return

ID   VALUE   DATE
1     34     3/20/2007
1     34     3/23/2007
2     40     3/20/2007
2     45     3/23/2007

Extra question: Similar like above but if there are multiple records of a date, return only the record with latest datetime (assuming date is now smalldatetime format like 3/20/2007 12:23:45PM)

Thanks for your help. This forum has been the best one around.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-30 : 02:09:06

For the first part

SELECT ID, Value, Date
FROM A
WHERE (ID = 1 AND Date IN (SELECT Date FROM A WHERE ID = 2))
OR (ID = 2 AND Date IN (SELECT Date FROM A WHERE ID = 1))

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-30 : 06:01:12

select id,value,date from table1 where date in
(SELECT date from table1 where ID = 1 )
AND date IN
(SELECT date from table1 where ID = 2 )

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-10-30 : 07:34:58

DECLARE @T TABLE ( ID INT, VALUE INT, DATE DATETIME)

INSERT INTO @T
SELECT 1, 34, '3/20/2007' UNION ALL
SELECT 1, 33, '3/21/2007' UNION ALL
SELECT 1, 34, '3/22/2007' UNION ALL
SELECT 1, 34, '3/23/2007' UNION ALL
SELECT 2, 40, '3/20/2007' UNION ALL
SELECT 2, 45, '3/23/2007'

SELECT T.*
FROM @T T
INNER JOIN @T T2 ON T2.DATE = T.DATE
AND T2.ID <> T.ID

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-30 : 12:44:36

thanks for all the suggestions. You guys been wonderful. I got it working after some tweaks

I put together something that works well using SQL analyzer but when I put them into VBA code to query directly, it bombs with error runtime error '3704': 'Operation is not allowed when the object is closed'

DECLARE @T1 TABLE ( ID INT, bid float,offer float,reference float, QuoteDATE DATETIME)
Insert into @T1

Select T.entityId,T.bid, T.offer, T.reference ,T.QuoteDate
from cdsQuotes T
Join ( Select max(QuoteDateTime) MCount, QuoteDate
FROM cdsQuotes T1
where T1.entityId = 6759 and T1.Term = 5 and T1.sourceId = 7
GROUP BY T1.QuoteDate
) T2 ON T.QuoteDate = T2.QuoteDate And T.QuoteDateTime = T2.MCount
And T.entityId = 6759 and T.term = 5 and T.sourceId = 7
order by T.quoteDate

DECLARE @T2 TABLE ( ID INT, bid float,offer float,reference float, QuoteDATE DATETIME)
Insert into @T2
Select T.entityId, T.bid, T.offer, T.reference ,T.QuoteDate
from cdsQuotes T
Join ( Select max(QuoteDateTime) MCount, QuoteDate
FROM cdsQuotes T1
where T1.entityId = 7858 and T1.Term = 5 and T1.sourceId = 7
GROUP BY T1.QuoteDate
) T2 ON T.QuoteDate = T2.QuoteDate And T.QuoteDateTime = T2.MCount
And T.entityId = 7858 and T.term = 5 and T.sourceId = 7
order by T.quoteDate

SELECT T1.ID,T1.bid, T1.offer,T1.reference,T2.ID,T2.bid, T2.offer,T2.reference,T1.QuoteDate
FROM @T1 T1
inner join @T2 T2 on
T1.QuoteDate = T2.QuoteDate And
T1.ID <> T2.ID

I'm not sure if this a good place to ask VBA but maybe VBA does not like the code ?

SQLStr = SQLStr & "DECLARE @T1 TABLE ( ID INT, bid float,offer float,reference float, QuoteDATE DATETIME)" & vbLf
SQLStr = SQLStr & "Insert into @T1 " & vbLf
SQLStr = SQLStr & "Select T.entityId,T.bid, T.offer, T.reference ,T.QuoteDate " & vbLf
SQLStr = SQLStr & "from cdsQuotes T" & vbLf
SQLStr = SQLStr & "Join ( Select max(QuoteDateTime) MCount, QuoteDate " & vbLf
SQLStr = SQLStr & "FROM cdsQuotes T1 " & vbLf
SQLStr = SQLStr & "where T1.entityId = " & entityId1 & " and T1.Term = " & term1 & " and T1.sourceId = " & sourceId1 & " " & vbLf
SQLStr = SQLStr & "GROUP BY T1.QuoteDate " & vbLf
SQLStr = SQLStr & ") T2 ON T.QuoteDate = T2.QuoteDate And T.QuoteDateTime = T2.MCount " & vbLf
SQLStr = SQLStr & "And T.entityId = " & entityId1 & " and T.term = " & term1 & " and T.sourceId = " & sourceId1 & " " & vbLf
SQLStr = SQLStr & "order by T.quoteDate " & vbLf

SQLStr = SQLStr & "DECLARE @T2 TABLE ( ID INT, bid float,offer float,reference float, QuoteDATE DATETIME)" & vbLf
SQLStr = SQLStr & "Insert into @T2 " & vbLf
SQLStr = SQLStr & "Select T.entityId,T.bid, T.offer, T.reference ,T.QuoteDate " & vbLf
SQLStr = SQLStr & "from cdsQuotes T" & vbLf
SQLStr = SQLStr & "Join ( Select max(QuoteDateTime) MCount, QuoteDate " & vbLf
SQLStr = SQLStr & "FROM cdsQuotes T1 " & vbLf
SQLStr = SQLStr & "where T1.entityId = " & entityId2 & " and T1.Term = " & term2 & " and T1.sourceId = " & sourceId2 & " " & vbLf
SQLStr = SQLStr & "GROUP BY T1.QuoteDate " & vbLf
SQLStr = SQLStr & ") T2 ON T.QuoteDate = T2.QuoteDate And T.QuoteDateTime = T2.MCount " & vbLf
SQLStr = SQLStr & "And T.entityId = " & entityId2 & " and T.term = " & term2 & " and T.sourceId = " & sourceId2 & " " & vbLf
SQLStr = SQLStr & "order by T.quoteDate " & vbLf

SQLStr = SQLStr & "SELECT T1.ID,T1.bid, T1.offer,T1.reference,T2.ID,T2.bid, T2.offer,T2.reference,T1.QuoteDate " & vbLf
SQLStr = SQLStr & "FROM @T1 T1 " & vbLf
SQLStr = SQLStr & "inner join @T2 T2 on " & vbLf
SQLStr = SQLStr & "T1.QuoteDate = T2.QuoteDate And " & vbLf
SQLStr = SQLStr & "T1.ID <> T2.ID " & vbLf

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 02:26:48

Why dont you make use stored procedure than concatenating the sql at front end?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources