Using a Subquery within a case statment, help needed please!

Database
N/A

Using a Subquery within a case statment, help needed please!

Hi there,

I was wondering could someone have a look at this query and give me a little help on it please?

Im trying to rework some records on my table TTE_BESTBILL and transform them as if they had been billed in a different manner. However I only want information from rows which fulfill certain criteria, hence the subquery.

Thanks in advance.

Emmet

Select
b.acc_no ,
b.HIGH_DESC,
Sum(b.CALLS) As TOTAL_CALLS ,
Sum(b.DURATION) As TOTAL_DURATION ,
Sum(b.DURATION) - 200 As BILLED_MINS ,
CASE
WHEN b.acc_no in
(select acc_no from TTE_BESTBILL
where HIGH_DESC = 'MOBILE'
and acc_no in (select acc_no from TTE_BESTBILL
where HIGH_DESC = 'FIXED'
group by acc_no
having sum(gross_rev) < 2.98 )
group by acc_no
having sum(gross_rev) < 8.26)
THEN Sum(gross_rev)
ELSE
((
(billed_mins * a.daytime_local / 100 ) + (billed_mins * a.evening_local / 100 ) + (billed_mins * a.weekend_local / 100 ) +
(billed_mins * a.daytime_national / 100 ) + (billed_mins * a.evening_national / 100 ) + (billed_mins * a.weekend_national / 100 )
) + 2.98 )
END
AS BILLED_REV
From
TTE_BESTBILL b
INNER JOIN TTE_BESTBILL_200_CALC a
On b.acc_no = a.acc_no
Where HIGH_DESC = 'FIXED'
Group By
b.acc_no ,
b.HIGH_DESC,
a.daytime_national ,
a.daytime_local ,
a.evening_national ,
a.evening_local ,
a.weekend_national ,
a.weekend_local ,
a.acc_no
;
2 REPLIES

Re: Using a Subquery within a case statment, help needed please!

I think that this should do it. You need to create a derived table that gives you the acc_no from TTE_BESTBILL. Then, left outer join to that derived table on and acc_no. If it isn't found, it will be NULL, so the expression "b.acc_no = dt1.acc_no" will be false.

Select
b.acc_no ,
b.HIGH_DESC,
Sum(b.CALLS) As TOTAL_CALLS ,
Sum(b.DURATION) As TOTAL_DURATION ,
Sum(b.DURATION) - 200 As BILLED_MINS ,
CASE
WHEN b.acc_no = dt1.acc_no
THEN Sum(gross_rev)
ELSE
((
(billed_mins * a.daytime_local / 100 ) + (billed_mins * a.evening_local / 100 ) + (billed_mins * a.weekend_local / 100 ) +
(billed_mins * a.daytime_national / 100 ) + (billed_mins * a.evening_national / 100 ) + (billed_mins * a.weekend_national / 100 )
) + 2.98 )
END
AS BILLED_REV
From
TTE_BESTBILL b
INNER JOIN TTE_BESTBILL_200_CALC a
On b.acc_no = a.acc_no

left outer join (select acc_no from TTE_BESTBILL
where HIGH_DESC = 'MOBILE'
and acc_no in (select acc_no from TTE_BESTBILL
where HIGH_DESC = 'FIXED'
group by acc_no
having sum(gross_rev) < 2.98 )
group by acc_no
having sum(gross_rev) < 8.26) dt1
on dt1.acc_no = b.acc_no

Where HIGH_DESC = 'FIXED'
Group By
b.acc_no ,
b.HIGH_DESC,
a.daytime_national ,
a.daytime_local ,
a.evening_national ,
a.evening_local ,
a.weekend_national ,
a.weekend_local ,
a.acc_no
;

Good luck!
N/A

Re: Using a Subquery within a case statment, help needed please!

Excellent, thanks Barry, im running the query now.

only thing I changed was to add the dt1.acc_no to the group by

Thanks very much,

If your ever in Ireland I owe you lots of pints.

Emmet