Any help is sincerely appreciated:
I have data in a table that represents the following:
Admin Visit Type Registration Date Discharge Date
D 20050301 20050301
D 20050301 20050301
W 20050301 20050301
E 20050301 20050301
D 20050301 20050302
W 20050301 20050303
W 20050301 20050311
D 20050301 20050301
Patient Type is always and I for the records I want but there are also Patient Types = O which I don't care about..
What I would like to do is accoumlate a counter on the number of Registrations per date as well as Discharges per date.
There can be thousands of registrations per day as well as thousands of discharges per day.
So lets say I want to pass a date parameter to accumulate the total registrations and discharges per day.
I have beat my head against the desk for the last two days because I believe this is a simple query but I just cannot get the results I want - so any help is greatly appreciated.
I have written the following sql but I do not get a sum of the total registrations and discharges and EXPR3 and Expr4 always equal each other which is not the case . For example on 20040301 I have 88 registrations and 17 discharges but I can't ever get the correct totals...
I wrote the following in Query Analyzer - but it does not work and I have went around in circles and have tried so many things I am just frustrated.......
Declare @.Parm_Beg_Date as nvarchar(8)
Set @.Parm_Beg_Date = 20040313
SELECT
Patient_Visit_Result_Master.PVR_Admin_Visit_Type,
Patient_Visit_Result_Master.PVR_Patient_Type,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Discharge_Date,
Count(Distinct(Patient_Visit_Result_Master.PVR_Registration_Date)) as Expr3,
Count(Distinct(Patient_Visit_Result_Master.PVR_Discharge_Date)) as Expr4
FROM Patient_Visit_Result_Master INNER JOIN
Patient_Visit_Result_Master Patient_Visit_Result_Master_1 ON
Patient_Visit_Result_Master.PVR_Hospital_ID = Patient_Visit_Result_Master_1.PVR_Hospital_ID AND
@.Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Registration_Date as nvarchar(8))or
@.Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Discharge_Date as nvarchar(8))
WHERE (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'E')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @.Parm_Beg_Date)
OR (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @.Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @.Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @.Parm_Beg_Date)
GROUP BY
Patient_Visit_Result_Master.PVR_Admin_Visit_Type,
Patient_Visit_Result_Master.PVR_Patient_Type,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Discharge_Date
Order By Patient_Visit_Result_Master.PVR_Admin_Visit_Type
Thanks in advance
Of course, you can break it down even more into smaller temp tables checking for each WHERE clause and the do a UNION into a bigger table and then perform the aggregation.
One thing I noticed.. you are using a DISTINCT along with the COUNT... as a general rule, you dont need to use a DISTINCT when counting records...|||Thank you for our reply!
That is the entire issue - I do not want to create an "temp" table and I have been trying to do everything to avoid that solution - and believe I am no SQL guero!
From 1998 to 2005 there are 16.5 million records based on In patients and Out patients - I know I am close (LOL) somewhere - but where!
I think what I will do- after seroius thought is to write a a "stat file" that contains the values I am looking for and have some kind of trigger to populate accordingly.
|||Here are two possible solutions make your tables UNION compatible and use UNIONALL or use CASE statement, try the link below for CASE statement. In SQL Server to use UNION you must have the same datatypes for all table facing the same direction. Run a search fro UNION operator in SQL Server BOL (books online). Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm|||As far as the DISTINCT clause I became desperate! I have NEVER used the Distinct keyword...
I did not want to do a UNION in that I query the file the second time and this is a HUGE file.
Been thinkin about this problem for a couple of days - so I think I will just write some code to accumulate the stats I need and then query from there!
But, thre still has to be a way to accomplish this in some sort of way - without writing some sort of stat file.
Thank you and best regards,
|||Why do you want to avoid using temp tables? they are a great tool when dealing with complex queries! and it will make your future maintenance work easier too.
by temp tables i do not mean creating a REAL table (that is not necessary) but in-memory tables that you drop after your query is complete, example:
select * into #temp from Customers
will create an in-memory table that will hold all the customers table data.
after you query is complete you execute:
drop table #temp
and thats it.|||Ok - I will try this - thank you.
I did not realize there were temporary tables in that a lot of stuff I have read I have seen that people create "in line" tables where they define all of the fields etceteras - that's why this forum has been great for me ---
Is there any problems when multiple people access the query with using #temp as the temporary table name - or do I use date and time as the table name ?
Can you create multiple temp tables and then do a join on them as well?
Really - thanks - I will try and post back.
Best Regards,
|||
As in the query I explained above, you can create an in-memory temp table on the fly and do not need to specify any of its fields, it will just be a replica of the table you are copying. Nobody can access your temp table outside of the current connection that you established, and yes, you can do JOINS, UNIONS, etc...
That is why, going back to your first point, I would divide the query in multiple steps. Each step will generate a temp table and after checking each step has been fulfilled properly I would join them and perform the rest of operations. Remember you don't need the DISTINCT in the COUNT
"JAVIGUILLEN"
I am really glad I found this forum!!!
Sincere THANKS! You were correct - this is very simple - wished I knew about the #temp tables before - I just don't know after all of the "stinking" searching I have done why I missed this capability?
Anyway, this is what I wrote and works pretty well - after I got messing with the Into - I only inserted the fields I needed in the temp tables instead of the other 60 plus fields that are in the table.
I also threw this into a stored procedure..... This SP will do this for Day, Week, Month, Quarter and Year. Should I consider the ALTER procedure or With RECOMPILE option - I have looked at these two alternatives but don't really understand YET...
This SQL stuff is pretty cool and I am excited...I am sure once you get pretty good you can do all kinds of magical stuff.
Declare @.Parm_Beg_Date as nvarchar(8)
Declare @.Parm_Hospital as nvarchar(10)
/***** Accumulate Total Registrations for the Period Passeds as @.Parm_Beg_Date *****/
SELECT
Patient_Visit_Result_Master.PVR_Hospital_ID,
Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Ward_ID,
Count(1) AS Registrations
INTO #Temp
FROM Patient_Visit_Result_Master
INNER JOIN
Hospital_Ward_Master
ON Patient_Visit_Result_Master.PVR_Hospital_ID = Hospital_Ward_Master.HWM_Hospital_ID
AND Patient_Visit_Result_Master.PVR_Ward_ID = Hospital_Ward_Master.HWM_Ward_ID
WHERE Patient_Visit_Result_Master.PVR_Hospital_ID = @.Parm_Hospital
AND Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Registration_Date AS nvarchar(8))
OR Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Registration_Date AS nvarchar(8))
GROUP BY
Patient_Visit_Result_Master.PVR_Hospital_ID,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Ward_ID
/***** Accumulte Total Discharges for the Period Passed as @.Parm_Beg_Date
SELECT
Patient_Visit_Result_Master.PVR_Hospital_ID,
Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) as PVR_Discharge_Date,
Patient_Visit_Result_Master.PVR_Ward_ID,
Count(1) AS Discharges
INTO #Temp1
FROM Patient_Visit_Result_Master
WHERE Patient_Visit_Result_Master.PVR_Hospital_ID = @.Parm_Hospital
AND Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Discharge_Date AS nvarchar(8))
OR Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Discharge_Date AS nvarchar(8))
GROUP BY
Patient_Visit_Result_Master.PVR_Hospital_ID,
Patient_Visit_Result_Master.PVR_Ward_ID,
Patient_Visit_Result_Master.PVR_Discharge_Date
/***** Join Total Registrations and Total Discharges for the Period Passed and Cast the fields the Report Data Set is Expecting ****/
SELECT
#Temp.PVR_Hospital_ID as PVR_Hospital_ID,
Cast(#Temp.PVR_Registration_Date as nvarchar (8))PVR_Registration_Date,
Cast(#Temp1.PVR_Discharge_Date as nvarchar(8)) PVR_Discharge_Date,
#Temp.PVR_Ward_ID as PVR_Ward_ID,
#Temp.Registrations + #Temp1.Discharges AS Expr1,
#Temp.Registrations + #Temp1.Discharges AS Expr2,
#Temp.Registrations as Expr3,
#Temp1.Discharges as Expr4,
Hospital_Ward_Master.HWM_Ward_Name,
CAST(@.Parm_Beg_Date AS nvarchar(8)) AS wrkdate,
wrkdate as LabelDescr1,
' ' as LabelDescr2,
'By Day' as LabelDescr
FROM #Temp
INNER JOIN
#Temp1
ON #Temp.PVR_Hospital_ID = #Temp1.PVR_Hospital_ID
INNER JOIN
Hospital_Ward_Master ON
#Temp.PVR_Ward_ID = Hospital_Ward_Master.HWM_Ward_ID
Group By
#Temp.PVR_Hospital_ID,
#Temp.PVR_Ward_ID,
Hospital_Ward_Master.HWM_Ward_Name,
PVR_Registration_Date,
PVR_Discharge_Date,
#Temp.Registrations,
#Temp1.Discharges
Drop Table #Temp
Drop Table #Temp1
|||I am glad I was able to help :)|||the ALTER functionality is used when you want to modify a stored procedure that already exists in the database.
RECOMPILE forces SQL Server to NOT use any cached version of the stored procedure that might have been created. Its used if you make a change in the code but SQL Server doesnt recognize it because it is using a cached version instead...
No comments:
Post a Comment