March 26, 2020 at 4:42 pm
I currently have query that gives me the results i needs BUT i am needing to add a new table/column to the query.
select name, state, id, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as app,
version, maddr, city, zip, customers.phone, contacts.sort, contacts.email, install_date, population, customers.notes,
CASE databasetype WHEN 1 THEN 'Vision' WHEN 2 THEN'SQL' ELSE'Unknown' END AS FileType,
updt_date from customers
vleft join contacts on contacts.number = customers.prim_contact
left join custapps on customers.number = custapps.customer
where customers.number > 0 and customers.deleted = 0
and custapps.deleted = 0 and custapps.version > ''
and (custapps.version in ('W800','W805','W900','W901'))
and custapps.app in ('AC','AP','AR','BL','BP','CH','CL','CM','CT','EM','FA','GL','HR','IN','PA','PO','PY','RG','SA','SO','SS','ST','UB','US','VM','WO','GLG')
order by customers.name, customers.state, custapps.app
Now i am needing to add table patches, column patchnumber but it needs to match up on the customer, applicationname and only the highest created (date/time) columns
so my current query will give me a listing of customers and all of their licensed application but now i am needing to add the patch information per application.
ELECT [number]
,[customer]
,[DownloadID]
,[Created]
,[UserName]
,[ApplicationName]
,[Version]
,[PatchNumber]
,[FileName]
,[Status]
FROM [CIS].[dbo].[patches]
March 26, 2020 at 4:47 pm
SELECT ...same_as_before_except_add_patches_columns...
FROM customers
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num
FROM [CIS].[dbo].[patches]
) AS patches ON patches.customer = customers.customer AND patches.row_num = 1
left join contacts on contacts.number = customers.prim_contact
...rest_of_query_same_as_before...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2020 at 5:04 pm
i added that code and got closer but i also needed the applicationname to match the custapps.app column so i added it to the query. however i am only get 1 application patchnumber per customer. so if they have AP, AC, BL only the BL is showing a patchnumber the others are all null.
select a.name, a.state, a.id, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as app, patches.PatchNumber,
custapps.version, a.maddr, a.city, a.zip, a.phone, contacts.sort, contacts.email, install_date, population, a.notes,
CASE databasetype WHEN 1 THEN 'Vision' WHEN 2 THEN'SQL' ELSE'Unknown' END AS FileType,
updt_date from customers a
left join contacts on contacts.number = a.prim_contact
left join custapps on a.number = custapps.customer
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]
) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num = 1
where a.number > 0 and a.deleted = 0
and custapps.deleted = 0 and custapps.version > ''
and (custapps.version in ('W800','W805','W900','W901'))
and custapps.app in ('AC','AP','AR','BL','BP','CH','CL','CM','CT','EM','FA','GL','HR','IN','PA','PO','PY','RG','SA','SO','SS','ST','UB','US','VM','WO','GLG')
order by a.name, a.state, custapps.app
March 26, 2020 at 5:10 pm
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer, ApplicationName ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]
) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2020 at 5:17 pm
Thank you so very much. I will definitely do some PARTITION BY learning.
Thanks...
March 26, 2020 at 5:45 pm
Great. I guess that confirms it gave you the results you needed :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2020 at 5:48 pm
Yes sir, got some other things to work thru but that was the hurdle.
you know how it goes when you inherit someone else's project that has left the company a long time ago...
Thanks.
March 26, 2020 at 6:57 pm
An alternative is to use OUTER APPLY:
OUTER APPLY (SELECT TOP(1) *
FROM [CIS].[dbo].[patches]
WHERE patches.customer = a.number
AND patches.ApplicationName = custapps.app
ORDER BY patches.Created DESC) patches
An index on:
[CIS].[dbo].[patches](customer, ApplicationName, Created DESC) INCLUDE (all other referenced columns)
will help performance.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy