Update based on multiple conditions in a single pass?

Question

Post reply

Update based on multiple conditions in a single pass?

bleroy

Hall of Fame

Points: 3052
More actions
July 22, 2014 at 4:57 am

#296644

Hi,
I have the following issue which I'm trying to resolve:
essentially, I have a table of measurements on one side, then a table containing threshold values with associated RAG (Red, Amber, or Green) status on the other side.
I am then trying to create a "RAG status table" in a single pass (table scan).
My initial approach was to determine which measurements where in the red, then do the same for the amber RAG (which would include the "Reds") but then exclude the Reds already identified (because you can't have 2 RAG statuses at once) - and then I can perform a third update query on the assumption that everything that remains is then "Green" ... this involves at least 2 (or even 3) scans of my data table which is huge (billions of rows) , I'm hoping someone can show me how to do this in a single pass.
I am hoping someone can show me how to determine the various statuses in a single table if that's at all possible? The select statement at the bottom of the code window shows how I would retrieve the Red statuses.
Thanks a lot for looking at this,
B
DDL & DML here below to create a bunch of tables and data to play with.
-- *****
CREATE TABLE [DimExServer](
[ServerID] [int] NOT NULL,
[Server] [varchar](100) NOT NULL
)
GO
INSERT INTO [DimExServer] (ServerID, Server) VALUES (1, 'Server01')
INSERT INTO [DimExServer] (ServerID, Server) VALUES (2, 'Server02')
INSERT INTO [DimExServer] (ServerID, Server) VALUES (3, 'Server03')
-- *****
CREATE TABLE [DimExMetric](
[MetricID] [int] NOT NULL,
[BaseMetric] [varchar](100) NULL
)
GO
INSERT INTO [DimExMetric] (MetricID, BaseMetric) VALUES (1, 'Metric01')
INSERT INTO [DimExMetric] (MetricID, BaseMetric) VALUES (2, 'Metric02')
INSERT INTO [DimExMetric] (MetricID, BaseMetric) VALUES (3, 'Metric03')
-- *****
CREATE TABLE [DataTable](
[ServerID] [int] NOT NULL,
[MetricID] [int] NOT NULL,
[MetricValue] [float] NULL,
)
GO
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (1, 1, 73) -- which will result in a green RAG
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (1, 2, 87)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (1, 3, 99)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (2, 1, 96)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (2, 2, 97)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (2, 3, 91)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (3, 1, 56)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (3, 2, 89)
INSERT INTO [DataTable] (ServerID, MetricID, MetricValue) VALUES (3, 3, 97)
-- *****
CREATE TABLE [DefaultRAGRules](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[MetricID] [int] NOT NULL,
[Threshold] [float] NOT NULL,
[RAGLevel] [varchar](5) NOT NULL,
CONSTRAINT [DefaultRAGRules_MetricRAG] UNIQUE NONCLUSTERED
(
[MetricID] ASC,
[Threshold] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
---- Insert some data into the DefaultRAGRules table
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 1, 75, 'Amber')
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 1, 90, 'Red')
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 2, 75, 'Amber')
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 2, 90, 'Red')
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 3, 85, 'Amber')
INSERT INTO [DefaultRAGRules] (EffectiveDate, MetricID, Threshold, RAGLevel) VALUES (GETDATE(), 3, 95, 'Red')
-------
SELECT
cp.ServerID
, ds.server
, cp.MetricID
, dm.BaseMetric
, cp.MetricValue
, 'Red' AS RAGSTATUS
--INTO RAGTemp
FROM DataTable cp
LEFT JOIN DimExServer ds ON ds.ServerID = cp.ServerID
LEFT JOIN DimExMetric dm ON dm.MetricID = cp.MetricID
INNER JOIN DefaultRAGRules drr ON drr.MetricID = cp.MetricID
AND cp.MetricValue >= drr.threshold
AND drr.RAGLevel = 'Red'-- this flters for the metrics we actually care about

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this. It might give you some ideas.

SELECT

cp.ServerID

, ds.[server]

, cp.MetricID

, dm.BaseMetric

, cp.MetricValue

, x.RAGLevel AS RAGSTATUS

--INTO RAGTemp

FROM DataTable cp

LEFT JOIN DimExServer ds

ON ds.ServerID = cp.ServerID

LEFT JOIN DimExMetric dm

ON dm.MetricID = cp.MetricID

OUTER APPLY (

SELECT TOP 1 drr.RAGLevel

FROM DefaultRAGRules drr

WHERE drr.MetricID = cp.MetricID

AND drr.threshold <= cp.MetricValue

ORDER BY drr.threshold DESC

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For 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

djj SSCoach Points: 18831 More actions · Answer 2

I have a way but it will be shot down as inefficient.

Left Join to the DefaultRAGRules table two times once for Red and once for Amber. Then use a case statement

CASE WHEN cp.MetricValue >= drr1.threshold THEN drr1.RAGLevel

WHEN cp.MetricValue >= drr2.threshold THEN drr2.RAGLevel

ELSE 'Green'

END AS RAGSTATUS

This should work but as stated is inefficient.