December 14, 2011 at 12:43 pm
Hello All,
I have query like this
select y.status, COUNT(*)as meter_cnt
from (
select case
when x.recission_date > x.flow_end_date then 'cancelled'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'active'
when x.flow_end_date IS not null then 'churn'
else 'in transit'
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2) x
where rowcnt = 1)y
group by y.status
I am getting the output of this query as
status meter_cnt
active 500
cancelled 600
churn 700
in transit 800
What I need to modify my above query to get my output as which is shows only the "churn" count
meter_cnt
700
and the same way I need to modify the query to get only the "in transit" count like
meter_cnt
800
I am new to sql server and programming. Please assist me. Thanks a lot for any help.
December 14, 2011 at 12:51 pm
select COUNT(*)as meter_cnt
from (
select case
when x.recission_date > x.flow_end_date then 'cancelled'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'active'
when x.flow_end_date IS not null then 'churn'
else 'in transit'
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2) x
where rowcnt = 1)y
WHERE y.status = 'which status you are looking for'
group by y.status
Jared
Jared
CE - Microsoft
December 14, 2011 at 12:58 pm
Thanks so much. It worked. I really appreciate it.
December 14, 2011 at 1:10 pm
My thought ... almost
select y.status, COUNT(*)as meter_cnt
from (
select case
when x.recission_date > x.flow_end_date then 'cancelled'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'active'
when x.flow_end_date IS not null then 'churn'
else 'in transit'
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2
---------------------------------------------
where x.flow_end_date IS NOT NULL ) x
/* I added the where clause here maybe this way you will
limit your records in your nest query and perform faster
*/
where rowcnt = 1)y
group by y.status
December 14, 2011 at 1:24 pm
sroumel (12/14/2011)
My thought ... almost
select y.status, COUNT(*)as meter_cnt
from (
select case
when x.recission_date > x.flow_end_date then 'cancelled'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'active'
when x.flow_end_date IS not null then 'churn'
else 'in transit'
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2
---------------------------------------------
where x.flow_end_date IS NOT NULL ) x
/* I added the where clause here maybe this way you will
limit your records in your nest query and perform faster
*/
where rowcnt = 1)y
group by y.status
DO NOT DO THAT! It will only exclude NULLS which are needed to determine status. There are a number of ways to make this faster, but without the DDL and sample data I cannot determine that. Certainly excluding rows based on NULL that are required for the calculation will give you incorrect results, not speed it up. If you want to make it real fast and have incorrect results, you can just say SELECT COUNT(*) FROM sys.indexes WHERE 0=1; i.e. fast and incorrect does not really do anybody any good.
Jared
Jared
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply