May 22, 2018 at 2:39 pm
I'm very new to TSQL but my job has expanded to include building some SSRS reports for our CRM environment. I feel like I have a pretty simple query to build here but can't figure out how to do it, and I don't know what to really search for either.
I have three key tables in this scenario. First is simply a list of contacts. Second is journal entries pertaining to services rendered for a person - these entries have a specific date. The third table has authorizations for the person's services that have date *ranges* on them. Each journal entry needs to fit into an authorization's date range for a billing process.
I'm trying to find the journal entries whose dates don't fit into any of the authorizations for that person.
For example, let's say I have two entries for John - one on 3/15/2018 and another for 5/15/2018. Next, John has 3 authorizations with these date ranges:
Auth. A) 11/1/2017-12/31/2017
Auth. B) 1/1/2018-2/28/2018
Auth. C) 3/1/2018-4/30/2018
I'd want the 3/15 entry to be hidden since it fits into Authorization C, but the 5/15 entry should be in the results since it fits into none of the authorizations. The dates in the authorizations are separate fields (start date and end date).
Anyone got some input on how I'd address this? Let me know if more information is needed. Thanks in advance!
May 22, 2018 at 2:52 pm
Please post DDL (create table) for each table and inserts with sample data so those helping don't have to type this for you and answer your question.
May 22, 2018 at 3:10 pm
SQL Newbie - try this link from StackOverflow: https://stackoverflow.com/questions/40361594/tsql-order-by-on-occasion-doesnt-order-correctly
May 23, 2018 at 9:38 am
Thanks, Joe - I'll take a crack at it. I don't know very much tsql for creating/editing tables, so I'm only somewhat confident this is all correct. Let me know if this is what you were looking for.CREATE TABLE contacts (
contact_id INT PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50));
INSERT INTO contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'), (2, 'Burgess', 'Lamar'), (3, 'Witwer', 'Danny');
CREATE TABLE authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
start_date DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES contacts (contact_id));
INSERT INTO authorizations (auth_id, start_date, end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1), (2,'2018-01-01', '2018-02-28',1),(3,'2018-03-01', '2018-04-30',1);
CREATE TABLE journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id));
INSERT INTO journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),(2,'2018-05-15',1),(3,'2018-03-15',2);
As you can see, there's no direct relationship between the journal_entries and authorizations tables. They're only connected through the contact. My goal is to have the journal_entries that don't fall into any of the authorization date ranges for that same person will be what appear.
In this case, only one of the journal_entries falls into an auth's date range - the 3/15 for John, as it fits into the date range of auth_id 3. The 3/15 for Lamar should show up since Lamar doesn't have any authorizations, and the 5/15 for John should show up since it doesn't fit into any of his:
| first_name | last_name | entry_id | entry_date |
|---|---|---|---|
| John | Anderton | 2 | 2018-05-15 |
| Lamar | Burgess | 3 | 2018-03-15 |
May 25, 2018 at 8:26 am
Try this on for size... although with temp tables it won't enforce the foreign keys, it at least deomstrates the concept, and I could actually run it... CREATE TABLE #contacts (
contact_id INT PRIMARY KEY CLUSTERED,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50)
);
INSERT INTO #contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'),
(2, 'Burgess', 'Lamar'),
(3, 'Witwer', 'Danny');
CREATE TABLE #authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
[start_date] DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES #contacts (contact_id)
);
INSERT INTO #authorizations (auth_id, [start_date], end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1),
(2, '2018-01-01', '2018-02-28',1),
(3, '2018-03-01', '2018-04-30',1);
CREATE TABLE #journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id)
);
INSERT INTO #journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),
(2,'2018-05-15',1),
(3,'2018-03-15',2);
SELECT C.first_name, C.last_name, J.*
FROM #journal_entries AS J
INNER JOIN #contacts AS C
ON J.entry_contact_id = C.contact_id
LEFT OUTER JOIN #authorizations AS A
ON J.entry_contact_id = A.auth_contact_id
AND J.entry_date BETWEEN A.[start_date] AND A.end_date
WHERE A.auth_id IS NULL;
DROP TABLE #journal_entries;
DROP TABLE #authorizations;
DROP TABLE #contacts;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 29, 2018 at 12:43 pm
sgmunson - Friday, May 25, 2018 8:26 AMTry this on for size... although with temp tables it won't enforce the foreign keys, it at least deomstrates the concept, and I could actually run it...CREATE TABLE #contacts (
contact_id INT PRIMARY KEY CLUSTERED,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50)
);
INSERT INTO #contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'),
(2, 'Burgess', 'Lamar'),
(3, 'Witwer', 'Danny');CREATE TABLE #authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
[start_date] DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES #contacts (contact_id)
);
INSERT INTO #authorizations (auth_id, [start_date], end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1),
(2, '2018-01-01', '2018-02-28',1),
(3, '2018-03-01', '2018-04-30',1);CREATE TABLE #journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id)
);
INSERT INTO #journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),
(2,'2018-05-15',1),
(3,'2018-03-15',2);SELECT C.first_name, C.last_name, J.*
FROM #journal_entries AS J
INNER JOIN #contacts AS C
ON J.entry_contact_id = C.contact_id
LEFT OUTER JOIN #authorizations AS A
ON J.entry_contact_id = A.auth_contact_id
AND J.entry_date BETWEEN A.[start_date] AND A.end_date
WHERE A.auth_id IS NULL;DROP TABLE #journal_entries;
DROP TABLE #authorizations;
DROP TABLE #contacts;
sgmunson - Indeed, your statement about "at least it demonstrates the concept" is understood. I'm running this in an existing DB so there's no need to create or drop tables in my live data, so the integrity of the keys was less of a concern. In any case, the way you set up the left outer join with multiple conditions combined with the where clause is exactly what I needed - I just didn't know how to structure it. Thanks so much!
May 29, 2018 at 4:12 pm
adamwgrise - Tuesday, May 29, 2018 12:43 PMsgmunson - Friday, May 25, 2018 8:26 AMTry this on for size... although with temp tables it won't enforce the foreign keys, it at least deomstrates the concept, and I could actually run it...CREATE TABLE #contacts (
contact_id INT PRIMARY KEY CLUSTERED,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50)
);
INSERT INTO #contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'),
(2, 'Burgess', 'Lamar'),
(3, 'Witwer', 'Danny');CREATE TABLE #authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
[start_date] DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES #contacts (contact_id)
);
INSERT INTO #authorizations (auth_id, [start_date], end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1),
(2, '2018-01-01', '2018-02-28',1),
(3, '2018-03-01', '2018-04-30',1);CREATE TABLE #journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id)
);
INSERT INTO #journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),
(2,'2018-05-15',1),
(3,'2018-03-15',2);SELECT C.first_name, C.last_name, J.*
FROM #journal_entries AS J
INNER JOIN #contacts AS C
ON J.entry_contact_id = C.contact_id
LEFT OUTER JOIN #authorizations AS A
ON J.entry_contact_id = A.auth_contact_id
AND J.entry_date BETWEEN A.[start_date] AND A.end_date
WHERE A.auth_id IS NULL;DROP TABLE #journal_entries;
DROP TABLE #authorizations;
DROP TABLE #contacts;sgmunson - Indeed, your statement about "at least it demonstrates the concept" is understood. I'm running this in an existing DB so there's no need to create or drop tables in my live data, so the integrity of the keys was less of a concern. In any case, the way you set up the left outer join with multiple conditions combined with the where clause is exactly what I needed - I just didn't know how to structure it. Thanks so much!
The purpose of Steve creating the table was so that others could test the code. It's not actually a part of the solution that he's recommending to you.
Please see the article at the first link under "Helpful Links" in my signature line below to understand more.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2018 at 8:06 pm
Jeff Moden - Tuesday, May 29, 2018 4:12 PMadamwgrise - Tuesday, May 29, 2018 12:43 PMsgmunson - Friday, May 25, 2018 8:26 AMTry this on for size... although with temp tables it won't enforce the foreign keys, it at least deomstrates the concept, and I could actually run it...CREATE TABLE #contacts (
contact_id INT PRIMARY KEY CLUSTERED,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50)
);
INSERT INTO #contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'),
(2, 'Burgess', 'Lamar'),
(3, 'Witwer', 'Danny');CREATE TABLE #authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
[start_date] DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES #contacts (contact_id)
);
INSERT INTO #authorizations (auth_id, [start_date], end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1),
(2, '2018-01-01', '2018-02-28',1),
(3, '2018-03-01', '2018-04-30',1);CREATE TABLE #journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id)
);
INSERT INTO #journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),
(2,'2018-05-15',1),
(3,'2018-03-15',2);SELECT C.first_name, C.last_name, J.*
FROM #journal_entries AS J
INNER JOIN #contacts AS C
ON J.entry_contact_id = C.contact_id
LEFT OUTER JOIN #authorizations AS A
ON J.entry_contact_id = A.auth_contact_id
AND J.entry_date BETWEEN A.[start_date] AND A.end_date
WHERE A.auth_id IS NULL;DROP TABLE #journal_entries;
DROP TABLE #authorizations;
DROP TABLE #contacts;sgmunson - Indeed, your statement about "at least it demonstrates the concept" is understood. I'm running this in an existing DB so there's no need to create or drop tables in my live data, so the integrity of the keys was less of a concern. In any case, the way you set up the left outer join with multiple conditions combined with the where clause is exactly what I needed - I just didn't know how to structure it. Thanks so much!
The purpose of Steve creating the table was so that others could test the code. It's not actually a part of the solution that he's recommending to you.
Please see the article at the first link under "Helpful Links" in my signature line below to understand more.
Understood, Jeff. Thanks for the reference to the helpful information!
May 30, 2018 at 10:42 am
adamwgrise - Tuesday, May 29, 2018 12:43 PMsgmunson - Friday, May 25, 2018 8:26 AMTry this on for size... although with temp tables it won't enforce the foreign keys, it at least deomstrates the concept, and I could actually run it...CREATE TABLE #contacts (
contact_id INT PRIMARY KEY CLUSTERED,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR (50)
);
INSERT INTO #contacts (contact_id, last_name, first_name)
VALUES (1, 'Anderton', 'John'),
(2, 'Burgess', 'Lamar'),
(3, 'Witwer', 'Danny');CREATE TABLE #authorizations (
auth_id INT PRIMARY KEY,
auth_contact_id INT NOT NULL,
[start_date] DATE,
end_date DATE,
CONSTRAINT fk_auth_contact FOREIGN KEY (auth_contact_id) REFERENCES #contacts (contact_id)
);
INSERT INTO #authorizations (auth_id, [start_date], end_date, auth_contact_id)
VALUES (1, '2017-11-01', '2017-12-31',1),
(2, '2018-01-01', '2018-02-28',1),
(3, '2018-03-01', '2018-04-30',1);CREATE TABLE #journal_entries (
entry_id INT PRIMARY KEY,
entry_contact_id INT NOT NULL,
entry_date DATE,
CONSTRAINT fk_entry_contact FOREIGN KEY (entry_contact_id) REFERENCES contacts (contact_id)
);
INSERT INTO #journal_entries (entry_id,entry_date,entry_contact_id)
VALUES (1,'2018-03-15',1),
(2,'2018-05-15',1),
(3,'2018-03-15',2);SELECT C.first_name, C.last_name, J.*
FROM #journal_entries AS J
INNER JOIN #contacts AS C
ON J.entry_contact_id = C.contact_id
LEFT OUTER JOIN #authorizations AS A
ON J.entry_contact_id = A.auth_contact_id
AND J.entry_date BETWEEN A.[start_date] AND A.end_date
WHERE A.auth_id IS NULL;DROP TABLE #journal_entries;
DROP TABLE #authorizations;
DROP TABLE #contacts;sgmunson - Indeed, your statement about "at least it demonstrates the concept" is understood. I'm running this in an existing DB so there's no need to create or drop tables in my live data, so the integrity of the keys was less of a concern. In any case, the way you set up the left outer join with multiple conditions combined with the where clause is exactly what I needed - I just didn't know how to structure it. Thanks so much!
You're very welcome.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply