0

I have the following query- I am trying to add another column, however I don't want any of the "WHERE" clauses from the previous 2 SELECT statements to take affect... so I can't just input the new column into both of the previous 2 SELECT statements. Specifically, the query currently shows results for a given date range (note the date_opened>=##STARTDATE## and date_opened<=##ENDDATE## lines), however the column I wish to add should count all total results- not just results that fall between ##STARTDATE## and ##ENDDATE##.

How would somebody go about this? Another UNION?

The db is Sybase SQLAnywhere 16, syntax similar to t-sql/mssql.

The new column I wish to add (without any of the existing date range constraints):

(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  group by cases.referred_link
) as num_refs_total,

And, the current query as a whole:

select casenum, 
matcode as casetype, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer,
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  group by cases.referred_link
) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'repeat client' as reason 
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff' 
and party.our_client='Y' 
and date_opened&gt;='##STARTDATE##' 
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN 
  (select 
    party_id 
    from 
      (select 
        count(casenum) as casecount, 
        party_id 
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff' 
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
group by cases.referred_link, cases.casenum, cases.matcode, date_opened, mailing_list
UNION ALL
select casenum, 
matcode, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer, 
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'personal referrer' as reason  
from cases 
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id 
where names.person='Y' 
and date_opened&gt;='##STARTDATE##' 
and date_opened&lt;='##ENDDATE##' 
and party.our_client='Y' 
and party.role='Plaintiff' 
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null) 
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
group by cases.referred_link, casenum, cases.matcode, date_opened, mailing_list
order by num_refs desc, date_opened asc
1
  • You probably want conditional aggregation count(case when ... then 1 end), although it's hard to tell given all the joins. Also: left join (select * is pointless subquery, you may as well just put the condition in the on like so left join mailing_list_defined a on mailing_list='Mattar Stars' and party.party_id=a.names_id And the double subquery at the end party.party_id IN ... can be simplified into one subquery with a HAVING clause also Apr 21 '21 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.