Rating in Consecutive Months Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rating in Consecutive Months Problem

Author

Topic

racingrob
Starting Member

4 Posts

Posted - 2008-07-21 : 11:56:47

Been looking it this forum and there are a couple of examples that are close to what i need but i thought that i would post this and see if somebody can ease my pain.

This is my source data,

DECLARE @mytable TABLE(fkSellerID int, ReportDate datetime, Rating varchar(5))
INSERT INTO @mytable
SELECT 123456, '2008-02-01', 'RED' UNION ALL
SELECT 123456, '2008-03-01', 'RED' UNION ALL
SELECT 123456, '2008-04-01', 'RED' UNION ALL
SELECT 123456, '2008-05-01', 'RED' UNION ALL
SELECT 234567, '2008-01-01', 'AMBER' UNION ALL
SELECT 234567, '2008-02-01', 'RED' UNION ALL
SELECT 234567, '2008-03-01', 'AMBER' UNION ALL
SELECT 234567, '2008-04-01', 'RED' UNION ALL
SELECT 234567, '2008-05-01', 'RED' UNION ALL
SELECT 345678, '2008-02-01', 'RED' UNION ALL
SELECT 345678, '2008-03-01', 'AMBER' UNION ALL
SELECT 345678, '2008-04-01', 'AMBER' UNION ALL
SELECT 345678, '2008-05-01', 'AMBER'

SELECT *
FROM @mytable
and what I am after is @ MAX(ReportDate) or any ReportDate that i choose.

fkSellerID, Lastest Rating, Number of Months at Rating,
123456, 'RED', 4,
234567, 'RED', 2,
345678, 'AMBER', 3,

Hope that makes sense, any help gratefully recived.

Thanks in advance.

Rob

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-21 : 12:56:00

Is the data always on the 1st of the month, or can it be on any day?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-21 : 13:14:08

Here's my first stab...

declare @ReportDate datetime
set @ReportDate = '2008-05-01' --or select @ReportDate = max(ReportDate) from @mytable

select
    a.fkSellerID,
    max(a.Rating) as [Lastest Rating],
    datediff(mm, coalesce(max(case when a.Rating <> b.Rating then b.ReportDate end),
        dateadd(mm, -1, min(b.ReportDate))), @ReportDate) as [Number of Months at Rating]
from (select fkSellerID, ReportDate, Rating from @mytable where ReportDate = @ReportDate) a
    left outer join @mytable b on a.fkSellerID = b.fkSellerID and a.ReportDate >= b.ReportDate
group by a.fkSellerID

Assumptions (okay or not?):

1. Data is always on the 1st of the month
2. There is data on @ReportDate for all sellers you want

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

racingrob
Starting Member

4 Posts

Posted - 2008-07-21 : 13:16:42

always the 1st of the month. It is a monthly report.

racingrob
Starting Member

4 Posts

Posted - 2008-07-21 : 13:20:27

thanks Ryan. I will have a play with your code in the morning. I am at home now. Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:54:56

[code]SELECT fkSellerID,Rating,COUNT(MonthVal)
FROM
(
SELECT fkSellerID,Rating,
MONTH(Reportdate) AS MonthVal,YEAR(Reportdate) AS YearVal
FROm YourTable
WHERE Reportdate <=@DateParam
GROUP BY fkSellerID,Rating,MONTH(Reportdate),YEAR(Reportdate)
)t
GROUP BY fkSellerID,Rating[/code]

racingrob
Starting Member

4 Posts

Posted - 2008-07-22 : 06:26:12

Ryan you're a star that is perfect, and I understand what you have done which is always a bonus.
visakh16 thanks for your input, it was the consecutive part that i was struggling with and i don't think that your solution picked that bit up.

Thanks guys

Rob

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-22 : 06:54:07

Thanks Rob

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources