Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wanting first data from within a month... Expand / Collapse
Author
Message
Posted Thursday, May 15, 2008 5:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2008 5:03 PM
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
Post #501715
Posted Thursday, May 15, 2008 7:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:06 PM
Points: 438, Visits: 907
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.
Post #501729
Posted Thursday, May 15, 2008 11:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2008 5:03 PM
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
Post #501772
Posted Friday, May 16, 2008 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 16, 2010 1:27 AM
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

Post #501793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse