July 16, 2013 at 11:49 am
Hello everyone,
My name is Thomas and I am brand new to T-SQL. I was thrown into writing our company reports a little over two months ago. I am completley self-taught so forgive any ignorance I may show. I was throw into this out of necessity,we are a non-profit and cant afford any more IT staff.
However have found that I really like reports!
My question is how do I find a missing value?
We want to know what clients who have had a visit greater than 01/01/2013 are also missing their Client Intake Forms with a form create date also greater than 01/01/2013. This information will be pulled from two tables. The table containing the document information and the table containing the visit information. They are joined on "PatientID".
The client intake form is text value under the "Document Description Column" along with several other Document's such as referals, ROI's ect. I can easily pull the information on who has a Intake Form but struggling on who does not.
Please see my "who does query"
Select Distinct
dbo.DocumentList.id AS 'DoucumentID'
,dbo.DocumentList.patientId AS 'ClientID'
,dbo.DocumentList.createdDT AS 'Document Date'
,docTypeDescription AS 'Document Name'
,visitdate
From dbo.DocumentList
Inner JOIN dbo.dsCHCChargesAndPayments ON
dbo.dsCHCChargesAndPayments.[Patient: Patient ID]=patientId
Where docTypeDescription IN('Client Intake Information Form')
and visitdate > '2013-01-01 00:00:00.000'
and createdDT >'2013-01-01 00:00:00.000'
Thanks any help is appreciated!
***SQL born on date Spring 2013:-)
July 16, 2013 at 1:50 pm
Hi
To assist people in helping you, you will need to post more information on your table structures and give some sample data. This article http://www.sqlservercentral.com/articles/Best+Practices/61537/ describes best practice.
I would say that the most likely way you want to do this is use a NOT EXISTS.
Guessing at the structure of your tables, something like this may do the trick:
SELECT cp.[Patient: Patient ID]
, cp.visitdate
FROM dbo.dsCHCChargesAndPayments AS cp
WHERE visitdate > '2013-01-01 00:00:00.000'
AND NOT EXISTS (
-- Selects any Intake forms for the patient after their visit. If one
-- exists the patient will not be returned
SELECT 1 -- Just a place holder
FROM dbo.DocumentList AS dl
WHERE dl.docTypeDescription IN('Client Intake Information Form')
AND dl.patientId = cp.[Patient: Patient ID] -- Join to patient
AND dl.createdDT >'2013-01-01 00:00:00.000'
)
July 16, 2013 at 1:53 pm
Thanks Micky I'm going to work on that right now:-)
***SQL born on date Spring 2013:-)
July 16, 2013 at 1:54 pm
First, welcome to the SSC forums. This is a great place to get help and for someone looking to grow their SQL skills.
Note the link in my signature line for getting better answers to your SQL questions. Providing us with DDL will help us provide you with the best answers.
That said, I put together some sample data and a query that I think will do what you need (or at least get you much closer).
USE tempdb;
----------------------------------------------------------------------
-- (1) CREATE SAMPLE DATA
----------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.DocumentList') IS NOT NULL
DROP TABLE dbo.DocumentList;
IF OBJECT_ID('tempdb.dbo.dsCHCChargesAndPayments') IS NOT NULL
DROP TABLE dbo.dsCHCChargesAndPayments;
CREATE TABLE dbo.DocumentList
(id int primary key,
patientID int not null,
createdDT date not null,
docTypeDescription varchar(100) not null);
CREATE TABLE dbo.dsCHCChargesAndPayments
(xaction_id int primary key,
patientID int not null,
visitDate date not null)
INSERT INTO dbo.DocumentList
SELECT 1, 100, '2013-01-03', 'Client Intake Information Form' UNION ALL
SELECT 2, 101, '2013-02-24', 'Client Intake Information Form' UNION ALL
SELECT 3, 102, '2013-03-10', 'Client Intake Information Form' UNION ALL
SELECT 4, 103, '2013-05-30', 'Some other form';
INSERT INTO dbo.dsCHCChargesAndPayments
SELECT 1, 100, '2013-06-02' UNION ALL
SELECT 3, 101, '2013-05-02' UNION ALL
SELECT 4, 102, '2013-06-12' UNION ALL
SELECT 5, 103, '2013-06-18';
--SELECT * FROM dbo.DocumentList
--SELECT * FROM dbo.dsCHCChargesAndPayments
----------------------------------------------------------------------
-- (2) THE QUERY
----------------------------------------------------------------------
WITH AllClients AS
(SELECT dl.patientID, ISNULL(ttl,0) AS ClientIntakeFormCount
FROM dbo.DocumentList dl
LEFT JOIN (
SELECT patientID, COUNT(*) AS ttl
FROM dbo.DocumentList
WHERE docTypeDescription='Client Intake Information Form'
GROUP BY patientID) AS xx
ON dl.patientID=xx.patientID)
SELECT --DISTINCT
dl.id AS 'DoucumentID'
,dl.patientId AS 'ClientID'
,dl.createdDT AS 'Document Date'
--,docTypeDescription AS 'Document Name'
,CASE
WHEN ac.ClientIntakeFormCount>=1 THEN 'Yes'
ELSE 'No'
END AS ClientIntakeCompleteed
,visitdate
From dbo.DocumentList AS dl
INNER JOIN AllClients ac
ON dl.patientID=ac.patientID
INNER JOIN dbo.dsCHCChargesAndPayments AS dcp
ON dl.patientID=dcp.patientID
--AND dl.docTypeDescription IN('Client Intake Information Form')
AND visitdate > '2013-01-01 00:00:00.000'
AND createdDT > '2013-01-01 00:00:00.000'
ORDER BY 'Document Date'
Let us know if this is what you are looking for. Cheers!
Edit: MickyT's NOT EXISTS method is the better way to go (I was still typing when he posted his query).
-- Itzik Ben-Gan 2001
July 16, 2013 at 2:36 pm
Micky you are one awsome dude! Worked like a charm:w00t: I had to get this list the old fashion way by hand comparing lists in excel and then do alot of ground work, erggg. Not a fan of excel..lol. Love SQL though.
***SQL born on date Spring 2013:-)
July 16, 2013 at 2:41 pm
You're welcome and I'm glad to be of assistance.
July 16, 2013 at 2:45 pm
thomashohner (7/16/2013)
Micky you are one awsome dude! Worked like a charm:w00t: I had to get this list the old fashion way by hand comparing lists in excel and then do alot of ground work, erggg. Not a fan of excel..lol. Love SQL though.
FYI, depending on how your spreadsheet is setup you can use the Import/Export Wizard (right-click on a database in SSMS, go to Tasks and select Import Data) to import that Excel Data. This may save you time in the future.
More about importing Excel data into SQL Server: http://support.microsoft.com/kb/321686
-- Itzik Ben-Gan 2001
July 16, 2013 at 2:58 pm
You guys are great thanks for all the help! I am one of those weirdo's that even though I have been only writing sql statements for a couple of months I find it extremely fun. Cant wait to learn as much as I can currently reading Joes for Pros series.
Thanks again guys:-)
***SQL born on date Spring 2013:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply