July 3, 2017 at 3:57 pm
Customers Table
single row per customer : Name, state, install date, maddr, city , zip, phone, population, databasetype, update date
Contacts Table
multiple contacts for each customer only 1 is designated as primary: customer, sort, number
custapps Table
multiple rows per customers:
customer, app , version, deleted
I am trying to ONLY get the customers with version s A,B,C or D but must have all 8 applications listed.
a customer could have app '01' but be on version 'E' and i dont want them selected.
select name, state, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as a_app, version, install_date, maddr, city, zip, customers.phone, contacts.sort, population, CASE databasetype WHEN 1 THEN 'Other' WHEN 2 THEN 'SQL' ELSE 'Unknown' END AS FileType, CONVERT(VARCHAR(10), updt_date, 101) as updt_date from customers
left join contacts on contacts.number = customers.prim_contact
right join custapps on customers.number = custapps.customer
where customers.number > 0 and customers.deleted = 0
and customers.install_date < '01/01/2013'
and custapps.deleted = 0
and custapps.version in ('A',B','C','D')
and custapps.app in ('01','03',04',08','10','12','13','26')
order by customers.name, customers.state, custapps.app
July 3, 2017 at 4:18 pm
roy.tollison - Monday, July 3, 2017 3:57 PMCustomers Table
single row per customer : Name, state, install date, maddr, city , zip, phone, population, databasetype, update date
Contacts Table
multiple contacts for each customer only 1 is designated as primary: customer, sort, number
custapps Table
multiple rows per customers:
customer, app , version, deletedI am trying to ONLY get the customers with version s A,B,C or D but must have all 8 applications listed.
a customer could have app '01' but be on version 'E' and i dont want them selected.
select name, state, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as a_app, version, install_date, maddr, city, zip, customers.phone, contacts.sort, population, CASE databasetype WHEN 1 THEN 'Other' WHEN 2 THEN 'SQL' ELSE 'Unknown' END AS FileType, CONVERT(VARCHAR(10), updt_date, 101) as updt_date from customers
left join contacts on contacts.number = customers.prim_contact
right join custapps on customers.number = custapps.customer
where customers.number > 0 and customers.deleted = 0
and customers.install_date < '01/01/2013'
and custapps.deleted = 0
and custapps.version in ('A',B','C','D')
and custapps.app in ('01','03',04',08','10','12','13','26')
order by customers.name, customers.state, custapps.app
Try taking Phil's query at https://www.sqlservercentral.com/Forums/FindPost1884410.aspx from your other question and adding a WHERE clause to the outer query.
July 5, 2017 at 8:14 am
Ed Wagner - Monday, July 3, 2017 4:18 PMroy.tollison - Monday, July 3, 2017 3:57 PMCustomers Table
single row per customer : Name, state, install date, maddr, city , zip, phone, population, databasetype, update date
Contacts Table
multiple contacts for each customer only 1 is designated as primary: customer, sort, number
custapps Table
multiple rows per customers:
customer, app , version, deletedI am trying to ONLY get the customers with version s A,B,C or D but must have all 8 applications listed.
a customer could have app '01' but be on version 'E' and i dont want them selected.
select name, state, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as a_app, version, install_date, maddr, city, zip, customers.phone, contacts.sort, population, CASE databasetype WHEN 1 THEN 'Other' WHEN 2 THEN 'SQL' ELSE 'Unknown' END AS FileType, CONVERT(VARCHAR(10), updt_date, 101) as updt_date from customers
left join contacts on contacts.number = customers.prim_contact
right join custapps on customers.number = custapps.customer
where customers.number > 0 and customers.deleted = 0
and customers.install_date < '01/01/2013'
and custapps.deleted = 0
and custapps.version in ('A',B','C','D')
and custapps.app in ('01','03',04',08','10','12','13','26')
order by customers.name, customers.state, custapps.appTry taking Phil's query at https://www.sqlservercentral.com/Forums/FindPost1884410.aspx from your other question and adding a WHERE clause to the outer query.
Thank you that was the directional push i needed.
thank you so very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply