Solved

Modify SQL Question

Posted on 2014-12-25
25
123 Views
Last Modified: 2014-12-29
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?
0
Comment
Question by:bfuchs
  • 13
  • 10
  • +1
25 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40518127
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
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 40518128
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40518135
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40520776
Hi Experts,
@Koen Van Wielink, Dung Dinh,
Please see attached.
Thanks,
Ben
Koen.png
Dung.png
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40520985
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40521029
@Anthony,
Done..
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40521193
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40521241
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
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40521244
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40521248
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40521249
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40521250
Ok, what is the count you're getting now? Can you capture your output?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:bfuchs
ID: 40521252
attached.
Koen.png
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40521257
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40521291
341
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40521331
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40522345
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40522967
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
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40523081
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40523082
Haha, that explains. You had me puzzled.
Glad I could help.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40523102
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40523116
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40523145
@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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40523149
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
 
LVL 3

Author Comment

by:bfuchs
ID: 40523158
Ok, got you, have a good night!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now