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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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