November 4, 2016 at 5:30 am
I have the following data with client account decision and status
Clientfields value
111Decisionaccept
111Statusdeposited
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract
115DecisionConditional Accept
115Statusdeposited
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract
118DecisionConditional Accept
I would like to get
records whose decision is 'accept' and also with status ('deposited', 'contract')
and get the records whose decision is 'conditional accept' and also with status ('deposited', 'contract')
in two different columns.
please help in writing case statements for this.
November 4, 2016 at 5:54 am
sindhupavani123 (11/4/2016)
I have the following data with client account decision and status
Clientfields value
111Decisionaccept
111Statusdeposited
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract
115DecisionConditional Accept
115Statusdeposited
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract
118DecisionConditional Accept
I would like to get
records whose decision is 'accept' and also with status ('deposited', 'contract')
and get the records whose decision is 'conditional accept' and also with status ('deposited', 'contract')
in two different columns.
please help in writing case statements for this.
Can you show how you would like the output to look, based on your sample data?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2016 at 5:56 am
Once you normalise your EAV-structured data, it's easy. The best way is to change your database design so that your tables are normalised. If that's not an option (sometimes we just have to work with what we have) then you can use a CTE to normalise the data on the fly, and query the CTE. See this article for how to normalise an EAV design.
John
November 4, 2016 at 5:58 am
sindhupavani123 (11/4/2016)
I have the following data with client account decision and status
Clientfields value
111Decisionaccept
111Statusdeposited
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract
115DecisionConditional Accept
115Statusdeposited
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract
118DecisionConditional Accept
I would like to get
records whose decision is 'accept' and also with status ('deposited', 'contract')
and get the records whose decision is 'conditional accept' and also with status ('deposited', 'contract')
in two different columns.
please help in writing case statements for this.
Use a standard comparison in the WHERE clause to capture rows matching the required "decision", and an EXISTS check for the status.
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
November 4, 2016 at 6:05 am
My output should look like this:
Clientfields value NewCol
111Decisionaccept
111Statusdeposited 111
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract 114
115DecisionConditional Accept
115Statusdeposited 115
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract 118
118DecisionConditional Accept
Tried writing the query but I'm unable to get the correct output
November 4, 2016 at 6:13 am
sindhupavani123 (11/4/2016)
My output should look like this:Clientfields value NewCol
111Decisionaccept
111Statusdeposited 111
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract 114
115DecisionConditional Accept
115Statusdeposited 115
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract 118
118DecisionConditional Accept
Tried writing the query but I'm unable to get the correct output
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- SAMPLE DATA SET
;WITH SAMPLE_DATA(Client,fields,value) AS
( SELECT X.Client,X.fields,X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X(Client,fields,value)
)
-- CROSS-TAB QUERY
--;WITH
,BASE_DATA AS
(
SELECT
SD.Client
,MAX(CASE WHEN SD.fields = 'Status' THEN SD.value END ) AS Status
,MAX(CASE WHEN SD.fields = 'Decision' THEN SD.value END ) AS Decision
FROM SAMPLE_DATA SD
GROUP BY SD.Client
)
,CONDITIONAL_CLIENTS AS
(
SELECT
BD.Client
,BD.Status
FROM BASE_DATA BD
WHERE
(
BD.Decision = 'accept'
AND
BD.Status = 'contract'
)
OR
(
BD.Decision = 'conditional accept'
AND
BD.Status IN ('deposited', 'contract')
)
)
SELECT
SD.Client
,SD.fields
,SD.value
,CC.Client
FROM SAMPLE_DATA SD
LEFT OUTER JOIN CONDITIONAL_CLIENTS CC
ON SD.Client = CC.Client
AND SD.value = CC.Status;
Output
Client fields value Client
----------- -------- ------------------ -------
111 Decision accept NULL
111 Status deposited NULL
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept NULL
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
November 4, 2016 at 6:31 am
ChrisM@Work (11/4/2016)
sindhupavani123 (11/4/2016)
I have the following data with client account decision and status
Clientfields value
111Decisionaccept
111Statusdeposited
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract
115DecisionConditional Accept
115Statusdeposited
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract
118DecisionConditional Accept
I would like to get
records whose decision is 'accept' and also with status ('deposited', 'contract')
and get the records whose decision is 'conditional accept' and also with status ('deposited', 'contract')
in two different columns.
please help in writing case statements for this.
Use a standard comparison in the WHERE clause to capture rows matching the required "decision", and an EXISTS check for the status.
This output disagrees with your initial post, which stated that you only want the records that have a certain decision and also have a certain status. This post says you want ALL the records, but only create a new field with the Client number in it when those conditions are fulfilled, and only on the status records. Please clarify.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 6:34 am
This also matches the somewhat ambiguous and vague requirements:
;WITH SAMPLE_DATA(Client,fields,value) AS
( SELECT X.Client,X.fields,X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X(Client,fields,value)
)
SELECT
b.Client, b.fields, b.[value],
Result = x.Client
FROM SAMPLE_DATA b
OUTER APPLY (
SELECT i.Client
FROM SAMPLE_DATA i
WHERE i.Client = b.Client
AND i.fields = 'Decision'
AND i.[Value] IN ('accept', 'Conditional Accept')
AND b.fields = 'Status'
AND b.[Value] IN ('deposited','contract')
) x
Thanks EE for the sample data setup 😉
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
November 4, 2016 at 6:35 am
Eirikur Eiriksson (11/4/2016)
sindhupavani123 (11/4/2016)
My output should look like this:Clientfields value NewCol
111Decisionaccept
111Statusdeposited 111
112decisionaccept
113Decisionaccept
114Decisionaccept
114Statuscontract 114
115DecisionConditional Accept
115Statusdeposited 115
116DecisionConditional Accept
117DecisionConditional Accept
118Statuscontract 118
118DecisionConditional Accept
Tried writing the query but I'm unable to get the correct output
Quick suggestion
😎
USE TEEST;GO
SET NOCOUNT ON;
-- SAMPLE DATA SET
;WITH SAMPLE_DATA(Client,fields,value) AS
( SELECT X.Client,X.fields,X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X(Client,fields,value)
)
-- CROSS-TAB QUERY
--;WITH
,BASE_DATA AS
(
SELECT
SD.Client
,MAX(CASE WHEN SD.fields = 'Status' THEN SD.value END ) AS Status
,MAX(CASE WHEN SD.fields = 'Decision' THEN SD.value END ) AS Decision
FROM SAMPLE_DATA SD
GROUP BY SD.Client
)
,CONDITIONAL_CLIENTS AS
(
SELECT
BD.Client
,BD.Status
FROM BASE_DATA BD
WHERE
(
BD.Decision = 'accept'
AND
BD.Status = 'contract'
)
OR
(
BD.Decision = 'conditional accept'
AND
BD.Status IN ('deposited', 'contract')
)
)
SELECT
SD.Client
,SD.fields
,SD.value
,CC.Client
FROM SAMPLE_DATA SD
LEFT OUTER JOIN CONDITIONAL_CLIENTS CC
ON SD.Client = CC.Client
AND SD.value = CC.Status;
Output
Client fields value Client----------- -------- ------------------ -------
111 Decision accept NULL
111 Status deposited NULL
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept NULL
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
Your output misses Client 111, who does meet the required conditions. However, the original poster also has stated output that disagrees with their initial statement, and I've asked for clarification.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 6:52 am
sgmunson (11/4/2016)
Your output misses Client 111, who does meet the required conditions. However, the original poster also has stated output that disagrees with their initial statement, and I've asked for clarification.
Missed the 'deposited' in the where clause, simply change
BD.Status = 'contract'
to
BD.Status IN ('deposited', 'contract')
😎
Corrected query
USE TEEST;
GO
SET NOCOUNT ON;
-- SAMPLE DATA SET
;WITH SAMPLE_DATA(Client,fields,value) AS
( SELECT X.Client,X.fields,X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X(Client,fields,value)
)
-- CROSS-TAB QUERY
--;WITH
,BASE_DATA AS
(
SELECT
SD.Client
,MAX(CASE WHEN SD.fields = 'Status' THEN SD.value END ) AS Status
,MAX(CASE WHEN SD.fields = 'Decision' THEN SD.value END ) AS Decision
FROM SAMPLE_DATA SD
GROUP BY SD.Client
)
,CONDITIONAL_CLIENTS AS
(
SELECT
BD.Client
,BD.Status
FROM BASE_DATA BD
WHERE
(
BD.Decision = 'accept'
AND
BD.Status IN ('deposited', 'contract')
)
OR
(
BD.Decision = 'conditional accept'
AND
BD.Status IN ('deposited', 'contract')
)
)
SELECT
SD.Client
,SD.fields
,SD.value
,CC.Client
FROM SAMPLE_DATA SD
LEFT OUTER JOIN CONDITIONAL_CLIENTS CC
ON SD.Client = CC.Client
AND SD.value = CC.Status;
Output
Client fields value Client
----------- -------- ------------------ ---------
111 Decision accept NULL
111 Status deposited 111
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept NULL
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
November 4, 2016 at 6:57 am
And here's an alternative that achieves the desired output (however conflicting with the original post that may be...), and shows the performance characteristics, as I'm guessing this sample data would likely be much more extensive in the real world, and may well have other values not shown here...
CREATE TABLE #TEST_DATA (
Client int,
Fields varchar(30),
Value varchar(30)
);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Client_INCLUDE_Fields_Value ON #TEST_DATA
(
Client ASC
)
INCLUDE (Fields, Value);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Fields_Value_INCLUDE_Client ON #TEST_DATA
(
Fields ASC,
Value ASC
)
INCLUDE (Client);
INSERT INTO #TEST_DATA (Client, Fields, Value)
VALUES(111, 'Decision', 'accept'),
(111, 'Status', 'deposited'),
(112, 'decision', 'accept'),
(113, 'Decision', 'accept'),
(114, 'Decision', 'accept'),
(114, 'Status', 'contract'),
(115, 'Decision', 'Conditional Accept'),
(115, 'Status', 'deposited'),
(116, 'Decision', 'Conditional Accept'),
(117, 'Decision', 'Conditional Accept'),
(118, 'Status', 'contract'),
(118, 'Decision', 'Conditional Accept');
DBCC FREEPROCCACHE;
SET STATISTICS IO, TIME ON;
WITH ALL_DATA AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Client ORDER BY CASE Fields WHEN 'Decision' THEN 1 WHEN 'Status' THEN 2 ELSE 9999 END, Value) AS RN
FROM #TEST_DATA
)
SELECT A1.Client, A1.Fields, A1.Value,
CASE
WHEN (
(A1.Fields = 'Decision' AND A1.Value IN ('accept', 'Conditional Accept'))
AND
(A2.Fields = 'Status' AND A2.Value IN ('contract', 'deposited'))
)
OR
(
(A2.Fields = 'Decision' AND A2.Value IN ('accept', 'Conditional Accept'))
AND
(A1.Fields = 'Status' AND A1.Value IN ('contract', 'deposited'))
) THEN A1.Client
ELSE NULL
END AS NewCol
FROM ALL_DATA AS A1
LEFT OUTER JOIN ALL_DATA AS A2
ON A1.Client = A2.Client
AND A1.RN = 2
AND A2.RN = 1
ORDER BY A1.Client, A1.Fields;
SET STATISTICS TIME, IO OFF;
DROP TABLE #TEST_DATA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 6:59 am
ChrisM@Work (11/4/2016)
This also matches the somewhat ambiguous and vague requirements:
;WITH SAMPLE_DATA(Client,fields,value) AS( SELECT X.Client,X.fields,X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X(Client,fields,value)
)
SELECT
b.Client, b.fields, b.[value],
Result = x.Client
FROM SAMPLE_DATA b
OUTER APPLY (
SELECT i.Client
FROM SAMPLE_DATA i
WHERE i.Client = b.Client
AND i.fields = 'Decision'
AND i.[Value] IN ('accept', 'Conditional Accept')
AND b.fields = 'Status'
AND b.[Value] IN ('deposited','contract')
) x
Interestingly the Filter operator (where clause) cost in the execution plan jumps up from 7% to 64% when combining all the conditions instead of targeting and combining narrower conditions.
😎
Didn't test this case specifically though and the costs in the execution plans and comparison between those can be a little misleading.
Thanks EE for the sample data setup 😉
You are very welcome Chris 😀
November 4, 2016 at 7:09 am
sgmunson (11/4/2016)
And here's an alternative that achieves the desired output (however conflicting with the original post that may be...), and shows the performance characteristics, as I'm guessing this sample data would likely be much more extensive in the real world, and may well have other values not shown here...
CREATE TABLE #TEST_DATA (
Client int,
Fields varchar(30),
Value varchar(30)
);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Client_INCLUDE_Fields_Value ON #TEST_DATA
(
Client ASC
)
INCLUDE (Fields, Value);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Fields_Value_INCLUDE_Client ON #TEST_DATA
(
Fields ASC,
Value ASC
)
INCLUDE (Client);
INSERT INTO #TEST_DATA (Client, Fields, Value)
VALUES(111, 'Decision', 'accept'),
(111, 'Status', 'deposited'),
(112, 'decision', 'accept'),
(113, 'Decision', 'accept'),
(114, 'Decision', 'accept'),
(114, 'Status', 'contract'),
(115, 'Decision', 'Conditional Accept'),
(115, 'Status', 'deposited'),
(116, 'Decision', 'Conditional Accept'),
(117, 'Decision', 'Conditional Accept'),
(118, 'Status', 'contract'),
(118, 'Decision', 'Conditional Accept');
DBCC FREEPROCCACHE;
SET STATISTICS IO, TIME ON;
WITH ALL_DATA AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Client ORDER BY CASE Fields WHEN 'Decision' THEN 1 WHEN 'Status' THEN 2 ELSE 9999 END, Value) AS RN
FROM #TEST_DATA
)
SELECT A1.Client, A1.Fields, A1.Value,
CASE
WHEN (
(A1.Fields = 'Decision' AND A1.Value IN ('accept', 'Conditional Accept'))
AND
(A2.Fields = 'Status' AND A2.Value IN ('contract', 'deposited'))
)
OR
(
(A2.Fields = 'Decision' AND A2.Value IN ('accept', 'Conditional Accept'))
AND
(A1.Fields = 'Status' AND A1.Value IN ('contract', 'deposited'))
) THEN A1.Client
ELSE NULL
END AS NewCol
FROM ALL_DATA AS A1
LEFT OUTER JOIN ALL_DATA AS A2
ON A1.Client = A2.Client
AND A1.RN = 2
AND A2.RN = 1
ORDER BY A1.Client, A1.Fields;
SET STATISTICS TIME, IO OFF;
DROP TABLE #TEST_DATA;
Nice in the terms of only doing 2 scans but the down side is that the execution plan has two sort operators, on for each scan which are quite costly in the absence of a POC index for the ROW_NUMBER function.
😎
November 4, 2016 at 8:00 am
Thank you all for the help.
What should i do if i want two columns with below output?
Client fields value ClientClient
----------- -------- ------------------ ---------
111 Decision accept NULL
111 Status deposited 111
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept Null
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
November 4, 2016 at 8:02 am
Thank you for help.
What should i do to get two columns with below expected output.
Client fields value Client1Client2
----------- -------- ------------------ ---------
111 Decision accept NULL
111 Status deposited 111
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept Null
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply