Possible to do this in T-SQL or stored procedure ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Possible to do this in T-SQL or stored procedure ?

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-25 : 16:08:08

Hi again,
I have a table named Quote which already ordered by Date, sourceId.
I'd like to get T-SQL syntax or stored procedures that will look through the table and return based on the following criterias

1) Look through each day and pick out the quotes by specific sourceId. The sourceId ranges from 1-100 and I'm only interested
in sourceId 11,14,36 and the latest quote from any of the remaining sourceId

2) For example, on 3/20/2007, I don't have anything from 11,14 or 36. I would then pick the latest quotes
103 105 18 3/20/2007

3) On 3/21/2007, I have quotes from 11,14 and others. There are 2 quotes from 14 so I would pick the later one.There are 2 from
19 and i would pick the later one as well. At the end of the day, I would have one quote each from 11,14 and 19

98 103 11 3/21/2007
97 103 14 3/21/2007
97 103 19 3/21/2007

And so on...
The goal is to be able to pick daily quotes from 11,14,36 or the latest from the rest of the source.
If there are multiples quotes from the same source for a single day, pick the latest quote.

Is this possible to do ? I hope my explaination is clear and precise.

Thanks

Bid Offer SourceId Date Time

99 103 2 3/20/2007 11:58:00 AM
100 103 4 3/20/2007 12:34:00 PM
99 102 5 3/20/2007 1:12:00 PM
99 102 6 3/20/2007 2:20:00 PM
99 102 15 3/20/2007 4:15:00 PM
103 105 15 3/20/2007 6:51:00 PM
103 105 18 3/20/2007 8:46:00 PM
98 103 11 3/21/2007 12:21:00 PM
99 104 14 3/21/2007 2:24:00 AM
97 103 14 3/21/2007 11:29:00 AM
97 103 19 3/21/2007 11:49:00 AM
97 103 19 3/21/2007 1:22:00 PM
99 103 2 3/22/2007 1:38:00 PM
100 104 11 3/22/2007 2:23:00 PM
100 104 14 3/22/2007 4:51:00 PM
101 104 36 3/22/2007 4:54:00 PM
101 104 9 3/23/2007 6:30:00 PM
103 106 11 3/23/2007 6:46:00 PM
99 105 12 3/23/2007 11:34:00 PM
103 105 14 3/23/2007 9:23:00 PM
97 104 36 3/23/2007 12:35:00 PM
97 104 36 3/23/2007 2:34:00 PM

......................

Bid Offer SourceId Date

103 105 18 3/20/2007
98 103 11 3/21/2007
97 103 14 3/21/2007
97 103 19 3/21/2007
99 103 2 3/22/2007
100 104 11 3/22/2007
100 104 14 3/22/2007
101 104 36 3/22/2007
103 106 11 3/23/2007
99 105 12 3/23/2007
103 105 14 3/23/2007
97 104 36 3/23/2007

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-26 : 13:30:19

Try this:

DECLARE @Quote TABLE(Bid INT, Offer INT, SourceId INT, Date DATETIME)

INSERT @Quote
SELECT 99, 103, 2, '3/20/2007 11:58:00 AM'
UNION ALL SELECT 100, 103, 4, '3/20/2007 12:34:00 PM'
UNION ALL SELECT 99, 102, 5, '3/20/2007 1:12:00 PM'
UNION ALL SELECT 99, 102, 6, '3/20/2007 2:20:00 PM'
UNION ALL SELECT 99, 102, 15, '3/20/2007 4:15:00 PM'
UNION ALL SELECT 103, 105, 15, '3/20/2007 6:51:00 PM'
UNION ALL SELECT 103, 105, 18, '3/20/2007 8:46:00 PM'
UNION ALL SELECT 98, 103, 11, '3/21/2007 12:21:00 PM'
UNION ALL SELECT 99, 104, 14, '3/21/2007 2:24:00 AM'
UNION ALL SELECT 97, 103, 14, '3/21/2007 11:29:00 AM'
UNION ALL SELECT 97, 103, 19, '3/21/2007 11:49:00 AM'
UNION ALL SELECT 97, 103, 19, '3/21/2007 1:22:00 PM'
UNION ALL SELECT 99, 103, 2, '3/22/2007 1:38:00 PM'
UNION ALL SELECT 100, 104, 11, '3/22/2007 2:23:00 PM'
UNION ALL SELECT 100, 104, 14, '3/22/2007 4:51:00 PM'
UNION ALL SELECT 101, 104, 36, '3/22/2007 4:54:00 PM'
UNION ALL SELECT 101, 104, 9, '3/23/2007 6:30:00 PM'
UNION ALL SELECT 103, 106, 11, '3/23/2007 6:46:00 PM'
UNION ALL SELECT 99, 105, 12, '3/23/2007 11:34:00 PM'
UNION ALL SELECT 103, 105, 14, '3/23/2007 9:23:00 PM'
UNION ALL SELECT 97, 104, 36, '3/23/2007 12:35:00 PM'
UNION ALL SELECT 97, 104, 36, '3/23/2007 2:34:00 PM'


SELECT 
	*
FROM
	(
		SELECT 
			Bid,
			Offer,
			SourceID,
			Date
		FROM
			(
				SELECT 
					Bid,
					Offer,
					SourceID,
					Date,
					ROW_NUMBER() OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0), SourceID ORDER BY Date DESC) AS RowNum
				FROM 
					@Quote
				WHERE 
					SourceID IN (11, 14, 36) 
			) AS A
		WHERE
			RowNum = 1

		UNION 

		SELECT 
			Bid,
			Offer,
			SourceID,
			Date
		FROM
			(
				SELECT 
					Bid,
					Offer,
					SourceID,
					Date,
					ROW_NUMBER() OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0) ORDER BY Date DESC) AS RowNum
				FROM 
					@Quote
				WHERE 
					SourceID NOT IN (11, 14, 36) 
			) AS B
		WHERE
			RowNum = 1
	) AS Temp
ORDER BY
	DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0),
	SourceID

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-27 : 15:55:48

Lamprey,
That's one piece work of art. I learn a lot. Thanks for the very quick response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources