SQL detail records with common table expression

I have three common table expressions that build the data for the results of the report.  The data is summed and the client wants to see the details.  In order to show the details I have to remove the PatientID parameter.  That is where the problem comes in.  

The code
            INNER JOIN [svcCount_cte] sc ON sc.svcCount = p.PatientID  
            INNER JOIN [staffCount_cte] ac ON ac.staffCount = p.PatientID

  apparently needs the PatientID to link the data to do the calculation.  My question is to look the the two sets of code and advise if there is a way to display the details of the data and not the summed by group as in the original.


No detail
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.

Bill PrewIT / Software Engineering ConsultantCommented:
What do you want the output to be?

JohnMac328Author Commented:
The screen shots show data sumed and no data (just zeros) - I want the detail which is coming from the patientiD which I had to remove to get the CTE to run.  Where there are just zeros I want detail data.
You can't get detail our of a totals query.  It's like asking to get back individual potato chunks out of mashed potatoes.

Before I comment further, I need to know whether you are looking for an Access solution or a SQL Server solution.  Including both Access and SQL in the topics causes confusion when the question is about SQL unless you specify whether you are looking for an Access SQL/VBA solution or T-SQL/Stored Procedure solution.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

JohnMac328Author Commented:
This is T-SQL/Stored procedure, not many options when I put in SQL

And yeah thats what I thought but wanted to check here in case I was missing something
The obvious solution is to create either a view that returns details and then a SP to aggregate it or two sp's.  One to return details and one to return totals.
JohnMac328Author Commented:
two sp's.  One to return details and one to return totals.

Could you give an example of that based on the code that I submitted.
No.  I don't write stored procedures normally so although I could do this for myself if I had to, I can't spend that much time doing it for free.  If you are using Access, you should be using bound forms and querydefs.  From my perspective, this is completely unnecessary since a querydef would easily solve the problem.  About 80% of my Access apps use SQL Server, Oracle, or DB2.  The rest use ACE.  I've been building Access apps since version 2.0 in the early 90's.  I can probably count on one hand the number of stored procedures I've had to create in those hundreds of applications.  If you are not fluent in T-SQL, you should probably stick to Access SQL and the QBE to help you build queries.
JohnMac328Author Commented:
Ok, this is SQL server 2008.  I will see if any other SQL people have an idea.
Hamed NasrRetired IT ProfessionalCommented:
If issue persists, and to save time, try to upload the script to recreate the issue in SQL server.
Include the script for the queries and list the expected result.
JohnMac328Author Commented:
That information is in the original question.
Hamed NasrRetired IT ProfessionalCommented:
The information asked for, is the one that creates the objects required to run the CTEs in the original question.
Mark WillsTopic AdvisorCommented:
Hi JohnMac328,

Can you describe the two code blocks you attached ? I can see that one has group by and the other doesnt. How do they relate to the screenshots ? Which one is the problem ? (assuming the detail one) or is there yet another one to be built ?

And when you say "detail", is that filling in where it shows NULL or ZERO, or, a more detailed version of the report showing different information again ?

Just trying to get a handle on what help we can provide :)
JohnMac328Author Commented:
The original is the one with summed data showing in the screen shot.  That one works fine but they want to see the detail.  In order to get the detail to show I had to remove the patient id parameter in the org stored procedure.  That is where the problem started because further down in the other stored procedure it needs the patient id to pull the data.  The second screen shot shows just zeros because it needs the patient it.  

Here is the original
WITH svcCount_cte (PatientID,SvcCount)

INNER JOIN [svcCount_cte] sc ON sc.PatientID = p.PatientID  
INNER JOIN [staffCount_cte] ac ON ac.PatientID = p.PatientID

and here is the second version where it does not see the paitentid - this one has the paitentid parameter removed

WITH svcCount_cte (svcCount)

      INNER JOIN [svcCount_cte] sc ON sc.svcCount = p.PatientID  
      INNER JOIN [staffCount_cte] ac ON ac.staffCount = p.PatientID

I'm just trying to help out since the original DBA is gone.
Mark WillsTopic AdvisorCommented:
Sorry, still missing something from the narrative....

Yes, I can see the zeros in minutes and cases...
Yes, I can see the removal of patientID in the "EE_need_Detail.txt" script

What I dont understand is "They need to see detail" and how " In order to get the detail to show I had to remove the patient id " was going to achieve that.

In other words, "What is Detail" ?

I cannot see how removing "group by" PatientID is going to help your cause at all. Maybe the erroneous "group by" is the last line ?

Could it be the case that they wanted the grouping gone and lead you down the wrong path - wouldnt be the first time a user has failed to explain themselves properly - and it wont be the last either....

If you were to change the very last select (in the EE_GroupDatatxt.txt script) to:
-- Return results to application
SELECT a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient, ISNULL(MinutesRaw,0) AS [Minutes], FLOOR(ISNULL(CasesRaw,0) + 0.5) AS [Cases]
FROM svcList_cte a
LEFT OUTER JOIN anesMinutesRaw_cte b ON b.[Service] = a.[Service] AND b.MEPERSCode = a.MEPERSCode 
ORDER BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;

Open in new window

But would imagine there is some other column missing to help show what the different rows are. Maybe date of service ?

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
JohnMac328Author Commented:
That seemed to give me enough data, thanks!
Mark WillsTopic AdvisorCommented:
That's both a relief and my pleasure in being able to help you. Hope your users agree :)
JohnMac328Author Commented:
I thought I had tried that and it errored out before but i must have had something else blowing it up - thanks again.
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
Query Syntax

From novice to tech pro — start learning today.