Modify SQL Question

Hi Experts,
I need to modify the sql below to give me a count break down by Patients3.PatientType
SELECT Count(1) AS Expr1
FROM (select distinct PatientsID,  iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 from (
       SELECT P1.PatientsID, P1.EffectiveFrom, P1.End, (select min(EffectiveFrom) from TmpPatBillingCodes as P2 where P2.PatientsID=P1.PatientsID and P2.EffectiveFrom > P1.End) AS next_service
FROM TmpPatBillingCodes AS P1 INNER JOIN Patients3 ON P1.PatientsID = Patients3.ID
WHERE (((Patients3.FacilityID)<>6488) AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))

) as x
) AS y; 

Open in new window

Can someone help me with this?
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Bit hard to be sure without sample data and such a nested query structure, but I'm pretty sure this would give you what you need:

SELECT		Count(PatientsID) AS Counts
		,	PatientsID
FROM	(select	distinct 
				PatientsID
				PatientType
			,	iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 
		from	(
				SELECT		P1.PatientsID
						,	P3.PatientType
						,	P1.EffectiveFrom
						,	P1.End
						,	(select min(EffectiveFrom) 
							from	TmpPatBillingCodes as P2 
							where	P2.PatientsID=P1.PatientsID 
							and		P2.EffectiveFrom > P1.End) AS next_service
				FROM	TmpPatBillingCodes AS P1 
						INNER JOIN Patients3 P3
							ON P1.PatientsID = P3.ID
				WHERE	(((Patients3.FacilityID)<>6488) 
				AND		((P1.EffectiveFrom)<#1/1/2014#) 
				AND		((P1.End)>=#1/1/2013#))

				) as x
		) AS y
group by PatientsID; 

Open in new window


Took the liberty of changing your formatting a bit to make it more readable.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
Try with this
SELECT PatientType,Count(1) AS Expr1
FROM ( select distinct PatientsID,  iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30, PatientType
       from (
             SELECT P1.PatientsID, P1.EffectiveFrom, P1.End, 
                 (select min(EffectiveFrom) 
                    from TmpPatBillingCodes as P2 
                    where P2.PatientsID=P1.PatientsID 
                          and P2.EffectiveFrom > P1.End) AS next_service,
                 P3.PatientType         
             FROM TmpPatBillingCodes AS P1 
                 INNER JOIN Patients3 P3 ON P1.PatientsID = P3.ID
             WHERE (((Patients3.FacilityID)<>6488) AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))
            ) as x
     ) AS y; 
GROUP BY PatientType;     

Open in new window

0
bfuchsAuthor Commented:
Hi Experts,
As of now both versions are not executing, access is giving me an error, will continue next week on this, thanks,
Ben
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bfuchsAuthor Commented:
Hi Experts,
@Koen Van Wielink, Dung Dinh,
Please see attached.
Thanks,
Ben
Koen.png
Dung.png
0
Anthony PerkinsCommented:
Since this question is intended for MS Access you may want to request that the MS SQL Server topic area be dropped.  That is unless you want T-SQL code.
0
bfuchsAuthor Commented:
@Anthony,
Done..
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Missed a comma.
Try this:

SELECT		Count(PatientsID) AS Counts
		,	PatientsID
FROM	(select	distinct 
				PatientsID
			,	PatientType
			,	iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 
		from	(
				SELECT		P1.PatientsID
						,	P3.PatientType
						,	P1.EffectiveFrom
						,	P1.End
						,	(select min(EffectiveFrom) 
							from	TmpPatBillingCodes as P2 
							where	P2.PatientsID=P1.PatientsID 
							and		P2.EffectiveFrom > P1.End) AS next_service
				FROM	TmpPatBillingCodes AS P1 
						INNER JOIN Patients3 P3
							ON P1.PatientsID = P3.ID
				WHERE	(((Patients3.FacilityID)<>6488) 
				AND		((P1.EffectiveFrom)<#1/1/2014#) 
				AND		((P1.End)>=#1/1/2013#))

				) as x
		) AS y
group by PatientsID; 

Open in new window


Also make sure you get a space between select and distinct in the first nested select statement. I see in your screenshot the words are concatenated.
0
bfuchsAuthor Commented:
Hi Koen,

I changed the Patients3.FacilityID = p3.facilityid in order to get rid of the error message, however now whats giving me is looks like a count per PatientsID (see attached), while I really need it break down by Patients3.PatientType, Please let me know how to correct it,

Thanks,
Ben
Koen.png
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Sorry Ben, sloppy reading from my end.
Replace the PatientsID in the select statement at the top with PatientType and Group By PatientType at the bottom:

SELECT		Count(PatientsID) AS Counts
		,	PatientType
FROM	(select	distinct 
				PatientsID
			,	PatientType
			,	iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 
		from	(
				SELECT		P1.PatientsID
						,	P3.PatientType
						,	P1.EffectiveFrom
						,	P1.End
						,	(select min(EffectiveFrom) 
							from	TmpPatBillingCodes as P2 
							where	P2.PatientsID=P1.PatientsID 
							and		P2.EffectiveFrom > P1.End) AS next_service
				FROM	TmpPatBillingCodes AS P1 
						INNER JOIN Patients3 P3
							ON P1.PatientsID = P3.ID
				WHERE	(((Patients3.FacilityID)<>6488) 
				AND		((P1.EffectiveFrom)<#1/1/2014#) 
				AND		((P1.End)>=#1/1/2013#))

				) as x
		) AS y
group by PatientType; 

Open in new window


Please just apply the changes I described to your latest code, the error message you got earlier would still be generated by this one (not quite clear what you changed to get rid of it).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
OK, we are getting closer...
I am getting expected format results, however the count is not correct, it looks like it first groups by patientID then the total is getting grouped by PatientType, What I need is to get the same count as the original (In my case about 1300 records) just divided per PatientType.
0
bfuchsAuthor Commented:
BTW, The change what I made to yours is for the following
Instead of  WHERE      (((Patients3.FacilityID)<>6488)
I changed to WHERE      (((P3.FacilityID)<>6488)
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, what is the count you're getting now? Can you capture your output?
0
bfuchsAuthor Commented:
attached.
Koen.png
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
If you just run this nested part, how many records do you get:

select	distinct 
				PatientsID
			,	PatientType
			,	iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 
		from	(
				SELECT		P1.PatientsID
						,	P3.PatientType
						,	P1.EffectiveFrom
						,	P1.End
						,	(select min(EffectiveFrom) 
							from	TmpPatBillingCodes as P2 
							where	P2.PatientsID=P1.PatientsID 
							and		P2.EffectiveFrom > P1.End) AS next_service
				FROM	TmpPatBillingCodes AS P1 
						INNER JOIN Patients3 P3
							ON P1.PatientsID = P3.ID
				WHERE	(((P3.FacilityID)<>6488) 
				AND		((P1.EffectiveFrom)<#1/1/2014#) 
				AND		((P1.End)>=#1/1/2013#))

				) as x
		

Open in new window

0
bfuchsAuthor Commented:
341
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, so that matches the total of the grouped results. Means the issue is not with the way we group, but with the result set returned by the nested query.
Just to make sure, can you run this as well and check the total number of records returned:

select	distinct 
			,	PatientsID
			,	iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 
	from	(SELECT		P1.PatientsID
					,	P1.EffectiveFrom
					,	P1.End
					,	(select	min(EffectiveFrom) 
						from	TmpPatBillingCodes as P2 
						where	P2.PatientsID=P1.PatientsID 
						and		P2.EffectiveFrom > P1.End) AS next_service
			FROM	TmpPatBillingCodes AS P1 
					INNER JOIN Patients3 P3
						ON P1.PatientsID = P3.ID
WHERE	(((Patients3.FacilityID)<>6488) 
AND		((P1.EffectiveFrom)<#1/1/2014#) 
AND		((P1.End)>=#1/1/2013#))

) as x

Open in new window


This is your original nested query before we made any changes. I'm struggling to see why the additions we made would reduce the number of records. I'd expect an increase if anything.
0
bfuchsAuthor Commented:
Hi Koan,

I am having trouble pasting the contents of your sql above in access, even when I try manually to modify it doesn't work, it keeps throwing funny characters, see attached.

Thanks,
Ben
Koen.png
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
It seems to be removing some spaces.
It's basically the original query you posted in your question, but without the final select statement. I just want to check how many records were returned by the original to help pinpoint if/why the modification has changed the number of records.
From your original query, remove:

SELECT Count(1) AS Expr1
FROM (

Open in new window


and

) as x
) AS y; 

Open in new window

To get the same thing.
0
bfuchsAuthor Commented:
Sorry Koen, It was my mistake, I realized that was looking at the wrong database, the actual count of this matches up with what returns from my original query.
Thank you very very much!
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Haha, that explains. You had me puzzled.
Glad I could help.
0
bfuchsAuthor Commented:
Hi Koen,

When you have a chance please take a look at the following link
http://www.experts-exchange.com/Database/MS_Access/Q_28586789.html#a40518000
first you will see where did this original query came from
And perhaps you can help me with the issue I'm having there...

Thanks,
Ben
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Had a quick look at it Ben, and it's because there's no mention in your original question of checking this annually. Entries 5546 and 6509 have a gap bigger than 30 days, as such the count becomes 2.
Unfortunately my knowledge of MS Access is limited and I'm not familiar with the syntax used by the person who wrote the original solution.
I suggest you open a new question rather than post on the original one since you already accepted that solution. This is a new development which I think deserves its own question. It also allows the experts to earn points again.
0
bfuchsAuthor Commented:
@Koen,
1- I did specify in the original question there that I am looking for specific date range, and this what was done to limit for 2013 records  
AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))

Open in new window

according to this #6509 should not be included.
2- about posting new question, just wondering what is the rule, after an answer is accepted and later I realize that it's incorrect, is there a way to reassign the points to the expert who comes up with correct answer?
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Reassigning the points can only be done by admins, and besides being a lot of work would also not be fair to the expert who's solution was originally accepted.
Opening a new question is never an issue, and is you include a reference to your original question everyone will be happy.
0
bfuchsAuthor Commented:
Ok, got you, have a good night!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.