SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on displaying all records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satyen
Starting Member

21 Posts

Posted - 02/11/2013 :  11:18:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/11/2013 :  11:42:38  Show Profile  Reply with Quote
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 - 02/11/2013 :  12:19:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/11/2013 :  12:21:39  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/11/2013 :  23:31:38  Show Profile  Reply with Quote

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%' 


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000