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.