pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, January 19, 2010

Roll call - when all members are present

Had to write a neat query that was a bit of fun-

A protocol is a set of services. A clinic offers services, independently of protocols. Should a clinic offer all those services, it satisfies a protocol. Return a list of all satisfied protocols given a single clinic.

(Pardon the spacing and syntax, but blogspot nukes my tabs and spaces. I must figure out a better way to post code.)
SELECT
p.ID
FROM
dbo.Protocol p
WHERE
p.IsActive = 1
AND p.ID NOT IN (
SELECT
ps.ProtocolID
FROM
dbo.ProtocolServices ps
LEFT JOIN
dbo.ClinicServices cs
ON
ps.ServiceID = cs.ServiceID
and cs.IsActive = 1
and cs.clinicid = 987
WHERE
ps.IsActive = 1
and cs.ID is null
)
What the query does is to say - give me all Protocols, except the ones where the service list is not satisfied. The subquery looks at the join tables (they are named just as they behave, per naming conventions) and determines any Protocols where the Clinic is not satisfying all Services - that is, where there is a null in the LEFT JOIN on ClinicServices.

Now, here's the fun part.

The developer I was working with, despite all his talents, is a groupie for lambda expressions, which I understand are some sort of dark magic concocted in obsidian towers for purposes dire and contradictory to the good will of databases.

Despite all that hyperbole, here's what he came up with to convert the above query to the below nonsense, err, λ expression. From what he explained to me, Lambda expressions are dynamic and use the declaratives seen below while LINQ is more hard coded. I welcome further input with the caveat that LINQ, EF and other systems for generating bad TSQL are systema non grata around here. :)

var protocols = Protocols.Where(p => p.IsActive);
var activeProtocolServices = ProtocolServices.Where(ps => ps.IsActive);
var clinicServices = ClinicServices
.Where(cs => cs.IsActive)
.Where(cs => cs.ClinicID == 987);
var allProtocolServicesForClinics = activeProtocolServices.Join(clinicServices, ps => ps.ServiceID, cs => cs.ServiceID, (ps, cs) => ps);
var protocolServicesNotApplicableForClinic = activeProtocolServices.Except(allProtocolServicesForClinics);
var protocolsForClinic = protocols.Except(protocolServicesNotApplicableForClinic.Select(ps => ps.Protocol));

No comments: