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

More RegEx Recipes for Text Processing

Yesterday I wrote a piece on using some elementary Find-and-Replace stuff using Notepad++ for handling an issue that occasionally arises in transcript handling.

On this post I thought I'd add some real RegEx recipes for more advanced text handling.

(I will be using two programs, the first is the previously mentioned Notepad++.  It is a free, as in beer, software that I use extensively.  The other is TextPad by Helios Software Solutions.  It is only about $30 for a license, but if you need the advanced features, it is WELL worth the price.  I will indicate which recipes must use TextPad, otherwise the default is using Notepad++.)

(I will refer to these RegEx processes as recipes, meaning there is a Find portion and a Replace portion.  I will use quotes to make reading easier, but the start and end quotes are not meant to be used in the actual recipe.  I will indicate a space as <space>, since those tend to be difficult to read on line.  For example; Find: "\r\n<space>\r\n".  Also I will use "F:" and "R:" instead of using all the extra letters of "Find:" and "Replace:"  And obviously, unless noted, the quotes are NOT part of the recipes and are included solely to make it easier to read.)

Alright, first an explanation of the recipe for replacing the erroneous EOL characters from the other blog.

That recipe was F:  "\r\r\n" R:  "\r\n\r\n".
     Explanation:  Find the lonely Carriage Return followed by the correct EOL sequence (\r\r\n) and replace it with the correct EOL sequence - twice (\r\n\r\n).
     Summary:  Clean up files with this odd combination of EOLs.

Another version of this issue is a file that ONLY has Line Feed characters at the end of EVERY line.
This recipe would be F:  "\n"  R:  "\r\n"
(This explanation and summary is left as an exercise for the reader.)

The recipe to remove double-spacing is F: "\r\n\r\n" R: "\r\n".
     Explanation:  Find two ASCII EOL markers in a sequence (\r\n\r\n) and replace with a single EOL (\r\n).
     Summary:  Remove all empty lines, such as found in a double-spaced file.

Trim trailing whitespace F: "[<space>\t]+$" R:  ""
     Explanation:  Find and select any and all "+" spaces "<space>" or tabs "\t" that occur just before the end of the line "$" and replace them all "[]" with nothing.
     Summary:  Trim the end of a line of extraneous whitespace.

Now the follow on to trim leading whitespace F: "^[<space>\t]+" R:  ""
     Explanation, same as above, except "^" means the start of a line.

To find most problem non-ANSI characters use F: "[^\xd,\xa,\x20-\x7a]"
     ANSI characters are the printing characters and lower non-printing ones. The "\x20-\x7a" catches the upper problem characters.


Thanks and have a great day.

chuck

Hello World!

Welcome to the my new blog.

I already write one for the firm I work for, Visionary Legal Technologies, as an advanced topic support and software oriented blog.

This one is for non-Visionary oriented software posts.

The title is from a t-shirt I was given as a gift that provides a quick phrase.

I may occasionally refer to posts on this site from the other, Visionary Tech, to allow me to write more in-depth pieces.  I do not expect there to be much duplication in content.

With that said, I'm off; follow along as you wish.

chuck

"I can explain it to you, but I cannot make you understand."