Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wanting first data from within a month...


Wanting first data from within a month...

Author
Message
michael.rogers
michael.rogers
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 21
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.

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


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.

SELECT REGISTRATION_NO AS, ODO_READING_KM as PreviousReading, READING_DATE as ReadingDate, TRANSACTION_MONTH
FROM COM_ODO_READINGS
WHERE TRANSACTION_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


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
antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 921
michael.rogers (5/15/2008)

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


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.

SELECT REGISTRATION_NO AS, ODO_READING_KM as PreviousReading, READING_DATE as ReadingDate, TRANSACTION_MONTH
FROM COM_ODO_READINGS
WHERE TRANSACTION_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




give this try:

declare @date smalldatetime
set @date = cast(cast((@StatusDateYear * 10000) + (@StatusDateMonth * 100) + 1 as varchar(8)) as smalldatetime)

; with REGS as
( SELECT REGISTRATION_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
FROM COM_ODO_READINGS
WHERE TRANSACTION_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.
michael.rogers
michael.rogers
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 21
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

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


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
PDreyer
PDreyer
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 64
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search