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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on displaying all records

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.casecode
inner join ips_casestaff ct ON ct.CaseCode = co.CaseCode
-- left join ips_meeting mee ON mee.CaseCode = c.CaseCode
where 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 case
SELECT 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 mee
WHERE co.name LIKE '%Council%'
AND ct.Role = 30
AND ct.nomineetype = 1
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.casecode
inner 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.CaseCode
inner 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
)n
on 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=1
where c.casecode = 'W02724' and co.name like '%Council%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -