Query to select max date across multiple tables 1

wbodger · Nov 1, 2012

OK, I need to select the max date for a participant, but I have somewhere around 7 tables, six of them where this field is called x and one where the field is y. I can identify them in Information_schema.columns, but I am not sure how to use a max across multiple tables. Have I given enough information? Oh, it is 2008 r2.

wb

imex · Nov 1, 2012

Try something like this:

Code:

with CTE as
(
    select max(x) as MaxDate from Table1
    union
    select max(x) from Table2
    union 
    select max(y) from Table3
)

select max(MaxDate) as MaxDate from CTE

Hope it's useful.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

gmmastros · Nov 1, 2012

You could "Union ALL" the various tables and then perform the max on that.

Ex:

Code:

Select Top 1 DateColumn As MaxDate
From   (
       Select Top 1 DateColumn
       From   TableA
       Where  ParticipantId = <Some Value>
       Order By DateColumn

       Union All

       Select Top 1 DateColumn
       From   TableB
       Where  ParticipantId = <Some Value>
       Order By DateColumn

       Union All

       Select Top 1 OtherDateColumn
       From   TableX
       Where  ParticipantId = <Some Value>
       Order By OtherDateColumn
       ) As DateStuff
Order By DateStuff.OtherDateColumn

Note that I used Union All, which will allow duplicates. If there are duplicates in the data, it won't matter because you are only selecting the Max 1 date value anyway.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Nov 1, 2012

I made a slight mistake in the code I posted above. It should be this:

Code:

Select Top 1 DateColumn As MaxDate
From   (
       Select (
              Select Top 1 DateColumn
              From   TableA
              Where  ParticipantId = <Some Value>
              Order By DateColumn DESC
              ) As DateColumn

       Union All

       Select (
              Select Top 1 DateColumn
              From   TableB
              Where  ParticipantId = <Some Value>
              Order By DateColumn DESC
              )

       Union All

       Select (
              Select Top 1 OtherDateColumn
              From   TableX
              Where  ParticipantId = <Some Value>
              Order By OtherDateColumn DESC
              )
       ) As DateStuff
Order By DateStuff.DateColumn DESC

The problem with the code I posted above occurs because of the order by on each individual query. Selecting "Top 1" is more efficient than performing a Max aggregate, which is why I suggested it. I also forgot to add DESC to the order by's.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

wbodger · Nov 2, 2012

George, your suggestion works when I can run it by itself and supply the Participant_ID, the glitch I have is it is part of a larger query and I am trying to join to this result set by Participant_ID. I have tried multiple iterations and cannot quite get this to work, feeling like a definite case of the Mondays on this friday...

Code:

 FROM
  (SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Confirmed' As Status FROM dbEDRN$.dbo.vwRptFinalG
   UNION
   SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Ineligible' As Status FROM dbEDRN$.dbo.vwRptIneligible
   UNION
   SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Pending' As Status FROM dbEDRN$.dbo.vwRptPending) PptList

   LEFT JOIN 

    dbEDRN$.dbo.tblFinalG FG ON PptList.STUDY_PARTICIPANT_ID = FG.STUDY_PARTICIPANT_ID AND FG.DeleteFlag = 0 
   INNER JOIN
    dbEDRN$.dbo.tblBLConsent C ON PptList.STUDY_PARTICIPANT_ID = C.STUDY_PARTICIPANT_ID AND C.DeleteFlag = 0 AND C.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblBLParticipant PQ ON PptList.STUDY_PARTICIPANT_ID = PQ.STUDY_PARTICIPANT_ID AND PQ.DeleteFlag = 0 AND PQ.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblBLClinical PC ON PptList.STUDY_PARTICIPANT_ID = PC.STUDY_PARTICIPANT_ID AND PC.DeleteFlag = 0 AND PC.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblProtocolDeviation PD ON PptList.STUDY_PARTICIPANT_ID = PD.STUDY_PARTICIPANT_ID AND PD.DeleteFlag = 0 AND PD.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblIneligibility Ineli ON PptList.STUDY_PARTICIPANT_ID = Ineli.STUDY_PARTICIPANT_ID AND Ineli.DeleteFlag = 0 AND Ineli.EntryFlag = 1  
   LEFT JOIN
	(SELECT Study_Participant_ID, targetDate FROM dbEDRN$.dbo.vwContactSchedule WHERE getdate() BETWEEN contactWindowLower AND contactWindowUpper) AS CS ON PptList.Study_Participant_ID = CS.STUDY_PARTICIPANT_ID
   LEFT JOIN
    db_LIMS_EDRN_STS.dbo.vwSpecimenSetStatus$ ss ON PptList.Study_Participant_ID = ss.PptID
   LEFT JOIN
    (Select Top 1 DateColumn As MaxDate, Study_Participant_ID
    From   (
		  Select Top 1 STUDY_CONSENT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLConsent
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By STUDY_CONSENT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLClinical
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLLab
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLParticipant
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUConsent
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFULab
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipant
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipantCa
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipantRelCa
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC

	   Union All

		  Select Top 1 PPT_DATACOLLECTED_CONTACT_DATE As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUTreatment
		  Where  Study_Participant_ID = PptList.Study_Participant_ID
		  Order By PPT_DATACOLLECTED_CONTACT_DATE DESC
	   ) As DateStuff
    Order By DateStuff.DateColumn DESC
    ) AS MD ON PptList.Study_Participant_ID = MD.Study_Participant_ID
 ORDER BY PptList.Study_Site_ID, PptList.Study_Participant_ID, PptList.Status

And the error

Code:

System.Data.SqlClient.SqlException: The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. The multi-part identifier "PptList.Study_Participant_ID" could not be bound. at compass.dbUtils.getDataset(String sql) in d:\Inetpub\[URL unfurl="true"]wwwroot\App_Code\compass\dbUtils.cs:line[/URL] 149 at vs_bin_studies_EDRN316_Reports_HEDS_MasterList.getMasterListInfo(String passedSiteID) in d:\Inetpub\[URL unfurl="true"]wwwroot\vs\bin\studies\EDRN316\reports\HEDS\MasterList.aspx.cs:line[/URL] 362 at vs_bin_studies_EDRN316_Reports_HEDS_MasterList.setupDisplay() in d:\Inetpub\[URL unfurl="true"]wwwroot\vs\bin\studies\EDRN316\reports\HEDS\MasterList.aspx.cs:line[/URL] 178

where line 362 is just where it actually runs the query that is built. The problem (as I see it) is trying to figure out how to join and get the participant id for all of the small union'ed queries.

wb

gmmastros · Nov 2, 2012

The "Top 1" trick only works if you don't need a group by.

Try this instead:

Code:

FROM
  (SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Confirmed' As Status FROM dbEDRN$.dbo.vwRptFinalG
   UNION
   SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Ineligible' As Status FROM dbEDRN$.dbo.vwRptIneligible
   UNION
   SELECT DISTINCT Study_Site_ID, Study_Participant_ID, 'Pending' As Status FROM dbEDRN$.dbo.vwRptPending) PptList

   LEFT JOIN 

    dbEDRN$.dbo.tblFinalG FG ON PptList.STUDY_PARTICIPANT_ID = FG.STUDY_PARTICIPANT_ID AND FG.DeleteFlag = 0 
   INNER JOIN
    dbEDRN$.dbo.tblBLConsent C ON PptList.STUDY_PARTICIPANT_ID = C.STUDY_PARTICIPANT_ID AND C.DeleteFlag = 0 AND C.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblBLParticipant PQ ON PptList.STUDY_PARTICIPANT_ID = PQ.STUDY_PARTICIPANT_ID AND PQ.DeleteFlag = 0 AND PQ.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblBLClinical PC ON PptList.STUDY_PARTICIPANT_ID = PC.STUDY_PARTICIPANT_ID AND PC.DeleteFlag = 0 AND PC.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblProtocolDeviation PD ON PptList.STUDY_PARTICIPANT_ID = PD.STUDY_PARTICIPANT_ID AND PD.DeleteFlag = 0 AND PD.EntryFlag = 1
   LEFT JOIN
    dbEDRN$.dbo.tblIneligibility Ineli ON PptList.STUDY_PARTICIPANT_ID = Ineli.STUDY_PARTICIPANT_ID AND Ineli.DeleteFlag = 0 AND Ineli.EntryFlag = 1  
   LEFT JOIN
	(SELECT Study_Participant_ID, targetDate FROM dbEDRN$.dbo.vwContactSchedule WHERE getdate() BETWEEN contactWindowLower AND contactWindowUpper) AS CS ON PptList.Study_Participant_ID = CS.STUDY_PARTICIPANT_ID
   LEFT JOIN
    db_LIMS_EDRN_STS.dbo.vwSpecimenSetStatus$ ss ON PptList.Study_Participant_ID = ss.PptID
   LEFT JOIN
       (
		  Select Max(STUDY_CONSENT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLConsent
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLClinical
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLLab
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE), Study_Participant_ID
		  From   dbEDRN$.dbo.tblBLParticipant
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUConsent
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFULab
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipant
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipantCa
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUParticipantRelCa
		  Group By Study_Participant_ID

	   Union All

		  Select Max(PPT_DATACOLLECTED_CONTACT_DATE) As DateColumn, Study_Participant_ID
		  From   dbEDRN$.dbo.tblFUTreatment
		  Group By Study_Participant_ID
	   ) As MD
	   On MD.Study_Participant_Id = PptList.Study_Participant_ID
 ORDER BY PptList.Study_Site_ID, PptList.Study_Participant_ID, PptList.Status

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

wbodger · Nov 2, 2012

Ahh... That behaves more like I was thinking it would. Cancer research thanks you!

wb

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query to select max date across multiple tables 1

wbodger

Programmer

imex

Programmer

gmmastros

Programmer

gmmastros

Programmer

wbodger

Programmer

gmmastros

Programmer

wbodger

Programmer

Similar threads

Part and Inventory Search

Sponsor