[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Find Last Not Null record

I need to find the last not null record value in a table which has a datetime column and a value column.
0
LCNW
Asked:
LCNW
  • 4
  • 3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT TOP 1 Max(dt) as last_date, value,
FROM YourTable
WHERE value IS NOT NULL
ORDER BY dt DESC

This defines 'last ... record' as the most recent date.
If your definition of 'last ... record' is something else, spell it out for us.
0
 
LCNWAuthor Commented:
That works. The problem now is I need to implement it into my existing query. Ugh...



SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units]


<<<<<<<<<<<<<<<<<<<<<<<<I NEED IT RIGHT HERE>>>>>>>>>>>>>>


FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Try this

SELECT blah, blah, blah,
   (SELECT TOP 1 value
   FROM YourTable
   WHERE value IS NOT NULL
   ORDER BY dt DESC) as last_date
FROM YourTable
   ... etc ...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LCNWAuthor Commented:
I implemented that and it gets a number, but not the correct one. I'm not sure where it's getting the value? Here's my updated query:

SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units], MAX(CASE WHEN c.SGOTAST IS NOT NULL
                      THEN CONVERT(nvarchar(10), c.SGOTAST) END) + '/' +
                          (SELECT     TOP (1) CONVERT(nvarchar(10), SGOTAST) AS SGOTAST
                            FROM          dbo.ITX_vw_tbl_Chemistry
                            WHERE      (SGOTAST IS NOT NULL)
                            ORDER BY DateTime DESC) AS SGOTAST
FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital
0
 
LCNWAuthor Commented:
I got it! I needed to add one bit with the patient in the WHERE clause:

SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units],
                          (SELECT     TOP (1) SGOTAST
                            FROM          dbo.ITX_vw_tbl_Chemistry AS s
                            WHERE      (SGOTAST IS NOT NULL) AND (PatientId = c.PatientId)
                            ORDER BY DateTime DESC) AS Expr1
FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital

Thanks for the help!
0
 
LCNWAuthor Commented:
Thanks for the help.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now