Intricate SQL statement to expose workflow status

Ok, here's a geeky sql statement I had to compose for web page and how it works.

SELECT

dbo.ProductionTicket.JCNumber AS JCNumber,

CONVERT(date, dbo.ProductionTicket.DateReceived) AS Received,

 dbo.ProductionTicket.Description AS Description,

'Production' AS Job,

CONVERT(date, dbo.ProductionTicket.DateCompleted) AS DateCompleted,

CASE dbo.ProductionTicket.Closed
WHEN 'False' THEN 'Open'
WHEN 'True' THEN 'Completed' END AS Completed

FROM dbo.ProductionTicket
WHERE (dbo.ProductionTicket.Closed = 'False')
OR
((dbo.ProductionTicket.Closed = 'True')
AND (dbo.ProductionTicket.DateCompleted > GETDATE() - 30))

UNION ALL

SELECT

CAST(dbo.TranscriptProcess.ID AS nvarchar(15)) AS JCNumber,

CONVERT(date, dbo.TranscriptProcess.DTReceived) AS Received,

LTRIM(RTRIM((ISNULL(dbo.TranscriptProcess.NameFirst, '') + ' ' + ISNULL(dbo.TranscriptProcess.NameLast, '')))) AS Description,

'Transcription' AS Job,

CONVERT(date, dbo.TranscriptProcess.DTFromReader) AS DateCompleted,

CASE dbo.TranscriptProcess.JobStatus
WHEN '19 - Returned to Client' THEN 'Completed'
WHEN '20 - Cancelled by Client' THEN 'Cancelled'
ELSE 'Open'
END AS Completed

FROM dbo.TranscriptProcess

WHERE ((dbo.TranscriptProcess.JobStatus = '19 - Returned to Client')
AND (dbo.TranscriptProcess.DTDUE > GETDATE() - 30))
OR
(dbo.TranscriptProcess.JobStatus <> '19 - Returned to Client'
AND dbo.TranscriptProcess.JobStatus <> '20 - Cancelled by Client')


This is designed to return various in-process jobs from two different tables and give a single view result.

So, there are two queries (the two SELECT statements) combined (the UNION ALL statement) to one record set.

Both SELECT statements return six fields (JCNumber, Received, Description, Job, DateCompleted and Completed).

Since the two tables are not identical, there is some data massaging going on prior to the UNION ALL step.

The first SELECT statement fields in order:

1. Simply applies a specific name to the field JCNumber.
2. This field needs to be CONVERTed to a Date format and named Received.
3. This is just named Description 
4. This is just named Job.
5. Another Date CONVERT and renamed to DateCompleted.
6. This needs boolean values CASEd to Open and Completed and named Completed. 

These records are coming from a table ProductionTicket where (the Closed field is either False) OR (the Closed field is True AND the date is within the last 30 days).  So it gets all still open records and any records that have been closed within the last 30 days.

The second SELECT statement fields in order:

1. First the field has to be CAST as a 15 character nvarchar, then named JCNumber.
1. Needs to match the field from the first SELECT statement.
2. This field needs to be CONVERTed to a Date format and named Received.
3. This field is generated by the concatenation of two NULLable fields (FirstName and LastName) with a space between with any leading (LTRIM) and trailing (RTRIM) spaces removed; and named Description.
4. This is just named Job.
5.  Another Date CONVERT and named to DateCompleted.
6. Another CASE process, except this time we are pealing off two specific values and the remaining ones are set to Open; and named Completed. 

These records are coming from a table TranscriptProcess where (the JobStatus is returned to client AND the difference between the Due date and now is less than 30) OR (the JobStatus isn't Returned to Client AND isn't Cancelled by Client).  So all jobs returned to the client with a Due date within the last 30 days (so we don't show long ago jobs) and all currently open jobs (not returned or cancelled).

Just wanted to show how convoluted SQL queries can be de-composed into easy to understand bits.

Nothing like explaining something to blow the cobwebs from the mind.

I like it.

chuck