Mind Boggling query.

Question

Mind Boggling query.

defineamol

Valued Member

Points: 72
More actions
May 13, 2018 at 12:39 am

#334695

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

defineamol - Sunday, May 13, 2018 12:39 AM
Task Table
Server ID Action Task ID
123 43 1
123 43 3
123 43 2
123 43 4
123 43 5
123 45 1
123 45 4
123 45 3
123 45 2
123 45 5
234 43 5
234 43 3
234 43 4
234 43 2
234 43 1
Task Template
TaskID TaskPredecessor
2 1
3 2
4 2
5 4
6 5
Output
Server ID Action Task ID
123 43 1
123 43 3
123 43 2
123 43 4
123 45 1
234 43 3
234 43 4
234 43 2
234 43 1
Preface: The query should look at the combination of the ServerID and Action ID pick up the task ID and check if it has a predecessor. If it has a predecessor then check the server action combination with that predecessor ID with status completed or active. If completed then check next.
Output: The output should print the Server action combination along with task ID and status. Constraint: it should print all the completed task and should stop printing at the latest active task.
ASK: Cant seem to get my head around it. Please post a sample query. Note: No use of cursors.

Homework?
😎

Post the DDL (create table) scripts, sample data as an insert statement and what you have tried so far please.

Thom A SSC Guru Points: 99126 More actions · Answer 2

You mention the status of the task is your description. There is no column status. Is it missing? If not, how do we know the status of the task without having said information?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

defineamol Valued Member Points: 72 More actions · Answer 3

defineamol

Valued Member

Points: 72

May 13, 2018 at 10:20 am

#1990289

Thom A SSC Guru Points: 99126 More actions · Answer 4

defineamol - Sunday, May 13, 2018 10:20 AM
Task Table
Server ID Action Task ID Status
123 43 1 completed
123 43 3 completed
123 43 2 completed
123 43 4 active
123 43 5 active
123 45 1 active
123 45 4 active
123 45 3 active
123 45 2 active
123 45 5 active
234 43 5 active
234 43 3 active
234 43 4 completed
234 43 2 completed
234 43 1 completed
Task Template
TaskID TaskPredecessor
2 1
3 2
4 2
5 4
6 5
Output
Server ID Action Task ID Status
123 43 1 completed
123 43 3 completed
123 43 2 completed
123 43 4 active
123 45 1 active
234 43 3 active
234 43 4 completed
234 43 2 completed
234 43 1 completed
Preface: The query should look at the combination of the ServerID and Action ID pick up the task ID and check if it has a predecessor. If it has a predecessor then check the server action combination with that predecessor ID with status completed or active. If completed then check next.
Output: The output should print the Server action combination along with task ID and status. Constraint: it should print all the completed task and should stop printing at the latest active task.
ASK: Cant seem to get my head around it. Please post a sample query. Note: No use of cursors.

That'swwhat you originally posted. Could you answer Eirikur's and my questions please?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Also, help us help you when we finally determine what you're actually asking. Please see the article at the first link under "Helpful Links" in my signature line below. Thanks.

--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)

defineamol Valued Member Points: 72 More actions · Answer 6

Hi Thom A and Jeff,

First of all let me thank you very much for replying to the post. I owe you guys. I have worked on the query a lil bit and came up with an intermediate tmp table below. The sample data and output are displayed below. Please help. Thx in advance.
Note: This is not a home work assignment. I wont cheat. 🙂

create table tmp ( ROW [INT] ,EntityID [INT], PlanID [INT] , ActionID [INT] ,TaskTemplateID [INT] , [status] [varchar](100), TaskTemplatePred [INT] )

insert into tmp values ('1', '89616', '1575', '56', '288', 'Cancelled', '285');
insert into tmp values ('2', '89616', '1575', '56', '288', 'Cancelled', '285');
insert into tmp values ('3', '89616', '1575', '56', '289', 'Cancelled', '288');
insert into tmp values ('4', '89616', '1575', '56', '287', 'Cancelled', '288');
insert into tmp values ('5', '8976', '196', '67', '118', 'Active', '115');
insert into tmp values ('6', '8976', '196', '67', '118', 'Active', '118');
insert into tmp values ('7', '8976', '196', '67', '119', 'Active', '118');
insert into tmp values ('8', '8976', '195', '26', '176', 'Complete', '172');
insert into tmp values ('9', '8976', '195', '26', '175', 'Complete', '172');
insert into tmp values ('10', '8976', '195', '26', '175', 'Complete', '175');
insert into tmp values ('11', '8976', '195', '26', '176', 'Complete', '175');
insert into tmp values ('12', '8976', '195', '26', '178', 'Active', '176');
insert into tmp values ('13', '8976', '195', '26', '178', 'Active', '178');
insert into tmp values ('14', '87172', '826', '57', '251', 'Not Required', '259');
insert into tmp values ('15', '87172', '826', '57', '252', 'Not Required', '259');
insert into tmp values ('16', '87172', '826', '57', '256', 'Not Required', '251');
insert into tmp values ('17', '87172', '826', '57', '255', 'Not Required', '252');
insert into tmp values ('18', '87172', '826', '57', '255', 'Not Required', '251');
insert into tmp values ('19', '87172', '826', '57', '267', 'Complete', '259');
insert into tmp values ('20', '87172', '826', '57', '261', 'Active', '258');
insert into tmp values ('21', '87172', '826', '57', '262', 'Complete', '259');
insert into tmp values ('22', '87172', '826', '57', '266', 'Not Required', '256');
insert into tmp values ('23', '87172', '826', '57', '265', 'Not Required', '255');
insert into tmp values ('24', '87172', '826', '57', '265', 'Not Required', '266');
insert into tmp values ('25', '87172', '826', '57', '268', 'Not Required', '265');
insert into tmp values ('26', '87172', '826', '57', '269', 'Not Required', '255');
insert into tmp values ('27', '87172', '826', '57', '287', 'Complete', '266');
insert into tmp values ('28', '87172', '826', '57', '281', 'Complete', '287');
insert into tmp values ('29', '87172', '826', '57', '282', 'Complete', '281');
insert into tmp values ('30', '87185', '1295', '17', '58', 'Complete', '57');
insert into tmp values ('31', '87185', '1295', '17', '59', 'Complete', '58');
insert into tmp values ('32', '87185', '1295', '17', '162', 'Active', '58');
insert into tmp values ('33', '87185', '1295', '17', '166', 'Active', '58');

For Entity 89616: All are cancelled hence data should not be displayed in the output.
For Entity 8976 and Action ID 196: The first entry is Active. Hence only one entry which is first entry should be displayed in the output.
For Entity 8976 and Action ID 195: The output should contain all rows including the first active entry in the status abd should drop all active entries after the first one unless the TaskTemplate Pred column for both the active entries is the same.
For Entity 87172 and Action ID 57: This is flawed data but I need to display all the rows including the smallest tasktemplateID with active status.
For Entity 87185 and Action ID 17: The ourput for this should be all rows because the tasktemplatepred for the last two active tasks is the same.

Output table below:

Row Number	EntityID	PlanID	ActionID	TaskTemplateID	status	TaskTemplatePred
5	8976	196	67	118	Active	115
8	8976	195	26	176	Complete	172
9	8976	195	26	175	Complete	172
10	8976	195	26	175	Complete	175
11	8976	195	26	176	Complete	175
12	8976	195	26	178	Active	176
14	87172	826	57	251	Not Required	259
15	87172	826	57	252	Not Required	259
16	87172	826	57	256	Not Required	251
17	87172	826	57	255	Not Required	252
18	87172	826	57	255	Not Required	251
19	87172	826	57	267	Complete	259
20	87172	826	57	261	Active	258
21	87172	826	57	262	Complete	259
22	87172	826	57	266	Not Required	256
23	87172	826	57	265	Not Required	255
24	87172	826	57	265	Not Required	266
25	87172	826	57	268	Not Required	265
26	87172	826	57	269	Not Required	255
27	87172	826	57	287	Complete	266
28	87172	826	57	281	Complete	287
29	87172	826	57	282	Complete	281
30	87185	1295	17	58	Complete	57
31	87185	1295	17	59	Complete	58
32	87185	1295	17	162	Active	58
33	87185	1295	17	166	Active	58

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Try this. I don't have your complete data set so I don't exactly know what the performance will be but these types of things can be amazingly quick. Don't sweat the incredibly non-SARG-able predicate because you're making a full pass on the table anyway.

WITH cte AS ( SELECT * ,EntityPlanCount = COUNT(*) OVER (PARTITION BY EntityID,PlanID) ,CancelledCount = SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) OVER (PARTITION BY EntityID,PlanID) ,ActiveCount = SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY EntityID,PlanID,Status) ,ActiveTemplateCount = SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY EntityID,PlanID,Status,TaskTemplatePred) ,StatusRowNum = ROW_NUMBER() OVER (PARTITION BY EntityID,PlanID,Status ORDER BY Row) FROM tmp ) SELECT * FROM cte WHERE 1 = CASE WHEN CancelledCount = 0 AND ActiveCount = 0 and ActiveTemplateCount = 0 THEN 1 WHEN EntityPlanCount-ActiveCount = 0 AND ActiveTemplateCount = 1 THEN 1 WHEN EntityPlanCount-ActiveCount = 0 AND ActiveTemplateCount > 1 THEN 0 WHEN EntityPlanCount-ActiveCount > 0 AND ActiveTemplateCount > 1 THEN 1 WHEN EntityPlanCount-ActiveCount > 0 AND Status = 'Active' AND StatusRowNum > 1 THEN 0 WHEN EntityPlanCount-CancelledCount > 0 THEN 1 ELSE 0 END ORDER BY Row ;

--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)

defineamol Valued Member Points: 72 More actions · Answer 8

Thanks so much Jeff Moden. I appreciate the response. You are the best. 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

defineamol - Tuesday, May 15, 2018 7:14 PM
Thanks so much Jeff Moden. I appreciate the response. You are the best. 🙂

Thank your for the feedback. Since you're the one that will need to support the code or repair any shortsightedness on my part, the next question would be... do you understand how and why it works?

--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)

Task Table
Server ID	Action	Task ID
123	43	1
123	43	3
123	43	2
123	43	4
123	43	5
123	45	1
123	45	4
123	45	3
123	45	2
123	45	5
234	43	5
234	43	3
234	43	4
234	43	2
234	43	1


Task Template
TaskID	TaskPredecessor
2	1
3	2
4	2
5	4
6	5

Output
Server ID	Action	Task ID
123	43	1
123	43	3
123	43	2
123	43	4
123	45	1
234	43	3
234	43	4
234	43	2
234	43	1