Need to add Count to SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to add Count to SP

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-26 : 18:17:04

The following SP returns data of patients. It is used to populate a detail screen. What I need to do is to show the data as a summary by day for a header screen (which you would click on to get the detail)
So, my question is, how to make a count here that will give me the results by date? perhaps it is needed to add the date columns here as well?

USE [PRO2]
GO
/****** Object: StoredProcedure [PTC].[THS_GET_BMDNO] Script Date: 11/26/2011 18:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [PTC].[THS_GET_BMDNO](
@FACILITYKEY CHAR(4),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@MENUID NUMERIC(9),
@MENUIDVALUE NUMERIC(9),
@MINVALUE NUMERIC(9),
@MAXVALUE NUMERIC(9),
@SHIFTFACILITY CHAR(4),
@THEUNIT CHAR(2),
@THESHIFT NUMERIC(1))
AS
BEGIN
/*
--
*/
DECLARE
@ARCH NUMERIC(1),
@SPLITDATE DATETIME,
@I NUMERIC(9, 0);

SELECT @SPLITDATE = NULLIF(SPLIT_DATE, CONVERT(DATETIME, '20000101', 112)) + 1
FROM OEN.GEN_P_ARCHIVE
WHERE FACILITY_KEY = @FACILITYKEY;

IF @@ROWCOUNT = 0
SET @SPLITDATE = CONVERT(DATETIME, '20000102', 112);

SET @ARCH = 0;
IF @SPLITDATE <= @ENDDATE
SET @ARCH = @ARCH + 1;
IF @SPLITDATE >= @STARTDATE
SET @ARCH = @ARCH - 1;

SELECT PM.NURSING_UNIT AS UNIT, PM.ROOM_BED, PM.PAT_NUMBER, PM.PATIENT_ID, PM.MRN,
PM.FIRST_NAME, PM.LAST_NAME, RTRIM(PM.LAST_NAME) + ',' + RTRIM(PM.FIRST_NAME) AS NAME_FULL,
CASE WHEN PAT_BM.PAT_NUMBER IS NULL THEN 0 ELSE 1 END AS IS_FOUND,
CASE WHEN PAT_BM.PAT_NUMBER IS NULL THEN 'No Info Found' ELSE 'Didn''t occur' END AS INFO
FROM OEN.GEN_M_PATIENT_MAST PM
LEFT JOIN
(SELECT DISTINCT TR1.PAT_NUMBER
FROM OPTC.THS_T_TRANSACTIONS1 TR1
JOIN OEN.GEN_M_PATIENT_MAST PM1 ON (PM1.PAT_NUMBER = TR1.PAT_NUMBER)
WHERE @ARCH >= 0
AND TR1.FACILITY_KEY = @FACILITYKEY
AND TR1.TR_DATETIME BETWEEN @STARTDATE AND @ENDDATE
AND TR1.EDIT_NO < 0
AND TR1.MENU_ID = @MENUID
AND TR1.MENU_ID_VALUE = @MENUIDVALUE
AND (NULLIF(@THEUNIT, '') IS NULL OR PM1.UNIT_CODE = @THEUNIT)
UNION
SELECT DISTINCT TR2.PAT_NUMBER
FROM PTC.THS_T_TRANSACTIONS_ARCHIVE TR2
JOIN GEN.GEN_M_PATIENT_MAST PM2 ON (PM2.PAT_NUMBER = TR2.PAT_NUMBER)
WHERE @ARCH <= 0
AND TR2.FACILITY_KEY = @FACILITYKEY
AND TR2.TR_DATETIME BETWEEN @STARTDATE AND @ENDDATE
AND TR2.EDIT_NO < 0
AND TR2.MENU_ID = @MENUID
AND TR2.MENU_ID_VALUE = @MENUIDVALUE
AND (NULLIF(@THEUNIT, '') IS NULL OR PM2.UNIT_CODE = @THEUNIT)) PAT_BM
ON (PAT_BM.PAT_NUMBER = PM.PAT_NUMBER)
WHERE PM.FACILITY_KEY = @FACILITYKEY
AND PM.ADMIT_DATE <= @STARTDATE
AND (PM.DISCHARGE_DATE IS NULL OR (PM.DISCHARGE_DATE >= @ENDDATE))
AND (NULLIF(@THEUNIT, '') IS NULL OR PM.UNIT_CODE = @THEUNIT)
AND ((@ARCH < 0) OR NOT EXISTS(
SELECT *
FROM PTC.THS_T_TRANSACTIONS1 T1
WHERE T1.PAT_NUMBER = PM.PAT_NUMBER
AND T1.FACILITY_KEY = @FACILITYKEY
AND T1.TR_DATETIME BETWEEN @STARTDATE AND @ENDDATE
AND T1.EDIT_NO < 0
AND T1.MENU_ID = @MENUID
AND T1.MENU_ID_VALUE <> @MENUIDVALUE))
AND ((@ARCH > 0) OR NOT EXISTS(
SELECT *
FROM PTC.THS_T_TRANSACTIONS_ARCHIVE T2
WHERE T2.PAT_NUMBER = PM.PAT_NUMBER
AND T2.FACILITY_KEY = @FACILITYKEY
AND T2.TR_DATETIME BETWEEN @STARTDATE AND @ENDDATE
AND T2.EDIT_NO < 0
AND T2.MENU_ID = @MENUID
AND T2.MENU_ID_VALUE <> @MENUIDVALUE))
ORDER BY 1, 2, 3;
END

data returned: (plus more)

UNIT 1 0100 C 154920 123753 300009 ERICA GEN GEN,ERICA 0 No Info Found

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 03:10:46

you need to group data by DAY(datefield) and then take count(*) from that to get count. perhaps you can have a category parameter in procedure with values Summary and Detail. when passed Summary, it will just give days with counts and when passed as detail it will give above detail itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources