Stored Procedure Performance Woes... How to get around CURSORS for html gen.
-
Hi everyone,
Working with a stored procedure that is used in an application of ours. This procedure takes an integer as a parameter and in the end, returns what is a long line of HTML that has many many columns and many rows. This HTML is very dynamic and the data that is dealt with is pretty complicated (medical SW industry) with very very specific requirements. My hands are tied with this: there is no other way to handle creating this "flow sheet" (imagine a crazy looking calendar), the application has no provision to create it itself so it just calls the procedure. In short: it HAS to be done via this SP.
I'm by no means an expert with performance tuning, but I do know that generally speaking, CURSORS are very inefficient in SQL Server. I do actually use them a lot with really good performance results, but that's generally in much less intensive operations. Anyway, my point is that I try to avoid when I can, but when we're stuck generating dynamic HTML via SQL, I absolutely cannot think of a way to circumvent the use of cursors because I must build the HTML from the top to the bottom one cell at a time. This means I have to iterate through each cell of a row before moving onto the next line, building that row, etc.
I'm not even sure that I can cut the data sets that are handled by the cursors down in size. They are basically at their bare minimum, and really are only holding at most 80 rows (which I guess can be a lot for a cursor to iterate through).
Unfortunately I'm hesitant to share the proc code (as much as I want to) simply because I'm not trying to get in trouble with my job. That said, does anyone have any experience similar to this and maybe some suggestions for strategies to mitigate the use of cursors in this weird, specific instance?
What makes matters worse is that the exec plan is SO LARGE that it's locking up SentryOne Plan Explorer, which was recommended to me for this very thing, ha.
Thanks for your time and help!
-
I understand your plea but can you at least post an anonymized example of what the output is supposed to look like and maybe even some readily consumable test data as an example? See the first link in my signature line below for one way to do the latter.
If none of that is possible, then I'll suggest you have a look at "Example C" at the following URL, which has an example of how to make HTML via T-SQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I appreciate that you are dealing with sensitive data, but are you able to put together some example data – which could be completely nonsensical – along with desired results? This data should be in a similar format to what you are dealing with.
That would allow us, at least, to explore techniques for getting to what you want & maybe give you some ideas for possible improvements.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Understood. I'll work on getting some anonymized data for an example.
-
OK, so I completely understand that this might not suffice; this is an example output of this procedure. Note a couple of things: if you view this in a browser, it won't have any of the cell borders, etc because this uses CSS, and the number of ROWS expand with the number of days on this sheet. (Sorry if this is an undesirable format - I didn't see anything in Jeff's code etiquette post)
<!-- enc_id = 29242 --><link rel='stylesheet' type='text/css' href='stylesheets/flowsheet.css'><script language="javascript" src="javascript/planex/Popup_v1.0.js?v=7"></script><table class='fs' cellpadding='0' cellspacing='0'><tr><td class='fs fsbold' colspan='161'>FET Delestrogen/Progesterone 1</td></tr><tr><td class='fs'>2020</td><td colspan='2' class='fs fsprior'>07/20</td><td colspan='2' class='fs fsprior'>07/21</td><td colspan='2' class='fs fsprior'>07/22</td><td colspan='2' class='fs fsprior'>07/23</td><td colspan='2' class='fs fsprior'>07/24</td><td colspan='2' class='fs fsprior'>07/25</td><td colspan='2' class='fs fsprior'>07/26</td><td colspan='2' class='fs fsprior'>07/27</td><td colspan='2' class='fs fsprior'>07/28</td><td colspan='2' class='fs fsprior'>07/29</td><td colspan='2' class='fs fsprior'>07/30</td><td colspan='2' class='fs fsprior'>07/31</td><td colspan='2' class='fs fsprior'>08/01</td><td colspan='2' class='fs fsprior'>08/02</td><td colspan='2' class='fs fsprior'>08/03</td><td colspan='2' class='fs fsprior'>08/04</td><td colspan='2' class='fs fsprior'>08/05</td><td colspan='2' class='fs fsprior'>08/06</td><td colspan='2' class='fs fsprior'>08/07</td><td colspan='2' class='fs fsprior'>08/08</td><td colspan='2' class='fs fsprior'>08/09</td><td colspan='2' class='fs fsprior'>08/10</td><td colspan='2' class='fs fsprior'>08/11</td><td colspan='2' class='fs fsprior'>08/12</td><td colspan='2' class='fs fsprior'>08/13</td><td colspan='2' class='fs fsprior'>08/14</td><td colspan='2' class='fs fsprior'>08/15</td><td colspan='2' class='fs fsprior'>08/16</td><td colspan='2' class='fs fsprior'>08/17</td><td colspan='2' class='fs fsprior'>08/18</td><td colspan='2' class='fs fsprior'>08/19</td><td colspan='2' class='fs fsprior'>08/20</td><td colspan='2' class='fs fsprior'>08/21</td><td colspan='2' class='fs fsprior'>08/22</td><td colspan='2' class='fs fsprior'>08/23</td><td colspan='2' class='fs fsprior'>08/24</td><td colspan='2' class='fs fsprior'>08/25</td><td colspan='2' class='fs fsprior'>08/26</td><td colspan='2' class='fs fsprior'>08/27</td><td colspan='2' class='fs fsprior'>08/28</td><td colspan='2' class='fs fsprior'>08/29</td><td colspan='2' class='fs fsprior'>08/30</td><td colspan='2' class='fs fsprior'>08/31</td><td colspan='2' class='fs fsprior'>09/01</td><td colspan='2' class='fs fsday1'>09/02</td><td colspan='2' class='fs fscol0'>09/03</td><td colspan='2' class='fs fscol1'>09/04</td><td colspan='2' class='fs fscol0'>09/05</td><td colspan='2' class='fs fscol1'>09/06</td><td colspan='2' class='fs fscol0'>09/07</td><td colspan='2' class='fs fscol1'>09/08</td><td colspan='2' class='fs fscol0'>09/09</td><td colspan='2' class='fs fscol1'>09/10</td><td colspan='2' class='fs fscol0'>09/11</td><td colspan='2' class='fs fscol1'>09/12</td><td colspan='2' class='fs fscol0'>09/13</td><td colspan='2' class='fs fscol1'>09/14</td><td colspan='2' class='fs fscol0'>09/15</td><td colspan='2' class='fs fscol1'>09/16</td><td colspan='2' class='fs fscol0'>09/17</td><td colspan='2' class='fs fscol1'>09/18</td><td colspan='2' class='fs fscol0'>09/19</td><td colspan='2' class='fs fscol1'>09/20</td><td colspan='2' class='fs fscol0'>09/21</td><td colspan='2' class='fs fscol1'>09/22</td><td colspan='2' class='fs fscol0'>09/23</td><td colspan='2' class='fs fscol1'>09/24</td><td colspan='2' class='fs fscol0'>09/25</td><td colspan='2' class='fs fscol1'>09/26</td><td colspan='2' class='fs fscol0'>09/27</td><td colspan='2' class='fs fscol1'>09/28</td><td colspan='2' class='fs fscol0'>09/29</td><td colspan='2' class='fs fscol1'>09/30</td><td colspan='2' class='fs fscol0'>10/01</td><td colspan='2' class='fs fscol1'>10/02</td><td colspan='2' class='fs fscol0'>10/03</td><td colspan='2' class='fs fscol1'>10/04</td><td colspan='2' class='fs fscol0'>10/05</td><td colspan='2' class='fs fscol1'>10/06</td><td colspan='2' class='fs fscol0'>10/07</td></tr><tr><td class='fs'> </td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsprior'>R</td><td class='fs fsprior'>L</td><td class='fs fsday1'>R</td><td class='fs fsday1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td><td class='fs fscol1'>R</td><td class='fs fscol1'>L</td><td class='fs fscol0'>R</td><td class='fs fscol0'>L</td></tr><tr><td class='fs fsbold'>Cycle Day #</td><td colspan='2' class='fs fsprior'>-42</td><td colspan='2' class='fs fsprior'>-41</td><td colspan='2' class='fs fsprior'>-40</td><td colspan='2' class='fs fsprior'>-39</td><td colspan='2' class='fs fsprior'>-38</td><td colspan='2' class='fs fsprior'>-37</td><td colspan='2' class='fs fsprior'>-36</td><td colspan='2' class='fs fsprior'>-35</td><td colspan='2' class='fs fsprior'>-34</td><td colspan='2' class='fs fsprior'>-33</td><td colspan='2' class='fs fsprior'>-32</td><td colspan='2' class='fs fsprior'>-31</td><td colspan='2' class='fs fsprior'>-30</td><td colspan='2' class='fs fsprior'>-29</td><td colspan='2' class='fs fsprior'>-28</td><td colspan='2' class='fs fsprior'>-27</td><td colspan='2' class='fs fsprior'>-26</td><td colspan='2' class='fs fsprior'>-25</td><td colspan='2' class='fs fsprior'>-24</td><td colspan='2' class='fs fsprior'>-23</td><td colspan='2' class='fs fsprior'>-22</td><td colspan='2' class='fs fsprior'>-21</td><td colspan='2' class='fs fsprior'>-20</td><td colspan='2' class='fs fsprior'>-19</td><td colspan='2' class='fs fsprior'>-18</td><td colspan='2' class='fs fsprior'>-17</td><td colspan='2' class='fs fsprior'>-16</td><td colspan='2' class='fs fsprior'>-15</td><td colspan='2' class='fs fsprior'>-14</td><td colspan='2' class='fs fsprior'>-13</td><td colspan='2' class='fs fsprior'>-12</td><td colspan='2' class='fs fsprior'>-11</td><td colspan='2' class='fs fsprior'>-10</td><td colspan='2' class='fs fsprior'>-9</td><td colspan='2' class='fs fsprior'>-8</td><td colspan='2' class='fs fsprior'>-7</td><td colspan='2' class='fs fsprior'>-6</td><td colspan='2' class='fs fsprior'>-5</td><td colspan='2' class='fs fsprior'>-4</td><td colspan='2' class='fs fsprior'>-3</td><td colspan='2' class='fs fsprior'>-2</td><td colspan='2' class='fs fsprior'>-1</td><td colspan='2' class='fs fsprior'>0</td><td colspan='2' class='fs fsprior'>1</td><td colspan='2' class='fs fsday1'>2</td><td colspan='2' class='fs fscol0'>3</td><td colspan='2' class='fs fscol1'>4</td><td colspan='2' class='fs fscol0'>5</td><td colspan='2' class='fs fscol1'>6</td><td colspan='2' class='fs fscol0'>7</td><td colspan='2' class='fs fscol1'>8</td><td colspan='2' class='fs fscol0'>9</td><td colspan='2' class='fs fscol1'>10</td><td colspan='2' class='fs fscol0'>11</td><td colspan='2' class='fs fscol1'>12</td><td colspan='2' class='fs fscol0'>13</td><td colspan='2' class='fs fscol1'>14</td><td colspan='2' class='fs fscol0'>15</td><td colspan='2' class='fs fscol1'>16</td><td colspan='2' class='fs fscol0'>17</td><td colspan='2' class='fs fscol1'>18</td><td colspan='2' class='fs fscol0'>19</td><td colspan='2' class='fs fscol1'>20</td><td colspan='2' class='fs fscol0'>21</td><td colspan='2' class='fs fscol1'>22</td><td colspan='2' class='fs fscol0'>23</td><td colspan='2' class='fs fscol1'>24</td><td colspan='2' class='fs fscol0'>25</td><td colspan='2' class='fs fscol1'>26</td><td colspan='2' class='fs fscol0'>27</td><td colspan='2' class='fs fscol1'>28</td><td colspan='2' class='fs fscol0'>29</td><td colspan='2' class='fs fscol1'>30</td><td colspan='2' class='fs fscol0'>31</td><td colspan='2' class='fs fscol1'>32</td><td colspan='2' class='fs fscol0'>33</td><td colspan='2' class='fs fscol1'>34</td><td colspan='2' class='fs fscol0'>35</td><td colspan='2' class='fs fscol1'>36</td><td colspan='2' class='fs fscol0'>37</td></tr><tr><td class='fs fsbold'>Day</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsprior'>We</td><td colspan='2' class='fs fsprior'>Th</td><td colspan='2' class='fs fsprior'>Fr</td><td colspan='2' class='fs fsprior'>Sa</td><td colspan='2' class='fs fsprior'>Su</td><td colspan='2' class='fs fsprior'>Mo</td><td colspan='2' class='fs fsprior'>Tu</td><td colspan='2' class='fs fsday1'>We</td><td colspan='2' class='fs fscol0'>Th</td><td colspan='2' class='fs fscol1'>Fr</td><td colspan='2' class='fs fscol0'>Sa</td><td colspan='2' class='fs fscol1'>Su</td><td colspan='2' class='fs fscol0'>Mo</td><td colspan='2' class='fs fscol1'>Tu</td><td colspan='2' class='fs fscol0'>We</td><td colspan='2' class='fs fscol1'>Th</td><td colspan='2' class='fs fscol0'>Fr</td><td colspan='2' class='fs fscol1'>Sa</td><td colspan='2' class='fs fscol0'>Su</td><td colspan='2' class='fs fscol1'>Mo</td><td colspan='2' class='fs fscol0'>Tu</td><td colspan='2' class='fs fscol1'>We</td><td colspan='2' class='fs fscol0'>Th</td><td colspan='2' class='fs fscol1'>Fr</td><td colspan='2' class='fs fscol0'>Sa</td><td colspan='2' class='fs fscol1'>Su</td><td colspan='2' class='fs fscol0'>Mo</td><td colspan='2' class='fs fscol1'>Tu</td><td colspan='2' class='fs fscol0'>We</td><td colspan='2' class='fs fscol1'>Th</td><td colspan='2' class='fs fscol0'>Fr</td><td colspan='2' class='fs fscol1'>Sa</td><td colspan='2' class='fs fscol0'>Su</td><td colspan='2' class='fs fscol1'>Mo</td><td colspan='2' class='fs fscol0'>Tu</td><td colspan='2' class='fs fscol1'>We</td><td colspan='2' class='fs fscol0'>Th</td><td colspan='2' class='fs fscol1'>Fr</td><td colspan='2' class='fs fscol0'>Sa</td><td colspan='2' class='fs fscol1'>Su</td><td colspan='2' class='fs fscol0'>Mo</td><td colspan='2' class='fs fscol1'>Tu</td><td colspan='2' class='fs fscol0'>We</td></tr><tr><td class='fs fsbold'>Oral Contraceptives (AM/PM)</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> / 1</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>delestrogen (AM/PM)</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> / 0.5mL</td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> / 0.5mL</td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> / 0.5mL</td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> / 0.5mL</td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> / 0.5mL</td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> / 0.5mL</td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>Azithromyocin (AM/PM)</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'>500mg / </td><td colspan='2' class='fs fscol1'>250 mg / </td><td colspan='2' class='fs fscol0'>250 mg / </td><td colspan='2' class='fs fscol1'>250 mg / </td><td colspan='2' class='fs fscol0'>250 mg / </td><td colspan='2' class='fs fscol1'>250 mg / </td><td colspan='2' class='fs fscol0'>250 mg / </td><td colspan='2' class='fs fscol1'>250 mg / </td><td colspan='2' class='fs fscol0'>250 mg / </td><td colspan='2' class='fs fscol1'>250 mg / </td><td colspan='2' class='fs fscol0'>250 mg / </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>Medrol (AM/PM)</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'>8 mg / </td><td colspan='2' class='fs fscol0'>16 mg / </td><td colspan='2' class='fs fscol1'>16 mg / </td><td colspan='2' class='fs fscol0'>16 mg / </td><td colspan='2' class='fs fscol1'>16 mg / </td><td colspan='2' class='fs fscol0'>16 mg / </td><td colspan='2' class='fs fscol1'>8 mg / </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>progesterone (AM/PM)</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> / 1mL at 4pm</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td><td colspan='2' class='fs fscol1'> / 2mL</td><td colspan='2' class='fs fscol0'> / 2mL</td></tr><tr><td class='fs fsbold'>Estrace (AM/PM)</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td><td colspan='2' class='fs fscol1'>2 mg / 2 mg</td><td colspan='2' class='fs fscol0'>2 mg / 2 mg</td></tr><tr><td class='fs fsbold'>FSH</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>E2</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'>334</td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'>480</td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>LH</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>P4</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'>47</td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td></tr><tr><td class='fs fsbold'>hCG</td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsprior'> </td><td colspan='2' class='fs fsday1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colspan='2' class='fs fscol1'> </td><td colspan='2' class='fs fscol0'> </td><td colsp
I'm trying to get a sample for analysis, but I'm having a hard time doing this without basically just copying the whole database (there are many TVFs, Scalar Functions, custom table types, and a slew of regular user tables being used inside this procedure). Yeah, this is a frustrating and it might seem like I'm being lazy. I promise you it would take me a long long time to get the entire stored procedure and what it depends on. Not to mention, I SINCERELY doubt that anyone would want to sift through this ~1000 line stored procedure.
That said, I might be able to ID some of the more seemingly troublesome cursors and post those in isolation with the data used in the cursor. Could that help?
Again, thanks for your time so far and apologies for this being kind of a mess so far.
Edit: WOW I didn't even see the file attachment option. I've attached the html in a simple text file there
Edit2: Nevermind. Not sure of a filetype to use that won't get flagged for security reasons.
-
I stored the HTML that you included as an HTML file and opened that with FireFox. It came out looking like the following...
... and got progressively worse the further to the right you go...
Does it currently look like that to you because it's looking a bit troublesome.
To simplify, it looks like some sort of either prescribed medication regimen or an actual dosage taken of different medications by day. I don't know what all the "/"s are for, though.
If you have shedload of cursors in the sp that are trying to put this data together, they're just not needed because, as complicated as this "roadmap" looks, it's all repeating data that can pretty easily be collected, perhaps aggregated, and pivoted using some dynamic SQL.
If you could cobble together an explanation of what the required different rows are supposed to contain, perhaps I can cobble together some test data to show you that this requires no explicit cursors or other forms of RBAR. A much more narrow and cleaner example of the rows in a spreadsheet might help, as well.
A great example of my confusion is what on Earth do the L and R below each date stand for? They look like subtitles for 2 columns under each date label (which are probably meant to span the L and R columns) but the (apparently) Java script that constructed the HTML for this is horribly bloated.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
That is indeed what it looks like. It looks pretty messy if you don't have the CSS and the context of what the chart is used for. Those "/" are there to split doses between AM and PM. But yes, generally speaking it is a weird calendar that shows some medication information (and other information) based on what's recorded in the application. The calendar grows and shrinks based on what "cycle" is current, how many days that cycle has been current.
My boss and I have been looking at this a bit more and a couple of interesting things have come up: fortunately this isn't a massive priority anymore. That, and my boss has become unconvinced that the cursors (at least the cursors themselves) are the issue. All of this concatenation is being done to an NVARCHAR(MAX) column. He commented out some parts of the cursors where the concatenation was happening and it was apparently massively faster. He's convinced now that the concatenation to the variable is the real culprit. The train of thought he's going down is to keep the cursors to insert into table vars, then do the contatenation at the end in one go using the XML Path method. Initially he wanted to use string_agg but that's not available until 2017, and we're unlikely to see another upgrade for the next few years.
He might not be wrong in that it could be faster because I could follow his logic. He is also quite a bit more experienced than I with SQL (and really programming in general; he's the R&D head). That said, I am interested in trying to do away with the cursors if possible. BUT, I guess I'll have to look into this a bit (and I'd like to see how this results).
So, this is quite a bit of a pivot, but can anyone speak to how the idea that the huge number of concats to the nvarchar(max) variable could be a large part (or small/none at all), here?
Thanks again. You all are lifesavers.
-
The key here is to always remember what's important in these types of things... it's massively important, even if all the code is done in T-SQL, to keep the data-layer separate from the presentation layer. If all the data were first collected into an EAV, it would be comparatively easy to create a dynamic CROSSTAB to do the pivoting and HTML formatting and none of it would require any RBAR, especially cursors.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK. I have been dealing with a bunch of memory pressure issues on a different server (different conversation, ha), but I finally got some time to find some example. That said, what I have shared below is not the original procedure I have been talking about, but it uses the same principles at the end to build the HTML, and it was written entirely by me nearly 3 years ago when I was first starting my SQL Server journey (read: there will be questionable design choices, etc - this relies heavily on the tempdb). The output of the below can be saved as an HTML file and opened in a browser to see the intended table. This isn't perfect, but it gives you a good idea of what it's supposed to look like in the end.
There is a LOT of code that I have removed to make this as easy to look at as possible while still keeping the cursors behaving generally how they normally would. Interestingly enough, in that code, there is a part where I did seem to build HTML cells using a set based operation, though I'm not sure how or order the rows (is that where stuff comes in? been a while since I've used that) and there are quite literally about 15 self joins because I didn't know how to pivot.
Anyway, how would you go about getting rid of the RBAR problem, here?
PS: I would love to see Jeff's face when he sees the cursor...inside another cursor 🙂
-
OK, my attachment got flagged again; here's the code. I can't upload any kind of file I've tried! I get it but I feel bad posting this much code in a code sample block.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @HTML_HEADER NVARCHAR(MAX) = '<table>'
DECLARE @HTML_TREAT NVARCHAR(MAX) = ''
DECLARE @HTML_DATE NVARCHAR(MAX) = ''
DECLARE @HTML_DOSE NVARCHAR(MAX) = ''
DECLARE @HTML_CYCLE NVARCHAR(MAX) = ''
DECLARE @HTML_HEIGHT NVARCHAR(MAX) = ''
DECLARE @HTML_WGT NVARCHAR(MAX) = ''
DECLARE @HTML_BSA NVARCHAR(MAX) = ''
DECLARE @HTML_WBC NVARCHAR(MAX) = ''
DECLARE @HTML_ANC NVARCHAR(MAX) = ''
DECLARE @HTML_HGB NVARCHAR(MAX) = ''
DECLARE @HTML_PLT NVARCHAR(MAX) = ''
DECLARE @HTML_MCV NVARCHAR(MAX) = ''
DECLARE @HTML_Na NVARCHAR(MAX) = ''
DECLARE @HTML_K NVARCHAR(MAX) = ''
DECLARE @HTML_Cr NVARCHAR(MAX) = ''
DECLARE @HTML_BUN NVARCHAR(MAX) = ''
DECLARE @HTML_Ca NVARCHAR(MAX) = ''
DECLARE @HTML_MG NVARCHAR(MAX) = ''
DECLARE @HTML_CBC_DATE NVARCHAR(MAX) = ''
DECLARE @HTML_XRT NVARCHAR(MAX) = ''
DECLARE @HTML_ABNORMAL_LABS NVARCHAR(MAX) = ''
DECLARE @HTML_TRAILER NVARCHAR(MAX) = '</table><br><br><br>'
DECLARE @HTML_OUTPUT NVARCHAR(MAX) = ''
DECLARE @HTML_OUTPUT2 NVARCHAR(MAX) = ''
DECLARE @DOSES TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
ENC_ID INT NOT NULL,
ENC_DATE DATE NOT NULL,
PTH_ID INT NULL,
DOSE VARCHAR(MAX) NULL,
GRP_ID NUMERIC(4,2) NULL,
RAD VARCHAR(MAX) NULL
)
DECLARE @VT TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
ENC_ID INT NULL,
ENC_DATE DATE NULL,
PTH_ID INT NULL,
WGT VARCHAR(MAX) NULL,
HEIGHT VARCHAR(MAX) NULL,
BSA VARCHAR(MAX) NULL
)
DECLARE @TREATMENT TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
ENC_ID INT NOT NULL,
PTH_ID INT NOT NULL,
DTM_ID INT NOT NULL,
TREATMENT VARCHAR(100) NULL,
ENC_DATE DATE NULL
)
DECLARE @DIAGNOSIS TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
PT_ID INT NOT NULL,
PTH_ID INT NOT NULL,
DIAG VARCHAR(200) NULL,
ENC_ID INT NOT NULL
)
DECLARE @ALLERGIES TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
PT_ID INT NOT NULL,
ALLERGIES VARCHAR(1000) NULL,
ENC_ID INT NULL
)
DECLARE @CYCLES TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
CYCLE VARCHAR(200) NULL,
ENC_ID INT NOT NULL,
PT_ID INT NOT NULL,
ENC_DATE DATE NOT NULL
)
DECLARE @ABN_LABS_CONCAT TABLE(
LABS VARCHAR(MAX) NULL,
LOH_ENC_ID INT NULL,
LOH_PT_ID INT NULL
)
DECLARE @LABS_ASSOC TABLE(
ENC_DATE DATE NULL,
ENC_ID INT NULL,
WBC VARCHAR(150) NULL,
ANC VARCHAR(150) NULL,
HGB VARCHAR(150) NULL,
PLT VARCHAR(150) NULL,
MCV VARCHAR(150) NULL,
NA VARCHAR(150) NULL,
K VARCHAR(150) NULL,
CR VARCHAR(150) NULL,
BUN VARCHAR(150) NULL,
CA VARCHAR(150) NULL,
MG VARCHAR(150) NULL,
CBC_DATE DATE NULL
)
DECLARE @PTINFO TABLE(
ID INT PRIMARY KEY IDENTITY(1,1),
PT_ID INT NOT NULL,
NAME VARCHAR(100) NOT NULL,
DOB DATE NOT NULL
)
INSERT INTO @PTINFO(PT_ID,NAME,DOB)
values(4461,'JETSON, GEORGE SENIOR','1945-11-06')
INSERT INTO @DOSES(ENC_DATE,ENC_ID,DOSE,RAD,PTH_ID)
select '2017-09-06','58395','','','252307'union all
select '2017-09-13','60384','','','263688'union all
select '2017-09-20','60395','','','263759'union all
select '2017-09-27','60401','','','263801'union all
select '2017-10-05','60415','','','263882'union all
select '2018-02-08','64617','','','287030';
INSERT INTO @VT(ENC_DATE,ENC_ID,WGT,HEIGHT,BSA)
select 'Feb 8 2018 12:00AM','64617','200.2','71.00','2.13'union all
select 'Oct 5 2017 12:00AM','60415','200.2','71.00','2.13'union all
select 'Sep 6 2017 12:00AM','58395','202.20','71.00','2.14'union all
select 'Sep 13 2017 12:00AM','60384','201.8','71.00','2.14'union all
select 'Sep 20 2017 12:00AM','60395','200.4','71.00','2.13'union all
select 'Sep 27 2017 12:00AM','60401','199','71.00','2.13';
INSERT INTO @TREATMENT(DTM_ID,ENC_ID,PTH_ID,TREATMENT,ENC_DATE)
select '3003','58395','252307','Carboplatin weekly','Sep 6 2017 12:00AM'union all
select '3003','60384','263688','Carboplatin weekly','Sep 13 2017 12:00AM'union all
select '3003','60395','263759','Carboplatin weekly','Sep 20 2017 12:00AM'union all
select '3003','60401','263801','Carboplatin weekly','Sep 27 2017 12:00AM'union all
select '3003','60415','263882','Carboplatin weekly','Oct 5 2017 12:00AM'union all
select '3003','64617','287030','Carboplatin weekly','Feb 8 2018 12:00AM';
INSERT INTO @DIAGNOSIS(PT_ID,PTH_ID,DIAG,ENC_ID)
select '4461','252309','162.5 Malignant neoplasm of lower lobe of right lung ICD10:C34.31 SNOMED:187870002','58395'union all
select '4461','263690','162.5 Malignant neoplasm of lower lobe of right lung ICD10:C34.31 SNOMED:187870002','60384'union all
select '4461','263761','162.5 Malignant neoplasm of lower lobe of right lung ICD10:C34.31 SNOMED:187870002','60395'union all
select '4461','263803','162.5 Malignant neoplasm of lower lobe of right lung ICD10:C34.31 SNOMED:187870002','60401'union all
select '4461','263884','162.5 Malignant neoplasm of lower lobe of right lung ICD10:C34.31 SNOMED:187870002','60415';
INSERT INTO @ALLERGIES(PT_ID,ALLERGIES)
select '4461','NKDA';
INSERT INTO @CYCLES(CYCLE,ENC_ID,PT_ID,ENC_DATE)
select 'C1','58395','4461','2017-09-06'union all
select 'C2','60384','4461','2017-09-13'union all
select 'C3','60395','4461','2017-09-20'union all
select 'C4','60401','4461','2017-09-27'union all
select 'C5','60415','4461','2017-10-05'union all
select 'No cycle number set','64617','4461','2018-02-08';
INSERT INTO @LABS_ASSOC
SELECT '2017-09-13','60384','<td><center>7.6</center></td>','<td><center>5.18</center></td>','<td><center>13.2</center></td>','<td><center>188</center></td>','<td><center>95</center></td>','<td><center>140.00</center></td>','<td><center>4.50</center></td>','<td><center>0.83</center></td>','<td bgcolor="#ffff00"><center>9.00</center></td>','<td><center>8.9</center></td>','','2017-09-08'UNION ALL
SELECT '2017-09-20','60395','<td><center>5.5</center></td>','<td><center>4.03</center></td>','<td><center>13.1</center></td>','<td><center>198</center></td>','<td><center>95.5</center></td>','<td><center>140.00</center></td>','<td><center>4.30</center></td>','<td><center>0.71</center></td>','<td><center>11.00</center></td>','<td><center>9.2</center></td>','','2017-09-15'UNION ALL
SELECT '2017-10-05','60415','<td><center>4.6</center></td>','<td><center>3.51</center></td>','<td bgcolor="#ffff00"><center>12.7</center></td>','<td bgcolor="#ffff00"><center>123</center></td>','<td><center>96.5</center></td>','<td><center>139.00</center></td>','<td><center>4.20</center></td>','<td><center>0.67</center></td>','<td bgcolor="#ffff00"><center>9.00</center></td>','<td><center>8.6</center></td>','','2017-09-29'UNION ALL
SELECT '2018-02-08','64617','<td><center>3.9</center></td>','<td><center>0.34</center></td>','<td bgcolor="#ffff00"><center>12.3</center></td>','<td bgcolor="#ffff00"><center>93</center></td>','<td><center>97.3</center></td>','<td><center>138.00</center></td>','<td><center>3.70</center></td>','<td><center>0.78</center></td>','<td><center>11.00</center></td>','<td><center>8.9</center></td>','','2017-10-06'UNION ALL
SELECT '2018-02-08','64617','<td><center>5.1</center></td>','<td><center>3.74</center></td>','<td><center>13.2</center></td>','<td><center>156</center></td>','<td><center>96.4</center></td>','<td><center>139.00</center></td>','<td><center>4.10</center></td>','<td><center>0.69</center></td>','<td><center>12.00</center></td>','<td><center>9.3</center></td>','','2017-10-22'UNION ALL
SELECT '2018-02-08','64617','<td><center>9.4</center></td>','<td><center>9.73</center></td>','<td><center>14.2</center></td>','<td><center>208</center></td>','<td><center>95</center></td>','<td><center>142.00</center></td>','<td><center>4.60</center></td>','<td><center>0.90</center></td>','<td><center>11.00</center></td>','<td><center>9.2</center></td>','','2017-12-18';
INSERT INTO @ABN_LABS_CONCAT(LOH_PT_ID,LOH_ENC_ID,LABS)
select '4461','58395',' Anion Gap 3.00, BUN/Creatinine Ratio 9.00, Carbon Dioxide (CO2) 35.00, Protein, Total 5.90'UNION ALL
select '4461','60384',' Protein, Total 5.90, RBC 4.2'UNION ALL
select '4461','64617',' Absolute Neutrophil 9.73, Carbon Dioxide (CO2) 32.00, Glucose 107.00, Monocytes 0.66, Protein, Total 5.80, RBC 4.18, RBC 4.55';
/*Start of HTML build*/
SELECT DISTINCT
@HTML_HEADER = @HTML_HEADER + '<tr><center><b>Patient</b>: <b>'+COALESCE(NAME,' ')+'</b></center></tr>'
FROM @PTINFO
SELECT DISTINCT
@HTML_HEADER = @HTML_HEADER + '<tr><center><b>DOB:</b> <b>'+COALESCE(CAST(DOB AS VARCHAR(30)),' ')+'</b></center></tr>'
FROM @PTINFO
SELECT DISTINCT
@HTML_HEADER = @HTML_HEADER + '<tr><center><b>Allergies:</b> <b>'+COALESCE(REPLACE(ALLERGIES,', ',',<br> '),'(NKDA)')
FROM @ALLERGIES
SELECT DISTINCT
@HTML_HEADER = @HTML_HEADER + '<tr><center><b>Diagnosis:</b> <b>'+COALESCE(DIAG,'(no diagnosis selected)')+'</b></center></tr>'
FROM @DIAGNOSIS
DECLARE @COUNT INT
DECLARE @TREAT VARCHAR(200)
DECLARE TREAT CURSOR FOR
SELECT
TREATMENT,
SUM(CT)
FROM (
SELECT DISTINCT
TREATMENT,CYCLE,1 AS CT
FROM @TREATMENT AS T
INNER JOIN @CYCLES AS C
ON C.ENC_ID = T.ENC_ID
INNER JOIN @VT AS V
ON T.ENC_ID = V.ENC_ID
INNER JOIN @DOSES AS D
ON D.PTH_ID = T.PTH_ID
) AS A
GROUP BY TREATMENT
OPEN TREAT
FETCH NEXT FROM TREAT INTO @TREAT,@COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @TREAT_CT INT = 1
DECLARE @ENC_DATE DATE
DECLARE @HEIGHT VARCHAR(20)
DECLARE @WGT VARCHAR(20)
DECLARE @BSA VARCHAR(MAX)
DECLARE @DOSE VARCHAR(MAX)
DECLARE @CYCLE VARCHAR(50)
DECLARE @ENC_ID INT
DECLARE @PREV_TREAT VARCHAR(MAX)
DECLARE @WBC VARCHAR(100)
DECLARE @ANC VARCHAR(100)
DECLARE @HGB VARCHAR(100)
DECLARE @PLT VARCHAR(100)
DECLARE @MCV VARCHAR(100)
DECLARE @Na VARCHAR(100)
DECLARE @K VARCHAR(100)
DECLARE @Cr VARCHAR(100)
DECLARE @BUN VARCHAR(100)
DECLARE @Ca VARCHAR(100)
DECLARE @MG VARCHAR(100)
DECLARE @CBC_DATE DATE
DECLARE @XRT VARCHAR(5)
DECLARE @ABNORMAL_LABS VARCHAR(MAX)
DECLARE VALUE CURSOR FOR
SELECT DISTINCT
T.ENC_DATE,
HEIGHT,
WGT,
BSA,
DOSE,
CYCLE,
TREATMENT,
WBC,
ANC,
HGB,
PLT,
MCV,
NA,
K,
CR,
BUN,
CA,
MG,
CBC_DATE,
RAD,
AL.LABS
FROM @TREATMENT AS T
LEFT JOIN @CYCLES AS C
ON C.ENC_ID = T.ENC_ID
LEFT JOIN @VT AS V
ON T.ENC_ID = V.ENC_ID
LEFT JOIN @DOSES AS D
ON D.PTH_ID = T.PTH_ID
LEFT JOIN @LABS_ASSOC AS L
ON L.ENC_ID = C.ENC_ID
LEFT JOIN @ABN_LABS_CONCAT AS AL
ON AL.LOH_ENC_ID = C.ENC_ID
WHERE TREATMENT = @TREAT
OPEN VALUE
SET @HTML_OUTPUT = ''
SET @HTML_TREAT = ''
SET @HTML_DATE = ''
SET @HTML_DOSE = ''
SET @HTML_CYCLE = ''
SET @HTML_HEIGHT = ''
SET @HTML_WGT = ''
SET @HTML_BSA = ''
SET @HTML_WBC = ''
SET @HTML_ANC = ''
SET @HTML_HGB = ''
SET @HTML_PLT = ''
SET @HTML_MCV = ''
SET @HTML_Na = ''
SET @HTML_K = ''
SET @HTML_Cr = ''
SET @HTML_BUN = ''
SET @HTML_Ca = ''
SET @HTML_MG = ''
SET @HTML_CBC_DATE = ''
SET @HTML_XRT = ''
SET @HTML_ABNORMAL_LABS = ''
FETCH NEXT FROM VALUE INTO @ENC_DATE,@HEIGHT,@WGT,@BSA,@DOSE,@CYCLE,@TREAT,@WBC,@ANC,@HGB,@PLT,@MCV,@Na,@K,@Cr,@BUN,@Ca,@MG,@CBC_DATE,@XRT,@ABNORMAL_LABS
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@HTML_DATE = @HTML_DATE + '<td><center><b>' + CAST(@ENC_DATE AS VARCHAR(30)) + '</b></center></td>',
@HTML_CYCLE = @HTML_CYCLE + '<td><center>'+COALESCE(@CYCLE,'Missing Cycle Information')+'</center></td>',
@HTML_HEIGHT = @HTML_HEIGHT + '<td><center>'+COALESCE(@HEIGHT,'Missing Height Information')+'</center></td>',
@HTML_WGT = @HTML_WGT + '<td><center>'+COALESCE(@WGT,'Missing Weight Information')+'</center></td>',
@HTML_BSA = @HTML_BSA + '<td><center>'+COALESCE(@BSA,'Missing BSA Information')+'</center></td>',
@HTML_TREAT = @HTML_TREAT +
CASE
WHEN @TREAT_CT =
CASE
WHEN @COUNT > 1 THEN CEILING(SUM(CAST(@COUNT AS NUMERIC(4,2))) / 2)
WHEN @COUNT = 1 THEN 1
END THEN '<td colspan="'+CAST(@COUNT AS VARCHAR(4))+'"><center><b>' + COALESCE(@TREAT,'No Treatment Information') +'</b></center></td>'
ELSE ''
END,
@HTML_XRT = @HTML_XRT + '<td><center>'+COALESCE(@XRT,'')+'</center></td>',
@HTML_CBC_DATE = @HTML_CBC_DATE + '<td><center>'+COALESCE(cast(@CBC_DATE AS VARCHAR(30)),'')+'</center></td>',
@HTML_WBC = @HTML_WBC + COALESCE(@WBC,'<td><center></center></td>'),
@HTML_ANC = @HTML_ANC + COALESCE(@ANC,'<td><center></center></td>'),
@HTML_HGB = @HTML_HGB + COALESCE(@HGB,'<td><center></center></td>'),
@HTML_PLT = @HTML_PLT + COALESCE(@PLT,'<td><center></center></td>'),
@HTML_MCV = @HTML_MCV + COALESCE(@MCV,'<td><center></center></td>'),
@HTML_Na = @HTML_Na + COALESCE(@NA,'<td><center></center></td>'),
@HTML_K = @HTML_K + COALESCE(@K,'<td><center></center></td>'),
@HTML_Cr = @HTML_Cr + COALESCE(@CR,'<td><center></center></td>'),
@HTML_BUN = @HTML_BUN + COALESCE(@BUN,'<td><center></center></td>'),
@HTML_Ca = @HTML_Ca + COALESCE(@CA,'<td><center></center></td>'),
@HTML_MG = @HTML_MG + COALESCE(@MG,'<td><center></center></td>'),
@HTML_ABNORMAL_LABS = @HTML_ABNORMAL_LABS + '<td><center>'+COALESCE(@ABNORMAL_LABS,'')+'</center></td>',
@HTML_DOSE = @HTML_DOSE + '<td><center>'+REPLACE(COALESCE(@DOSE,'No Dose Information'),', ',',<br> ')+'
</center></td>'
SET @TREAT_CT = @TREAT_CT + 1
FETCH NEXT FROM VALUE INTO @ENC_DATE,@HEIGHT,@WGT,@BSA,@DOSE,@CYCLE,@TREAT,@WBC,@ANC,@HGB,@PLT,@MCV,@Na,@K,@Cr,@BUN,@Ca,@MG,@CBC_DATE,@XRT,@ABNORMAL_LABS
SELECT
@HTML_OUTPUT = '<table border=2 cellpadding="2" align="center">
<tr><td><b>Date:</b></td><b>' + COALESCE(@HTML_DATE,'DATE ERROR') +
'</b></tr><tr><td><b>Cycle:</b></td>' + COALESCE(@HTML_CYCLE,'CYCLE ERROR') +
'</tr><tr><td><b>Treatment:</b></td><b>' + COALESCE(@HTML_TREAT,'TREAT ERROR') +
'</b></tr><tr><td><b>Height:</b></td>' + COALESCE(@HTML_HEIGHT,'HEIGHT ERROR') +
'</tr><tr><td><b>Weight:</b></td>' + COALESCE(@HTML_WGT,'WGT ERROR') +
'</tr><tr><td><b>BSA:</b></td>' + COALESCE(@HTML_BSA,'BSA ERROR') +
'</tr><tr><td><b>XRT:</b></td>' + COALESCE(@HTML_XRT,'XRT ERROR') +
'</tr><tr><td><b>CBC Date:</b></td>' + COALESCE(@HTML_CBC_DATE,'CBC DATE ERROR') +
'</tr><tr><td><b>WBC:</b></td>' + COALESCE(@HTML_WBC,'WBC ERROR') +
'</tr><tr><td><b>ANC:</b></td>' + COALESCE(@HTML_ANC,'ANC ERROR') +
'</tr><tr><td><b>HGB:</b></td>' + COALESCE(@HTML_HGB,'HGB ERROR') +
'</tr><tr><td><b>PLT:</b></td>' + COALESCE(@HTML_PLT,'PLT ERROR') +
'</tr><tr><td><b>MCV:</b></td>' + COALESCE(@HTML_MCV,'MCV ERROR') +
'</tr><tr><td><b>Na:</b></td>' + COALESCE(@HTML_Na,'Na ERROR') +
'</tr><tr><td><b>K:</b></td>' + COALESCE(@HTML_K,'K ERROR') +
'</tr><tr><td><b>Cr:</b></td>' + COALESCE(@HTML_Cr,'Cr ERROR') +
'</tr><tr><td><b>BUN:</b></td>' + COALESCE(@HTML_BUN,'BUN ERROR') +
'</tr><tr><td><b>Ca:</b></td>' + COALESCE(@HTML_Ca,'Ca ERROR') +
'</tr><tr><td><b>MG:</b></td>' + COALESCE(@HTML_MG,'MG ERROR') +
'</tr><tr><td><b>Abnormal Labs:</b></td>' + COALESCE(@HTML_ABNORMAL_LABS,'ABNORMAL_LABS ERROR') +
'</tr><tr><td><b>Dose:</b></td>' + COALESCE(@HTML_DOSE,'DOSE ERROR') + '</tr></table><br><br><br>'
END
CLOSE VALUE
DEALLOCATE VALUE
SELECT
@HTML_OUTPUT2 += @HTML_OUTPUT
FETCH NEXT FROM TREAT INTO @TREAT,@COUNT
END
CLOSE TREAT
DEALLOCATE TREAT
/* FINAL Output*/
SELECT
@HTML_HEADER + @HTML_OUTPUT2 + @HTML_TRAILER
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply