May 15, 2008 at 5:30 pm
Good Morning,
I am sure there is an easy solution to this problem, but since I have been looking at it for sometime I am possibly unable to find the easy solution for this.
I am returning a list of vehicles from a table. I am also joining that table with the Odometer reading for that vehicle within a given monthly period. Oct 2007 or Apr 2008. There can be multiple odometer reading within a month, so what I am after is the First reading for a car in that month, and the first reading for a car in the next month.
[font="Courier New"]SELECT REGISTRATION_NO, ODO_READING_KM as NextReading, READING_DATE, TRANSACTION_MONTH
FROM COM_ODO_READINGS
WHERE TRANSACTION_MONTH BETWEEN DATEADD(MONTH, 1, CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME)) AND DATEADD(DAY, -1, DATEADD(MONTH, 2 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))
Order BY REGISTRATION_NO[/font]
This is the query that returns the Next reading User supplies Oct 2007 as the month and year, it will use Nov 2007 as the month to use.
[font="Courier New"]
SELECTREGISTRATION_NO AS, ODO_READING_KM as PreviousReading, READING_DATE as ReadingDate, TRANSACTION_MONTH
FROMCOM_ODO_READINGS
WHERETRANSACTION_MONTH BETWEEN CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME) AND DATEADD(DAY, -1, DATEADD(MONTH, +1 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))
Order By REGISTRATION_NO[/font]
The user enters the parameters from Reporting Services and as two separate entries, Month and Year (Business specified this method). Now, these queries will return the same vehicle more than once if there were more than 1 odometer reading taken and entered for that month selected. It is rare and there are specific business cases that would warrant this to occur.
Like I mentioned that I am sure there is a simple solution for this and for me looking at this problem for some time now, well several hours, it seems to allude me. Your help with this would be useful. If you need more information please let me know.
Kind Regards
Michael Rogers
May 15, 2008 at 7:13 pm
michael.rogers (5/15/2008)
[font="Courier New"]SELECT REGISTRATION_NO, ODO_READING_KM as NextReading, READING_DATE, TRANSACTION_MONTHFROM COM_ODO_READINGS
WHERE TRANSACTION_MONTH BETWEEN DATEADD(MONTH, 1, CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME)) AND DATEADD(DAY, -1, DATEADD(MONTH, 2 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))
Order BY REGISTRATION_NO
[/font]This is the query that returns the Next reading User supplies Oct 2007 as the month and year, it will use Nov 2007 as the month to use.
[font="Courier New"]
SELECTREGISTRATION_NO AS, ODO_READING_KM as PreviousReading, READING_DATE as ReadingDate, TRANSACTION_MONTH
FROMCOM_ODO_READINGS
WHERETRANSACTION_MONTH BETWEEN CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME) AND DATEADD(DAY, -1, DATEADD(MONTH, +1 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))
Order By REGISTRATION_NO[/font]
give this try:
declare @date smalldatetime
set @date = cast(cast((@StatusDateYear * 10000) + (@StatusDateMonth * 100) + 1 as varchar(8)) as smalldatetime)
; with REGS as
( SELECTREGISTRATION_NO, ODO_READING_KM, READING_DATE as ReadingDate, TRANSACTION_MONTH,
MONTH(TRANSACTION_MONTH) as month,
row_number() over (partition by month(TRANSACTION_MONTH) order by REGISTRATION_NO) as seq
FROMCOM_ODO_READINGS
WHERETRANSACTION_MONTH >=
@date and TRANSACTION_MONTH < dateadd( month, 2, @date)
)
select * from REGS as A where seq = 1
the final select should have two rows with each row being the first entry for the month.
May 15, 2008 at 11:13 pm
Thank you so much for your reply. I did try what you gave me and it reduced my total result set. Not long after that I managed to get the SQL to work, but Selecting the Reading Dates for the Cars for the selected Month and Next Month using
[font="Courier New"]Select O.Registration_No, Min(O.READING_DATE) as ReadingDate
from COM_ODO_READINGS O
WHERE Month(o.READING_DATE) = @StatusDateMonth and Year(o.READING_DATE) = @StatusDateYear
Group By O.Registration_No[/font]
The other query is the same except the WHERE clause has the next month. This gave me, as you can see, a Registration Number and the First Date for the Selected Month and Year.
I used this result set joined to the list of cars to get the reading based on those two dates, where the Reading dates were equal with that Registration Number, which was the exact reading I was after.
Once again, thank you for your help and the answer you gave, certainly gave me a better understanding of using the OVER and PARTITION BY.
Kind Regards
Michael Rogers
May 16, 2008 at 12:24 am
michael.rogers (5/15/2008)
WHERE Month(o.READING_DATE) = @StatusDateMonth and Year(o.READING_DATE) = @StatusDateYear
Functions on a column (READING_DATE) will prevent an index from being used and as your data grows your query will become slower and slower.
Rather do something like this
declare @stdt datetime, @endt datetime
select @stdt=convert(char(8), @StatusDateYear*10000+@StatusDateMonth*100+1)
,@endt=dateadd(mm, datediff(mm,'20000101',@stdt)+1, '20000101')
Now you can select
where READING_DATE>=@stdt
and READING_DATE<@endt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply