Group Ranking #2

Here is a try. I created a view to do it readable, you can substitute it by
its definition if you one just one #$%^@.&* shot.
use tempdb;
go
create table t(
invoice int not null unique,
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
case
when (select count(distinct d.paidontime) from t as d) > 1 then
coalesce(max(b.invoice), a.invoice - 1)
else 1
end
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"mekim" wrote:

> Hi All,
> I have a table that I wish to group by the change or switch in the
> "PaidOnTime" column while sequenced by descending invoice #
> I have read a bunch of gr8 articles that describe this situation...
> i.e.
> http://www.databasejournal.com/feat...10894_2244821_2
> http://support.microsoft.com/defaul...b;EN-US;q186133
> however this is with a twist...I don't want to group or number "Groups" -
I
> want to group the "Switch" in the groups.. (I'm trying my best not to make
> this sound confusing - one could only imagine if I wasn't trying :-)
> Anyway - here's sample data
> Input
> --
> Invoice PaidOnTime
> 1006 Yes
> 1005 Yes
> 1004 No
> 1003 Yes
> 1002 Yes
> 1001 Yes
> 1000 No
> -- RIGHT Output :-D
> Group Invoice PaidOnTime
> 1 1006 Yes
> 1 1005 Yes
> 2 1004 No
> 3 1003 Yes
> 3 1002 Yes
> 3 1001 Yes
> 4 1000 No
>
> The below is the best I can get :-(
> -- Wrong Output Since it Groups by PaidOnTime and not the "Switching" of
> PaidOnTime
> Group Invoice PaidOnTime
> 1 1006 Yes
> 1 1005 Yes
> 2 1004 No
> 1 1003 Yes
> 1 1002 Yes
> 1 1001 Yes
> 2 1000 No
>
> Best Regards,
> Mekim
> p.s. Excuse the dbl post plzCorrection,
use tempdb;
go
create table t(
invoice int not null unique,
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
case
when exists(select * from t as c where c.invoice < a.invoice and
c.paidontime <> a.paidontime) then max(b.invoice)
else -1
end
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select * from my_view
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"Alejandro Mesa" wrote:
> Here is a try. I created a view to do it readable, you can substitute it b
y
> its definition if you one just one #$%^@.&* shot.
> use tempdb;
> go
> create table t(
> invoice int not null unique,
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> case
> when (select count(distinct d.paidontime) from t as d) > 1 then
> coalesce(max(b.invoice), a.invoice - 1)
> else 1
> end
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "mekim" wrote:
>|||u r - Alejandro-mazing!!! - it works EXACTLY the way I need to
I know my "posting" was not the clearest - so thx for figuring that out also
Alejandro - I can understand basically "what u did - I'm just not so certain
"how u did" it - kind of intense - but I am certainly putting the effort
into deciphering it
My Very Much Thx!!!
Mekim
"Alejandro Mesa" wrote:
> Correction,
> use tempdb;
> go
> create table t(
> invoice int not null unique,
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> case
> when exists(select * from t as c where c.invoice < a.invoice and
> c.paidontime <> a.paidontime) then max(b.invoice)
> else -1
> end
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select * from my_view
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||You are welcome. Here is a new version, I changed the view definition a
little bit.
I am supposing that invoice numbers are unique and greater than zero. Based
on that, I am using the max invoice number that is less than current and
which paidontime value is diff from the current as the group_id (excuse my
english). If you select from the view, you will see:
group_id invoice paidontime
-- -- --
1004 1006 yes
1004 1005 yes
1003 1004 no
1000 1003 yes
1000 1002 yes
1000 1001 yes
-1 1000 no
then you can use the tehcnique describe in KB Q186133, to calculate the rank
:
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
if you join previous statement with the view by group_id, then you have all
the info needed.
use tempdb;
go
create table t(
invoice int not null unique check (invoice > 0),
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
coalesce(max(b.invoice), -1)
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select * from my_view
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"mekim" wrote:
> u r - Alejandro-mazing!!! - it works EXACTLY the way I need to
> I know my "posting" was not the clearest - so thx for figuring that out al
so
> Alejandro - I can understand basically "what u did - I'm just not so certa
in
> "how u did" it - kind of intense - but I am certainly putting the effort
> into deciphering it
> My Very Much Thx!!!
> Mekim
> "Alejandro Mesa" wrote:
>|||Hi Alejandro,
Hmmm...The 2nd one seems to be more efficent - which I'm not sure why - but
I will try to figure it out - but this brings up an interesting option for m
e
- going the other direction as well
It turns out it's more important to know the "prior" & "Next" invoice out of
the group series then any sort of ranking
Is it possible to get the output to look as follows in one view? I got it
to work separately - but not together w/o a join
group_id2 group_id invoice paidontime
-- -- -- --
-1 1004 1006 yes
-1 1004 1005 yes
1005 1003 1004 no
1004 1000 1003 yes
1004 1000 1002 yes
1004 1000 1001 yes
1001 -1 1000 no
I tried and it works (and again - I have not fully decipered the SQL
Statement so plz excuse the question)
HOWEVER - Is there a way to merge these two in the same statement or does it
need to be a join of some sorts
the follow view gives me these results
group_id invoice paidontime
-- -- --
-1 1006 yes
-1 1005 yes
1005 1004 no
1004 1003 yes
1004 1002 yes
1004 1001 yes
1001 1000 no
drop view my_view
go
create view my_view
as
select
(
select
case
when exists(select * from t as c where c.invoice > a.invoice and
c.paidontime <> a.paidontime) then Min(b.invoice)
else -1
end
from
t as b
where
b.invoice > a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
The best I can see is I need to join these two views via Invoice - which is
fine - however - greed kicks in to be more efficent
again - I think this is so how u pulled it off - I guess u've probably
figured out that I tried many times w/o success to make it work :-)
Best Regards,
Mekim
"Alejandro Mesa" wrote:
> You are welcome. Here is a new version, I changed the view definition a
> little bit.
> I am supposing that invoice numbers are unique and greater than zero. Base
d
> on that, I am using the max invoice number that is less than current and
> which paidontime value is diff from the current as the group_id (excuse my
> english). If you select from the view, you will see:
> group_id invoice paidontime
> -- -- --
> 1004 1006 yes
> 1004 1005 yes
> 1003 1004 no
> 1000 1003 yes
> 1000 1002 yes
> 1000 1001 yes
> -1 1000 no
> then you can use the tehcnique describe in KB Q186133, to calculate the ra
nk:
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> if you join previous statement with the view by group_id, then you have al
l
> the info needed.
> use tempdb;
> go
> create table t(
> invoice int not null unique check (invoice > 0),
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> coalesce(max(b.invoice), -1)
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select * from my_view
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "mekim" wrote:
>