CODEWITHSUNDEEP
sql-servercrystal-reports
Shahad g
Shahad g

Reputation: 79

writing a mathematical formula in sql

Using the following sql query :

Select count(*) as Totaladmit From v_dbPatientAdmissions where AdmitDate > '2017-01-01' and AdmitDate <'2017-12-30' and PatientType = 2
Select count(*) as TotalDischarge From v_dbPatientAdmissions where DischargeDate > '2017-01-01' and DischargeDate <'2017-012-30' and PatientType = 2
select count(*) as totalIP from v_dbPatientAdmissions where PatientType=2 and DischargeDate is null 
select count (BedName) as Bedcount from V_Beds

We get the following output: 

TotalAdmit
66668

TotalDischarge
6651

TotalIP
91

BedCount
174

I want to write a mathematical formula that does the following : enter image description here

In order to crate a crystal report that returns only one value (Rate), also for the Totaladmit and TotalDischarge i want to apply a condition the excludes the records that shares the same AdmitDate and DischargeDate.

Upvotes: 2

Views: 3055

Answers (3)

PSK
PSK

Reputation: 17943

As you have different condition on your select, so common group by with WHERE will not work.

You can try like following. This will be the simplest solution.

SELECT ( ( TotalIp + Totaladmit - TotalDischarge ) / BedCount ) * 100 AS 
       [Output] 
FROM   (SELECT (SELECT Count(*) 
                From   v_dbPatientAdmissions 
                where  AdmitDate > '2017-01-01' 
                       and AdmitDate < '2017-12-30' 
                       and PatientType = 2)       as Totaladmit, 
               (SELECT Count(*) 
                From   v_dbPatientAdmissions 
                where  DischargeDate > '2017-01-01' 
                       and DischargeDate < '2017-12-30' 
                       and PatientType = 2)       as TotalDischarge, 
               (SELECT Count(*) 
                from   v_dbPatientAdmissions 
                where  PatientType = 2 
                       and DischargeDate is null) as TotalIp, 
               (SELECT Count (BedName) 
                from   V_Beds)                    AS Bedcount) T

Apart from this, you can also use CROSS APPLY for doing the same thing.

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17615

I would bypass the complication of same day discharge by including them in the calculation for example

drop table t
go
create table t(patientid int, admitdate date, dischargedate date)
create table b(bedname varchar(1))
go
truncate table t
insert into t values (1,'2017-01-01','2017-01-01'),(1,'2017-02-01',null),(2,'2017-01-01','2017-02-01'),
(3,'2016-01-01','2017-01-01'),(4,'2016-01-01',null)

insert into b values ('a'),('b')

select (admittednull + (admitted - discharged - sameday)) / beds * 100 as calulatedratio
from
(
select sum(case when year(admitdate) = 2017 then 1 else 0 end) as admitted,
        sum(case when year(dischargedate) = 2017 then 1 else 0 end) as discharged,
        sum(case when year(admitdate) <> 2017 and dischargedate is null then 1 else 0 end) as admittednull,
        (select count(*) from b) beds,
        sum(case when year(admitdate) = 2017 and year(dischargedate) = 2017 and admitdate = dischargedate then 1 else 0 end) sameday
from t
) s

As it happens this returns a value of 0.

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2011

Please try this-

SELECT
    ( ( 
       SUM(CASE WHEN PatientType=2 and DischargeDate is null THEN 1 ELSE 0 END) OVER()
     + SUM(CASE WHEN AdmitDate > '2017-01-01' and AdmitDate <'2017-12-30' and PatientType = 2 THEN 1 ELSE 0 END) OVER() )
     - (SUM(CASE WHEN DischargeDate > '2017-01-01' and DischargeDate <'2017-012-30' and PatientType = 2 THEN 1 ELSE 0 END) OVER() )
     ) * 100.0 / ( select COUNT(BedName) OVER() from V_Beds )
FROM v_dbPatientAdmissions

Upvotes: 0

Related Questions