January 17, 2014 at 4:24 pm
I have a query that I'm trying to figure out the best way to de duplicate unique users.
The report needs to de duplicate based on the start of the fiscal year which is 07/01 for us.
With the way I have it currently it works fine de duplicating the date range selected however, It needs to exclude those unique users from the previous quarter.
For example, we now have two quarters under our belt. If I run it for 07/01/13 to 12/31/13 I get 789 unique users.
If I run it from 07/01/13 to 09/30/13 (1st quarter)=532
If I run it from 10/01/13 to 12/31/13 (2nd quarter)=434
But, I want the difference from 789-532 to get just the new unique users in the 2nd quarter
When you run the quarters separately they don't add up to the same number when you run them together and I understand the reason why, because its only de duplicating during the date range run.
What is the easiest method to have the list dE duplicate across both quarters but only show the new additions from the second quarter?
Would I have to do a NOT EXISTS with multiple result sets?
Thanks Thomas
***SQL born on date Spring 2013:-)
January 17, 2014 at 4:30 pm
You could use a NOT EXISTS, but I don't understand the part of multiple results are you'll be getting a single resultset.
It would be nice if you shared DDL, sample data and expected results for the ones (like me) that need some help to picture the problems.
January 17, 2014 at 4:38 pm
Thanks Luis, I'm still new at this on the ddl, do you mean the datatypes? I know I sound ignorant but I'm still new.
Here is my query, the PatientID is unique and numeric
Declare @DOSTo VARCHAR (20)
Declare @DOSFrom VARCHAR (20)
Set @DOSFrom='06/30/13'
Set @DOSTo='12/31/13'
SELECT DISTINCT
v.PatientID
,FromDate
,fctP.LNameFName AS PatientName
,fctP.DateOfBirth AS Birthdate
,DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 AS Age
,CASE WHEN ISNULL(LEFT(fctP.Sex,1),'U') NOT IN ('M','F') THEN 'U' ELSE LEFT(fctP.Sex,1) END AS Sex
,CASE WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 < 4.99 THEN '1: Age 0-4 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 BETWEEN 5.00 AND 17.999 THEN '2: Age 5-17 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 BETWEEN 18.00 AND 24.999 THEN '3: Age 18-24 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 BETWEEN 20.00 AND 24.999 THEN '4: Age 20-24 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 BETWEEN 25.00 AND 54.999 THEN '5: Age 25-54 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 BETWEEN 30.00 AND 34.999 THEN '6: Age 55-64 '
WHEN DATEDIFF(dd,fctP.DateOfBirth,@DOSTo)/365.25 >= 45.00 THEN '9: Age Over 65 '
WHEN fctP.DateOfBirth IS NULL THEN 'No Birthdate'
END AS AgeLabel
,CASE WHEN ISNULL(vp.RequiresTranslationFlag, 0) = 1 THEN 1 ELSE 0 END AS Translation
,ISNULL(vp.Ethnicity2, 'U') AS EthnicityName
,ISNULL(vp.RaceCodeValue, '7') AS RaceCodeValue
,ISNULL(vp.EthnicityCode,'3') AS EthnicityCode
,vp.Language
,ISNULL(r.LocalName,'Unknown') AS RaceName
,ISNULL(r.UDS3bCode,'7') AS RaceCode
FROM
vwUDSVisitInfo v
LEFT JOIN CareProvider cp ON v.RenderingProviderID = cp.CareProviderID
LEFT JOIN Person d ON cp.PersonID = d.PersonID
LEFT JOIN vwUDSPerson vp ON v.PersonID = vp.PatientProfileID
LEFT JOIN fctPerson fctP ON fctP.PatientID = v.PatientID
LEFT JOIN vwUDSRace r ON vp.RaceID = r.RaceId
Full OUTER JOIN CustomReportFieldValues cr ON cr.PatientID=v.PatientID
WHERE
v.FromDate BETWEEN @DOSFrom AND @DOSTo AND
v.VisitType= 'ArtStreet' AND v.ProcedureCode Not IN ('OS006','90853')
***SQL born on date Spring 2013:-)
January 17, 2014 at 4:40 pm
I'm reading your signature right now
***SQL born on date Spring 2013:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply