SQL query to only return a row based on fluid conditions

Question

SQL query to only return a row based on fluid conditions

matter2003

SSC Veteran

Points: 254
More actions
January 3, 2019 at 12:00 pm

#416599
I know this is possible but am unsure how to set this up. Basically I am needing to pull data for each employee but only if it meets certain criteria based on a few different dates.
For instance, if the employee was assigned to a company before 6/1 they get counted automatically.
If the employee was assigned to a company after 6/1 they only get counted IF they have a review with that company after the date they were assigned(ie, they are assigned on 6/25 and have a review on 7/1...this should be counted. If for instance they were assigmed on 6/25 and the review happened on 6/15 they would not count for this employee)
If the employee gets removed from a company before 4/1 they dont get counted. If they are removed on or after 4/1 it counts.
So the key columns are Created Date of the review, Start Date and End Date from the employee-customer table.
I believe this would need to either be some type of subquery which returns the start date for the employee with that customer and then compares the review date based on a Case statement evaluating this date versus the review date but I am unsure exactly how to do this.
Any help would be appreciated.
EDIT: Table Structure/Data below:
Employee-Customer Table
ID EmpID CustID StartDate EndDate 1 4 10 10/1/2017 2/21/2018 2 4 11 10/1/2017 7/31/2018 3 4 15 10/1/2017 4/8/2018 4 4 17 6/1/2018 NULL (means still active with this employee) 5 4 19 5/18/2018 NULL
Customer Data Table
ID CustID ActivityDate Task 1 10 1/13/2018 Review 3 15 4/2/2018 Review 4 17 6/25/2018 Review 5 17 6/13/2018 Client Engagement 6 17 6/29/2018 Client Engagement 7 19 5/25/2018 Client Engagement 8 19 6/28/2018 Review
- 10: This customer does NOT get returned because the customer was removed from the employee prior to the 4/1 cutoff date.
- 11: This customer DOES get returned because the employee has had the customer past the 5/31 cutoff date, even though there is no review for the customer
- 15: This customer DOES get returned because the employee had the customer past the 4/1 cutoff date before it was removed from them.
- 17: The client Engagement from 6/29/2018 DOES get returned but the client engagement from 6/13/2018 does NOT get returned because it happened BEFORE the review was done with this client(effectively when an employee Start Date for a customer is PAST 5/31 the activity only counts AFTER they have had a review with that customer---all activity that takes place prior to this review date gets ignored)
- 19: The Client Engagement DOES get returned in this case because the employee was assigned to them before 6/1, so any activity counts regardless of if a review was done prior to the other item happening.Hopefully this explanation and breakdown makes sense.
So for the this example, I want a query that brings back the following customer ID's with data based on the criteria:Hopefully this explanation and breakdown makes sense.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

You'd likely get help more quickly if you posted your data in a readily consumable format. Lot's of people like to test their solutions before posting an answer. See the first link under "Helpful Links" in my signature line below for one way to do that.

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

matter2003 SSC Veteran Points: 254 More actions · Answer 2

Jeff Moden - Thursday, January 3, 2019 8:25 PM
You'd likely get help more quickly if you posted your data in a readily consumable format. Lot's of people like to test their solutions before posting an answer. See the first link under "Helpful Links" in my signature line below for one way to do that.

Table scripts/Inserts

CREATE TABLE Cust_Employee(

Cust_Emp_ID int IDENTITY(1,1) NOT NULL,

Cust_ID int NOT NULL,

User_ID int NULL,

Start_Date datetime NULL,

End_Date datetime NULL,

CONSTRAINT PK_Cust_Employee PRIMARY KEY CLUSTERED

(

Cust_Emp_ID ASC

)WITH (PAD INEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

)ON PRIMARY

GO

CREATE TABLE Cust_Data(

Cust_Data_ID int IDENTITY(1,1) NOT NULL,

Cust_ID int NULL,

Activity_Date datetime NULL,

Task VARCHAR(50) NULL

)

CONSTRAINT PK_Client_Data PRIMARY KEY CLUSTERED

(

Cust_Data_ID ASC

)WITH (PAD INEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

)ON PRIMARY

GO

INSERT INTO Cust_Employee VALUES(4, 10, '10/1/2017', '2/21/2018')

INSERT INTO Cust_Employee VALUES(4, 11, '10/1/2017', '7/31/2018')

INSERT INTO Cust_Employee VALUES(4, 15, '10/1/2017', '4/8/2018')

INSERT INTO Cust_Employee VALUES(4, 17, '6/1/2018', NULL)

INSERT INTO Cust_Employee VALUES(4, 19, '5/18/2018', NULL)

INSERT INTO Cust _Data VALUES(10, '1/13/2018', 'Review')

INSERT INTO Cust _Data VALUES(15, '4/2/2018', 'Review')

INSERT INTO Cust _Data VALUES(17, '6/25/2018', 'Review')

INSERT INTO Cust _Data VALUES(17, '6/13/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(17, '6/29/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(19, '5/25/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(10, '6/28/2018', 'Review')

matter2003 SSC Veteran Points: 254 More actions · Answer 3

I'm using SQL Server 2017.

Expected Results:

Emp_ID	Cust_ID	Start_Date	End_Date	Activity_Date	Task
4	11	10/1/2017	7/31/2018	NULL	NULL
4	15	10/1/2017	4/8/2018	4/2/2018	Review
4	17	6/1/2018	NULL	6/25/2018	Review
4	17	6/1/2018	NULL	6/29/2018	Client Engagement
4	19	5/18/2018	NULL	6/28/2018	Review

sgmunson SSC Guru Points: 110640 More actions · Answer 4

matter2003 - Thursday, January 3, 2019 12:00 PM
I know this is possible but am unsure how to set this up. Basically I am needing to pull data for each employee but only if it meets certain criteria based on a few different dates.
For instance, if the employee was assigned to a company before 6/1 they get counted automatically.
If the employee was assigned to a company after 6/1 they only get counted IF they have a review with that company after the date they were assigned(ie, they are assigned on 6/25 and have a review on 7/1...this should be counted. If for instance they were assigmed on 6/25 and the review happened on 6/15 they would not count for this employee)
If the employee gets removed from a company before 4/1 they dont get counted. If they are removed on or after 4/1 it counts.
So the key columns are Created Date of the review, Start Date and End Date from the employee-customer table.
I believe this would need to either be some type of subquery which returns the start date for the employee with that customer and then compares the review date based on a Case statement evaluating this date versus the review date but I am unsure exactly how to do this.
Any help would be appreciated.
EDIT: Table Structure/Data below:
Employee-Customer Table
ID EmpID CustID StartDate EndDate 1 4 10 10/1/2017 2/21/2018 2 4 11 10/1/2017 7/31/2018 3 4 15 10/1/2017 4/8/2018 4 4 17 6/1/2018 NULL (means still active with this employee) 5 4 19 5/18/2018 NULL
Customer Data Table
ID CustID ActivityDate Task 1 10 1/13/2018 Review 3 15 4/2/2018 Review 4 17 6/25/2018 Review 5 17 6/13/2018 Client Engagement 6 17 6/29/2018 Client Engagement 7 19 5/25/2018 Client Engagement 8 19 6/28/2018 Review
10: This customer does NOT get returned because the customer was removed from the employee prior to the 4/1 cutoff date.
11: This customer DOES get returned because the employee has had the customer past the 5/31 cutoff date, even though there is no review for the customer
15: This customer DOES get returned because the employee had the customer past the 4/1 cutoff date before it was removed from them.
17: The client Engagement from 6/29/2018 DOES get returned but the client engagement from 6/13/2018 does NOT get returned because it happened BEFORE the review was done with this client(effectively when an employee Start Date for a customer is PAST 5/31 the activity only counts AFTER they have had a review with that customer---all activity that takes place prior to this review date gets ignored)
19: The Client Engagement DOES get returned in this case because the employee was assigned to them before 6/1, so any activity counts regardless of if a review was done prior to the other item happening.Hopefully this explanation and breakdown makes sense.
So for the this example, I want a query that brings back the following customer ID's with data based on the criteria:Hopefully this explanation and breakdown makes sense.

As it turns out, it's your use of multiple different dates with the word Cutoff being applied to it that doesn't make sense. Where would the query figure out if a given row has a cutoff date that needs to apply to it in some way, as well as what that cutoff date would be?

Also, you bring back customer 19 solely because they got assigned before 6/1, and I can't see any obvious way to know why I would use 6/1 as that date to measure against, or whether or not it would apply to all customers. I'm pretty sure you've been waiting 3 weeks for an answer that may never come until a much more thorough explanation of the rules governing this is supplied.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)