Rewriting Stored Procedure to be easier on the eye
-
I have several reports that follow the same structure as below, some of which get quite lengthy and difficult to manage. I'm hoping someone can help me find an easier (to read) way to fun the following report. I find myself making mistakes when I have to add or remove columns from the report. The report identifies when a step in a process has been skipped for one reason or another. when reading, you'll want to know at anything _F represents a date that we need to file up on the file for and anything _C represents the date the step was completed. The completion of one steps triggers the generation of a follow up date of anther column. My initial though was to try and use Cross apply, but my attempts have not been successful.
I've included 2 select statements, the first is select *, the second shows the output from my report. When both the followUp and Completed dates for a specific step are both Null the record shows on the report. If just the Completed is not null but the FollowUp is null, it is left off the report (if the step has been completed and no longer needs to be followed up on). Also, if the FollowUp is NOT NULL but the Completed IS NULL, it will also not appear on the report as this is a files normal progress.
I headache thanks you!
David 92959Create Table ##Activity_Tracking (
Record_ID Int,
Referral_F datetime,
Referral_C datetime,
Note_Ordered_F datetime,
Note_Ordered_C datetime,
Breach_Ordered_F datetime,
Breach_Ordered_C datetime,
Breach_Recvd_F datetime,
Breach_Recvd_C datetime,
Note_Recvd_F datetime,
Note_Recvd_C datetime)
Insert into ##Activity_Tracking Values
('1100', NULL, '2012-03-08 00:00:00.000', NULL, '2012-10-25 00:00:00.000', NULL, '2000-01-01 00:00:00.000', NULL, '2012-04-09 00:00:00.000', NULL, '2012-11-08 00:00:00.000'),
('1113', NULL, '2011-09-04 00:00:00.000', NULL, '2011-07-25 00:00:00.000', NULL, '2011-08-19 00:00:00.000', NULL, NULL, NULL, '2011-09-15 00:00:00.000'),
('1115', NULL, '2011-08-29 00:00:00.000', NULL, '2011-08-08 00:00:00.000', NULL, NULL, NULL, '2011-08-26 00:00:00.000', NULL, '2012-07-23 00:00:00.000'),
('1141', NULL, '2011-10-12 00:00:00.000', NULL, NULL, NULL, '2011-09-09 00:00:00.000', NULL, '2011-10-12 00:00:00.000', NULL, '2011-10-17 00:00:00.000'),
('1324', NULL, '2017-04-25 00:00:00.000', NULL, '2017-04-24 00:00:00.000', NULL, '2017-04-25 00:00:00.000', NULL, '2017-04-25 00:00:00.000', '2017-05-24 00:00:00.000', NULL)Select *
From ##Activity_TrackingSelect *
From ##Activity_Tracking
WHERE
( Referral_C IS NULL OR
Note_Ordered_C IS NULL OR
Breach_Ordered_C IS NULL OR
Breach_Recvd_C IS NULL OR
Note_Recvd_C IS NULL)
AND
(Referral_F IS NULL AND
Note_Ordered_F IS NULL AND
Breach_Ordered_F IS NULL AND
Breach_Recvd_F IS NULL AND
Note_Recvd_F IS NULL)
AND
(Referral_C IS Not NULL OR
Note_Ordered_C IS Not NULL OR
Breach_Ordered_C IS NOT NULL OR
Breach_Recvd_C IS Not NULL OR
Note_Recvd_C IS Not NULL)
DROP table ##Activity_Tracking -
Something like this??? (sorry using a jpg... the forum formatting was killing the SSMS formatting)
-
Since we're talking formatting, I'd strongly recommend taking a look at SQL Prompt from Redgate Software (DISCLOSURE: my employer). Not only will it help you with formatting, but, when you're working on the code, it'll help you figure out which column it is that you're editing within the T-SQL code (and a whole lot more).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Friday, May 19, 2017 6:54 AM
As someone who doesn't get paid by RedGate, I'll 2nd Grants SQL Prompt recommendation. In terms of increased productivity it pays for itself fairly quickly.
-
Just to make sure I understand the requirements correctly, a row only shows up on the report if both _F and _C for a particular step are NULL?
Cheers!
-
SELECT *
FROM ##Activity_Tracking
WHERE (
Referral_C IS NULL OR
Note_Ordered_C IS NULL OR
Breach_Ordered_C IS NULL OR
Breach_Recvd_C IS NULL OR
Note_Recvd_C IS NULL
)
AND COALESCE(Referral_F, Note_Ordered_F, Breach_Ordered_F, Breach_Recvd_F, Note_Recvd_F) IS NULL -- they're all NULL
AND COALESCE(Referral_C, Note_Ordered_C, Breach_Ordered_C, Breach_Recvd_C, Note_Recvd_C) IS NOT NULL -- at least one of these is NOT NULL“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
@ Jason A. Long and Grant Fritchey,
I'm after simplifying the second query. Not so much of the how the data in the table is labeled. I'm yet to check out <a title="Go to ChrisM@Works code, but what I'm after is more along those lines.
Thank you all for the posts.
Grant Fritchey, what do you do for Red Gate? I would be interested in their software, but need to learn more about it. However, as a rule of thumb I don't buy for sales people, only engineers.
-
If we're already using non-SARGable expressions, then let's do it for all the conditions.
Select *
From ##Activity_Tracking
WHERE Referral_C + Note_Ordered_C + Breach_Ordered_C + Breach_Recvd_C + Note_Recvd_C IS NULL --at least one of these is NULL
AND COALESCE(Referral_F, Note_Ordered_F, Breach_Ordered_F, Breach_Recvd_F, Note_Recvd_F) IS NULL -- they're all NULL
AND COALESCE(Referral_C, Note_Ordered_C, Breach_Ordered_C, Breach_Recvd_C, Note_Recvd_C) IS NOT NULL -- at least one of these is NOT NULL -
<a title="Go to ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl07_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork">ChrisM@Work,
Thanks for the rewrite. That's going to help a lot!This is me getting greedy, but it gets used in so many places I feel the need to ask. Is there another way to rewrite the top section to be more in line with the coalesce statements? Is there a NULL IN (Column List) capability in T-SQL?
I know as an alternative I could build the statement by using @SQL varchar(max) = ..., but I'm hoping to avoid that if possible.
Thank again!
David92595
-
Luis, you read my mind!
- David92595 - Friday, May 19, 2017 1:14 PM
Just remember that those queries will always scan the whole table. No index would speed the query.
- David92595 - Friday, May 19, 2017 12:56 PM
If you're querying for the same set of data in "so many places" in your procedure, is it worth it to create a separate temp table and populate it with just that set? Then you could hit that set of data without all the ORs you need against ##activity_tracking and just use the WHERE clauses you need for that statement.
Another possibility is to add a bit column to your existing table and, since you're using the same conditions many times, update that bit to 1 for anything you want to consider using all the ORs and COALESCEs once and then use that single column in the rest of your queries. Now an NCI might be able to help save reads, depending on the queries.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply