[AccessD] Progressive SQL Update Query
Ryan W
wrwehler at gmail.com
Wed Feb 22 09:03:37 CST 2023
Well, I can't share all the code there...
So we take the data off the instrument (in a csv or txt file) depending on
the instrument, our Access FE parses it and sends it up.
Every time a sample is imported from the flat file, every analyte that the
client requests is ON by default, it's up to the analyst to discern whether
the data is usable, hence why some things get run multiple times.
The goal is so have a progressive/cascading effect. Sample 1 comes in,
Chromium and Nickel get turned off, Sample 1 (#2) comes in, user hits
toggle and Chromium and Nickel are all that are on. User realises Chromium
needs another look so Sample 1 #2 comes in, user hits toggle and ideally
only Chromium gets turned on for #3. As stated the current query resets
the second run, there could be a third and fourth but I think the way the
query is currently written anything past 2 is basically dead without hand
holding from the analyst.
Here's the code that basically aggregates the SEL list for the samples:
;WITH Analytes AS (
SELECT MAX(CAST(arsr.Rpt AS INT)) AS MOA --Rpt is a bit field, the
value is either 1 or 0
,arsr.Analyte
,ars.TestNum --the testnum that contains the master list of
requested analytes
,tars.SeqNo
FROM tmpAnalRunSeq AS tars --staging table for data the
user is working on, to pare down the dataset
INNER JOIN AnalRunSeq AS ars
ON ars.TestNum = tars.TestNum
INNER JOIN AnalRunSeqResult AS arsr
ON ars.SeqNo = arsr.SeqNo
WHERE tars.SampType = 'SAMP'
AND tars.WSID = @MyWSID
AND tars.AnalDate > ars.AnalDate
AND arsr.SEL = 1
AND arsr.AnalyteType IN ('A' ,'C')
GROUP BY
arsr.Analyte
,ars.TestNum
,tars.SeqNo
)
UPDATE arsr
SET arsr.Rpt = 'False'
FROM AnalRunSeqResult AS arsr
INNER JOIN tmpAnalRunSeq AS tars
ON tars.SeqNo = arsr.SeqNo
INNER JOIN Analytes
ON Analytes.Analyte = arsr.Analyte
AND Analytes.SeqNo = arsr.SeqNo
WHERE arsr.AnalyteType IN ('A' ,'C')
AND arsr.SEL = 'True'
AND arsr.Rpt = 'True'
AND tars.Validated = 'False'
AND tars.WSID = @MyWSID
AND Analytes.MOA = 'True'
The way this query is written is it's turning OFF what's already ON
(previously) since everything is ON as a default.
I was using a correlated subquery in the update statement to find
arsr.Analyte IN (SELECT DISTINCT Analyte FROM AnalRunSeqResult where
TestNum = tars.TestNum and tars.WSID = @MyWSID and arsr.Rpt = 'True') but I
re-wrote it to use a CTE because it felt a little easier to read (to my
eyes)
On Wed, Feb 22, 2023 at 8:50 AM James Button via AccessD <
accessd at databaseadvisors.com> wrote:
> Maybe post the SQL that sets up the entry set for the sampling.
> And then that that manipulates the first sampling needs.
> And then that that manipulates the second needs.
> And then that that manipulates the third (and final?) sampling needs.
>
> As well as whatever script is used to generate the sampling process
> request from
> the data.
>
> Without that sort of detail your post is pretty much a statement along the
> lines
> of:
>
> "Somebody has made at least one error in the design and/or scripting."
>
> JimB
>
>
> -----Original Message-----
> From: AccessD
> <accessd-bounces+jamesbutton=blueyonder.co.uk at databaseadvisors.com> On
> Behalf Of
> Ryan W
> Sent: Wednesday, February 22, 2023 2:36 PM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Progressive SQL Update Query
>
> https://i.imgur.com/34g0Srj.png
>
>
> Here is an image of an example I was working on yesterday:
>
> Sample 1 had Chromium and Nickel off
>
> So hitting "toggle" turned Chromium and Nickel ON on #2 (accurate)
>
> And subsequently everything went OFF for Sample #3 (also accurate based on
> current query logic)
>
> The user then toggled Chromium off on Sample #2 and hit Toggle and it
> turned his Chromium back on for #2, and #3 was still all off.
>
> I guess the gist of it is if the list is hand manipulated it resets the
> list and anything > run #2 gets entirely turned off.
>
>
>
>
>
> On Wed, Feb 22, 2023 at 8:09 AM Ryan W <wrwehler at gmail.com> wrote:
>
> > I'm not sure how to explain this without explaining poorly or not getting
> > the message right.
> >
> > We have data we get from clients and they ask us to analyze them for
> trace
> > metals,
> > so the client list wants to know about 10 trace metals out of 30.
> >
> > How it currently works is we run the analysis and import the data, our
> > software pulls it in and matches the requested list to the analysis and
> > turns off all trace metals not requested.
> >
> > Sometimes one (or more) of the metals requires re-analysis for whatever
> > reason. So the user toggles the 'bad' hits out of the sequence. (say they
> > turn off Lead and Copper)
> >
> > So we re-run it and import it and if the data is good, they turn off
> > everything BUT Lead and Copper.
> >
> > In some cases, we have a third analysis. (sometimes we have to dilute the
> > sample to get a good reading)...
> >
> > So say in that second analysis, Copper was not good. So they turn it
> off..
> > the third Analysis gets copper ONLY turned on for reporting.
> >
> >
> > Right now the toggling is all done by the user.
> >
> > I want to write a progressive query that looks at the aggregated analysis
> > for anything requested (SEL), that's OFF and turn it ON (or vice versa)
> on
> > the following analysis (this works).
> >
> > However, if there's a third analysis in the sequence everything ends up
> > turned off because Analysis 1 and 2 meet the requested analysis.
> >
> > So in this example:
> >
> > Sample 1's list contains all trace metals, but lead and copper are off
> > Sample 2's list after hitting "toggle" button contains lead and copper
> and
> > everything else is off.
> > Sample 3 is completely off because the aggregated list of required trace
> > metals are satisfied by 1 and 2.
> >
> > However Sample 2 needs user intervention and turns off Copper. I want
> > them to be able to hit "Toggle" again and turn ON Copper for Sample 3,
> but
> > leave Copper OFF on Sample 2, even though Sample 1 doesn't satisfy the
> > copper requirement.
> >
> > My query logic works fine if there's only 1 rerun, but if there's more
> > than one is where the problem lies.
> >
> >
> > I don't know if I need to use a cursor or a recordset and a looping
> > mechanism to make this work instead of a straight up batch query?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
More information about the AccessD
mailing list