[RESOLVED] ANOTHER SQL Question ...

My SQL isn't that sharp, so I hope that there is an easy answer to this one.

The following grabs records from a table, and I format a few fields along the way. Thats all fine.

VB Code:

SELECT     a.BRANCH_NUMBER, b.EST_BRNM, a.DIVISION, a.REGION, a.REGIONAL_MGR, a.SAFETY_ADVISOR, SUBSTRING(Audit_Date, 7, 2) 
                      + '/' + SUBSTRING(Audit_Date, 5, 2) + '/' + SUBSTRING(Audit_Date, 1, 4) AS Audit_Date, a.WEIGHTED_RESULT, a.WEIGHTED_PERCENTAGE, 
                      SUBSTRING(Audit_Review_Date, 7, 2) + '/' + SUBSTRING(Audit_Review_Date, 5, 2) + '/' + SUBSTRING(Audit_Review_Date, 1, 4) AS Audit_Review_Date,
                       a.ACTION_PLAN_COMPLETE, SUBSTRING(Action_Plan_Completion_Date, 7, 2) + '/' + SUBSTRING(Action_Plan_Completion_Date, 5, 2) 
                      + '/' + SUBSTRING(Action_Plan_Completion_Date, 1, 4) AS Action_Plan_Completion_Date, a.RISK_INDICATOR, 
                      CASE RISK_INDICATOR WHEN 'R' THEN 'A' WHEN 'A' THEN 'B' WHEN 'G' THEN 'C' END AS SortCode
FROM         AUDIT_LOCATION_SUMMARY a INNER JOIN
                      ESTATES.DBO.BRANCH_DETAILS b ON (b.EST_CHAN = a.CHAIN AND b.EST_BRAN = a.BRANCH_NUMBER)
WHERE     a.CHAIN = '01'
ORDER BY 14 DESC

Now ... For each Branch there may be more than 1 record returned, based on "Audit_Date". I want all these fields, but only the LATEST date for each Branch, i.e. if more than 1 record exists for any Branch I only want the one with the latest date.

Bearing in mind I am using SUBSTRING to turn my dates around (they are stored as strings in the format YYYYMMDD), I am having trouble using the MAX command.

Am I barking up the wrong tree ?

OK figured this one out too ...

I changed it to ....

VB Code:

SELECT     MAX(a.AUDIT_DATE), a.BRANCH_NUMBER, b.EST_BRNM, a.DIVISION, a.REGION, a.REGIONAL_MGR, a.SAFETY_ADVISOR, SUBSTRING(Audit_Date, 7, 
                      2) + '/' + SUBSTRING(Audit_Date, 5, 2) + '/' + SUBSTRING(Audit_Date, 1, 4) AS Audit_Date, a.WEIGHTED_RESULT, a.WEIGHTED_PERCENTAGE, 
                      SUBSTRING(Audit_Review_Date, 7, 2) + '/' + SUBSTRING(Audit_Review_Date, 5, 2) + '/' + SUBSTRING(Audit_Review_Date, 1, 4) AS Audit_Review_Date,
                       a.ACTION_PLAN_COMPLETE, SUBSTRING(Action_Plan_Completion_Date, 7, 2) + '/' + SUBSTRING(Action_Plan_Completion_Date, 5, 2) 
                      + '/' + SUBSTRING(Action_Plan_Completion_Date, 1, 4) AS Action_Plan_Completion_Date, a.RISK_INDICATOR, 
                      CASE RISK_INDICATOR WHEN 'R' THEN 'A' WHEN 'A' THEN 'B' WHEN 'G' THEN 'C' END AS SortCode
FROM         AUDIT_LOCATION_SUMMARY a INNER JOIN
                      ESTATES.DBO.BRANCH_DETAILS b ON (b.EST_CHAN = a.CHAIN AND b.EST_BRAN = a.BRANCH_NUMBER)
WHERE     a.CHAIN = '01'
GROUP BY a.AUDIT_DATE, a.BRANCH_NUMBER, b.EST_BRNM, a.DIVISION, a.REGION, a.REGIONAL_MGR, a.SAFETY_ADVISOR, a.AUDIT_DATE, 
                      a.WEIGHTED_RESULT, a.WEIGHTED_PERCENTAGE, a.AUDIT_REVIEW_DATE, a.ACTION_PLAN_COMPLETE, a.ACTION_PLAN_COMPLETION_DATE, 
                      a.RISK_INDICATOR
ORDER BY 15

This is starting to be a very long-winded statement !

This is MS SQL Server - right?

If so, start using user defined functions - they are great!

Code:

---

Create Function dbo.FormatDate(@InDate varchar(8))
  Returns varchar(10) as
Begin
        Declare @OutDate varchar(10)
        Set @OutDate=SubString(@InDate,5,2)+'/'+Right(@InDate,2)+'/'+Left(@InDate,4)
        Return @OutDate
End
Go
Select dbo.FormatDate('20060131')

---

Here is how to create one - and a sample of how to call it.

This returns:

Code:

---

         
----------
01/31/2006

(1 row(s) affected)

---

That seems like a sound idea :)

Where do these user defined functions get stored ?

If its any help I use Enterprise Manager to access my tables, DTS packages etc.

Oh and yes it is Microsoft SQL Server.

We never use EM to manipulate our DB's - we always use QUERY ANALYZER and save these little scripts as .SQL text files - they go into SOURCE SAFE nicely that way.

but at any rate - the UDF is stored in the DB just like a SPROC is - just defined a bit differently and the dbo. prefix is required.

In EM there is a branch for USER DEFINED FUNCTIONS just like TABLES, VIEWS and STORED PROCEDURES - they go in there.

Brilliant. Its about time I started learning how to do these things with a bit more structure to them.
Thanks a lot Szlamany :)

Thats hard enough to spell sober .. I would hate to try and type that whilst drunk !!

Glad to help!

"sz" is good enough...