Modify SQL Question

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; 

Select allOpen in new window

Took the liberty of changing your formatting a bit to make it more readable.

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;     

Select allOpen in new window

Hi Experts,
As of now both versions are not executing, access is giving me an error, will continue next week on this, thanks,
Ben

Hi Experts,
@Koen Van Wielink, Dung Dinh,
Please see attached.
Thanks,
Ben
Koen.png
Dung.png

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.

@Anthony,
Done..

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; 

Select allOpen 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.

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

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.

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)

Ok, what is the count you're getting now? Can you capture your output?

attached.
Koen.png

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
		

Select allOpen in new window

341

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

Select allOpen 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.

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

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 (

Select allOpen in new window

and

) as x
) AS y; 

Select allOpen in new window

To get the same thing.

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!

Haha, that explains. You had me puzzled.
Glad I could help.

Hi Koen,

When you have a chance please take a look at the following link
https://www.experts-exchange.com/questions/28586789/Complicated-SQL-question.html?anchorAnswerId=40518000#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

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.

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

Select allOpen 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?

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.

Ok, got you, have a good night!