Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
satyen
Starting Member
21 Posts |
Posted - 2013-02-11 : 11:18:56
|
I am trying to write a query to display ALL casecodes (record) at one go. Not specific to one desired casecode e.g. 'W02724'Can anyone help me do the appropriate changes. Much appreciated :)FYI - the casecode primary key field applies to all tables.ips_case would be the main table.select c.casecode AS 'Casecode', c.name AS 'Case Name', --co.name AS 'Creditor', --co.cref AS 'Reference',(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode = 'W02724')AS 'Case Administrator',(select nominee from ips_casestaff where Role = 10 and nomineetype = 1 and casecode = 'W02724') AS 'Trustees Name',(select top (1) mee.meetingpurpose from ips_meeting mee where casecode = 'W02724' order by meetingdate desc) AS 'Meeting Purpose', c.appdate AS 'Date of appointment',(select top(1) no.shortnote from ips_notes no where no.ntype = 'COUNCIL PROGRESS' and casecode = 'W02724' order by moddatetime desc) AS 'Progress Report',(select top (1) no.shortnote from ips_notes no where no.ntype = 'BKYPROP' and casecode = 'W02724' order by moddatetime desc) AS 'Assets & Value',(select top (1) no.shortnote from ips_notes no where no.ntype = 'COUNCIL DISTRIBUTION' and casecode = 'W02724' order by moddatetime desc) AS 'Estimated Time of Dividend'from ips_case c left join ips_contact co ON c.casecode = co.casecodeinner join ips_casestaff ct ON ct.CaseCode = co.CaseCode-- left join ips_meeting mee ON mee.CaseCode = c.CaseCodewhere c.casecode = 'W02724' and co.name like '%Council%' and ct.Role = 30 and ct.nomineetype = 1 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-11 : 11:42:38
|
You should be able to - in the code below, I am showing by example how to select the Case Administrator column and Meeting Purpose column. It would be similar for other columns depending on whether you want to select TOP(1) or not.I changed the LEFT JOIN to INNER JOIN. Because of the WHERE clause and because of the INNER JOIN on the next table, the LEFT JOIN effectively turns out to be an INNER JOIN in this caseSELECT c.casecode AS 'Casecode', c.name AS 'Case Name', ct.LoginName AS 'Case Administrator', mee.meetingpurpose AS 'Meeting Purpose'FROM ips_case c INNER JOIN ips_contact co ON c.casecode = co.casecode INNER JOIN ips_casestaff ct ON ct.CaseCode = co.CaseCode -- left join ips_meeting mee ON mee.CaseCode = c.CaseCode OUTER APPLY ( SELECT TOP(1) mee.meetingpurpose FROM ips_meeting mee WHERE mee.casecode = c.casecode ORDER BY meetingdate DESC ) AS meeWHERE co.name LIKE '%Council%' AND ct.Role = 30 AND ct.nomineetype = 1 |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-11 : 12:19:09
|
Thank you for the prompt response James. I am having some problem. I will come back to you tomorrow. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-11 : 12:21:39
|
Sure. First try to run the code that I posted and see if the four columns compare favorably with what you are getting in your original query. If it is not, I have missed something. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 23:31:38
|
[code]select c.casecode AS 'Casecode', c.name AS 'Case Name', --co.name AS 'Creditor', --co.cref AS 'Reference',ct.[Case Administrator],ct.[Trustees Name], mee.meetingpurpose, c.appdate AS 'Date of appointment',[Progress Report],[Assets & Value],[Estimated Time of Dividend]from ips_case c inner join ips_contact co ON c.casecode = co.casecodeinner join (select CaseCode, max(case when Role = 30 and nomineetype = 1 then LoginName end) as [Case Administrator], max(case when Role = 10 and nomineetype = 1 then nominee end) as [Trustees Name], from ips_casestaff group by CaseCode )ct ON ct.CaseCode = c.CaseCodeinner join (select CaseCode, max(case when ntype = 'COUNCIL PROGRESS' then shortnote end) as [Progress Report], max(case when ntype = 'BKYPROP' then shortnote end) as [Assets & Value], max(case when ntype = 'COUNCIL DISTRIBUTION' then shortnote end) as [Estimated Time of Dividend], from (select *,row_number() over (partition by casecode order by moddatetime desc) as seq from ips_notes )t where seq=1 group by CaseCode )non n.casecode = c.casecode left join (select row_number() over (partition by CaseCode order by meetingdate desc) as seq, CaseCode,meetingpurpose from ips_meeting )mee ON mee.CaseCode = c.CaseCode and seq=1where c.casecode = 'W02724' and co.name like '%Council%' [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|