T-SQL issue?? or do I have to UDF
-
My Prob - I have a Transactional table (Meeting Data - 1 mill) storing MeetingNo, MeetingDate, KPI_1, KPI_2 ...
I need to select MeetingNo, MeetingDate, KPI_1, KPI_2 for a date range, as well as KPI_1, KPI_2 for the previous Date of each meeting.
I can do with UDF's but Soooo slow. Any T-SQL which can also do this? Stumped !!
-
I used function's to do something similar. However I also made shure that the table was properly indexed and also made shure that in the select statement I never put a function in front of the indexed column being scanned. ie
Select stuff from table
where convert(datetime,MeetingDate) ='whaterver' ---This will hide the index and the index will not be used. Also do a DBCC showcontig on the table to determine if the Scan Density is good.
Andy. -
If you want to have both sets of data in the same row then you have to join the table to itself.
Performance will depend of several factors.
Correct Indexing
Dates having no time portion
Rows for all prior dates exist
If the above is satisfied then
SELECT a.MeetingNo, a.MeetingDate, a.KPI_1, a.KPI_2, b.KPI_1, b.KPI_2
FROM
a
INNER JOIN
b
ON b.MeetingNo = a.MeetingNo
AND b.MeetingDate = a.MeetingDate - 1
If there are gaps in the dates then
SELECT a.MeetingNo, a.MeetingDate, a.KPI_1, a.KPI_2, b.KPI_1, b.KPI_2
FROM
a
INNER JOIN
b
ON b.MeetingNo = a.MeetingNo
AND b.MeetingDate =
(SELECT MAX(c.MeetingDate) as [MeetingDate] FROM [#t] c WHERE c.MeetingDate < a.MeetingDate)
But this would involve sql creating and using a temp table. I do not know of a way round this even if you created the temp table yourself.
If the dates contain time portion then the only solution I can think of is to create a temp table, insert all the data (removing time from dates) and using that table in the above queries.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply