March 2, 2008 at 12:31 am
I need to join these three tables to generate monthly totals. The Attribute_Value table has values for departments, facility, patients, physician, staff, etc. The treatment table has data from medical records and the lookup table has lookup codes to describe various data values. The query needs to group only by department and facility and calculate totals for previous month. If the query is run in march it should group and sum for Feb only.
The following are the sample tables and report.
Attribute_value
Attribute_IDAttribute_name
1Allergy
2Internal Medicine
3Cardiology
4Colma
5Daly City
6Sloat
7David England
8Nancy Sister
9CVS
10Longs Drugs
Lookupcode Table
Lookupcode_IDLookupcode_Type
1Department
2Facility
3Staff
4Physician
5Pharmacy
Treatment
Treatment_IDMedrec_NbrLookupcode_IDAttribute_IDDate
11112/10/2008
21242/10/2008
31472/10/2008
415102/10/2008
52122/10/2008
62252/10/2008
725102/10/2008
83122/10/2008
93252/10/2008
1035102/10/2008
114122/10/2008
124252/10/2008
1345103/1/2008
145113/1/2008
155243/1/2008
165473/1/2008
1755103/1/2008
186132/28/2008
196262/28/2008
207132/28/2008
217262/28/2008
Department Facility Monthly Total
Allergy Colma 1
Internal MedicineDaly City 3
Cardiology Sloat 2
Appreciate your help with the monthly report query
March 2, 2008 at 1:55 am
Use Case statement
March 2, 2008 at 8:21 am
Use Case statement
Is this coincidental or a user with the same name answering the post?
Anyway to elaborate on the solution:
You could simple use a SUM or COUNT case statement in your query.
SELECT
department,
facility,
COUNT(CASE
WHEN MONTH([DATE]) = MONTH(DATEADD(mm,-1,GETDATE()))
THEN Treatment_ID
END) AS [COUNT]
FROM MyTable
GROUP BY department, facility
March 2, 2008 at 11:58 am
Thank You for replying. It is the same person. Sorry for the confusion.
Please note the department and facility are not the column names. They are referenced in the table treatment by the lookup code. The department and facility have same medical record number in the treatment table. So each medical record has values in the treatment table and the value type is specified by the lookup code.
Please advise. Appreciate your help.
March 2, 2008 at 4:44 pm
Please note the department and facility are not the column names. They are referenced in the table treatment by the lookup code. The department and facility have same medical record number in the treatment table.
My previous post was to set you in the right direction. I was giving you general syntax, in the hopes that you would take it and try to solve the problem yourself. I hope you attempted to solve the problem yourself because in the long run you will learn more from failure than success.
The code below will give you the correct answer and will work each month without alteration.
DECLARE @Attribute_Value TABLE(
Attribute_ID INT IDENTITY(1,1),
Attribute_name VARCHAR(25)
)
INSERT INTO @Attribute_Value (Attribute_name)
SELECT 'Allergy' UNION ALL
SELECT 'Internal Medicine' UNION ALL
SELECT 'Cardiology' UNION ALL
SELECT 'Colma' UNION ALL
SELECT 'Daly City' UNION ALL
SELECT 'Sloat' UNION ALL
SELECT 'David England' UNION ALL
SELECT 'Nancy Sister' UNION ALL
SELECT 'CVS' UNION ALL
SELECT 'Longs Drugs'
DECLARE @Lookupcode Table(
Lookupcode_ID INT IDENTITY(1,1),
Lookupcode_Type VARCHAR(25)
)
INSERT INTO @Lookupcode (Lookupcode_Type)
SELECT 'Department' UNION ALL
SELECT 'Facility' UNION ALL
SELECT 'Staff' UNION ALL
SELECT 'Physician' UNION ALL
SELECT 'Pharmacy'
DECLARE @Treatment TABLE(
Treatment_ID INT IDENTITY(1,1),
Medrec_Nbr INT,
Lookupcode_ID INT,
Attribute_ID INT,
Date DATETIME
)
INSERT INTO @Treatment (Medrec_Nbr, Lookupcode_ID,Attribute_ID,Date)
SELECT 1, 1, 1, '2/10/2008' UNION ALL
SELECT 1, 2, 4, '2/10/2008' UNION ALL
SELECT 1, 4, 7, '2/10/2008' UNION ALL
SELECT 1, 5, 10,'2/10/2008' UNION ALL
SELECT 2, 1, 2, '2/10/2008' UNION ALL
SELECT 2, 2, 5, '2/10/2008' UNION ALL
SELECT 2, 5, 10,'2/10/2008' UNION ALL
SELECT 3, 1, 2, '2/10/2008' UNION ALL
SELECT 3, 2, 5, '2/10/2008' UNION ALL
SELECT 3, 5, 10,'2/10/2008' UNION ALL
SELECT 4, 1, 2, '2/10/2008' UNION ALL
SELECT 4, 2, 5, '2/10/2008' UNION ALL
SELECT 4, 5, 10,'3/1/2008' UNION ALL
SELECT 5, 1, 1, '3/1/2008' UNION ALL
SELECT 5, 2, 4, '3/1/2008' UNION ALL
SELECT 5, 4, 7, '3/1/2008' UNION ALL
SELECT 5, 5, 10,'3/1/2008' UNION ALL
SELECT 6, 1, 3, '2/28/2008' UNION ALL
SELECT 6, 2, 6, '2/28/2008' UNION ALL
SELECT 7, 1, 3, '2/28/2008' UNION ALL
SELECT 7, 2, 6, '2/28/2008'
SELECT
av.Attribute_name,
--count the id that occured last month.
COUNT(CASE
WHEN MONTH(t.Date) = MONTH(DATEADD(mm,-1,GETDATE()))
THEN t.Treatment_ID
END) AS [NbrTreatments]
FROM @Treatment t
INNER JOIN @Lookupcode lc
ON t.Lookupcode_ID = lc.Lookupcode_ID
INNER JOIN @Attribute_Value av
ON t.Attribute_ID = av.Attribute_ID
WHERE lc.Lookupcode_Type= 'FACILITY' OR
lc.Lookupcode_Type= 'DEPARTMENT'
GROUP BY av.Attribute_name
/*
Department Facility Monthly Total
Allergy Colma 1
Internal Medicine Daly City 3
Cardiology Sloat 2
*/
March 3, 2008 at 4:50 pm
Thank You for providing a very good sql script. My production table has lot of medical records in the treatment tables. Each medical record has one department and one facility. The script you gave extracts depart and facility in the same colum. How do I generate report with three columns? For department, facility and monthly_totals. I tried using CASE statement to get the facility. But it is not working. The production table has 21 rows for Cardiology and 21 rows sloat. So there are 21 medical records and each medical record has one row for department (cardiology) and one row for facility (sloat).
When I run the query I have to get three rows as follows
Department Facility Monthly_Total
Cardiology Sloat 21
March 3, 2008 at 9:33 pm
Okay. I have modified the query. It should produce the results you are looking for.
DECLARE @Attribute_Value TABLE(
Attribute_ID INT IDENTITY(1,1),
Attribute_name VARCHAR(25)
)
INSERT INTO @Attribute_Value (Attribute_name)
SELECT 'Allergy' UNION ALL
SELECT 'Internal Medicine' UNION ALL
SELECT 'Cardiology' UNION ALL
SELECT 'Colma' UNION ALL
SELECT 'Daly City' UNION ALL
SELECT 'Sloat' UNION ALL
SELECT 'David England' UNION ALL
SELECT 'Nancy Sister' UNION ALL
SELECT 'CVS' UNION ALL
SELECT 'Longs Drugs'
DECLARE @Lookupcode Table(
Lookupcode_ID INT IDENTITY(1,1),
Lookupcode_Type VARCHAR(25)
)
INSERT INTO @Lookupcode (Lookupcode_Type)
SELECT 'Department' UNION ALL
SELECT 'Facility' UNION ALL
SELECT 'Staff' UNION ALL
SELECT 'Physician' UNION ALL
SELECT 'Pharmacy'
DECLARE @Treatment TABLE(
Treatment_ID INT IDENTITY(1,1),
Medrec_Nbr INT,
Lookupcode_ID INT,
Attribute_ID INT,
Date DATETIME
)
INSERT INTO @Treatment (Medrec_Nbr, Lookupcode_ID,Attribute_ID,Date)
SELECT 1, 1, 1, '2/10/2008' UNION ALL
SELECT 1, 2, 4, '2/10/2008' UNION ALL
SELECT 1, 4, 7, '2/10/2008' UNION ALL
SELECT 1, 5, 10,'2/10/2008' UNION ALL
SELECT 2, 1, 2, '2/10/2008' UNION ALL
SELECT 2, 2, 5, '2/10/2008' UNION ALL
SELECT 2, 5, 10,'2/10/2008' UNION ALL
SELECT 3, 1, 2, '2/10/2008' UNION ALL
SELECT 3, 2, 5, '2/10/2008' UNION ALL
SELECT 3, 5, 10,'2/10/2008' UNION ALL
SELECT 4, 1, 2, '2/10/2008' UNION ALL
SELECT 4, 2, 5, '2/10/2008' UNION ALL
SELECT 4, 5, 10,'3/1/2008' UNION ALL
SELECT 5, 1, 1, '3/1/2008' UNION ALL
SELECT 5, 2, 4, '3/1/2008' UNION ALL
SELECT 5, 4, 7, '3/1/2008' UNION ALL
SELECT 5, 5, 10,'3/1/2008' UNION ALL
SELECT 6, 1, 3, '2/28/2008' UNION ALL
SELECT 6, 2, 6, '2/28/2008' UNION ALL
SELECT 7, 1, 3, '2/28/2008' UNION ALL
SELECT 7, 2, 6, '2/28/2008'
DECLARE @bom DATETIME, @eom DATETIME
SET @bom =
--Previous BOM
dateadd(m, datediff(m, 0, GETDATE())-1, 0)
set @eom =
--Previous EOM
DateAdd(second, -1, DateAdd(month, DateDiff(month, 0,GETDATE()), 0))
--=====================================================
SELECT a.Attribute_Name as [Department],
b.Attribute_Name AS [Facility],
COUNT(a.Attribute_Name) AS [Nbr_Treatments]
FROM(
SELECT t.Medrec_Nbr, lc.Lookupcode_id, av.Attribute_Name
FROM @Treatment t
INNER JOIN @Lookupcode lc
ON t.Lookupcode_ID = lc.Lookupcode_ID
INNER JOIN @Attribute_Value av
ON t.Attribute_ID = av.Attribute_ID
WHERE (t.Date >= @bom AND t.Date <= @eom) AND
(lc.Lookupcode_Type= 'DEPARTMENT' )
) AS a
INNER JOIN
(
SELECT t.Medrec_Nbr, lc.Lookupcode_id, av.Attribute_Name
FROM @Treatment t
INNER JOIN @Lookupcode lc
ON t.Lookupcode_ID = lc.Lookupcode_ID
INNER JOIN @Attribute_Value av
ON t.Attribute_ID = av.Attribute_ID
WHERE (t.Date >= @bom AND t.Date <= @eom) AND
(lc.Lookupcode_Type= 'FACILITY')
) AS b
ON a.Medrec_Nbr = b.Medrec_Nbr AND
a.Lookupcode_ID <> b.Lookupcode_ID
GROUP BY a.Attribute_Name, b.Attribute_Name
ORDER BY a.Attribute_Name, b.Attribute_Name
--==============================================
March 6, 2008 at 11:19 am
Thank You. Work's fine. Appretiate your help
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply