Using SQL to count as if using an array 
Author Message
 Using SQL to count as if using an array

I have this problem which I used to use pascal to solve.  I have list of
hospital attenders with a date of admission and the number of days they
spent in hospital

Date                        days in hospital
01/04/96                             5
01/04/96                             1
01/04/96                             1
02/04/96                             2
03/04/96                             1
03/04/96                             1
and so on

I want to count up the number of people in hospital on each day, so the
output would look like this

Date                        patients in hospital
01/04/96                             3
02/04/96                             2
03/04/96                             3
04/04/96                             1

If I was programming this I would set up an array and use a while loop
to increment the count in each cell like this

stopdate:= date+number of days in hospital
while date<=stopdate do
    array[stopdate].count:=  array[stopdate].count +1
   stopdate:=stopdate-1

How do I do this in SQL?  I have thought of using a table with all the
dates listed in one column and another column for the count as the
array.  I can match the dates but how do I increment the count?

Yours perplexed

Richard



Sat, 05 Apr 2003 03:00:00 GMT
 Using SQL to count as if using an array

bulk insert your raw data into a table and

select Date, sum ( days in hospital )
from table
group by Date

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group)
-----------------------------------------------------------------


Quote:
> I have this problem which I used to use pascal to solve.  I have list of
> hospital attenders with a date of admission and the number of days they
> spent in hospital

> Date                        days in hospital
> 01/04/96                             5
> 01/04/96                             1
> 01/04/96                             1
> 02/04/96                             2
> 03/04/96                             1
> 03/04/96                             1
> and so on

> I want to count up the number of people in hospital on each day, so the
> output would look like this

> Date                        patients in hospital
> 01/04/96                             3
> 02/04/96                             2
> 03/04/96                             3
> 04/04/96                             1

> If I was programming this I would set up an array and use a while loop
> to increment the count in each cell like this

> stopdate:= date+number of days in hospital
> while date<=stopdate do
>     array[stopdate].count:=  array[stopdate].count +1
>    stopdate:=stopdate-1

> How do I do this in SQL?  I have thought of using a table with all the
> dates listed in one column and another column for the count as the
> array.  I can match the dates but how do I increment the count?

> Yours perplexed

> Richard



Sat, 05 Apr 2003 03:00:00 GMT
 Using SQL to count as if using an array

If I'm not mistaken, that would give the sum of the length of stays by date
of admission, not the patient count on each day.


Between [Date] and DateAdd(d, [days in hospital], [date])), but not how you
could get this in a report form (at least directly).  If you had a table
with dates, you could do a join with a Sum(Case...) to calculate the number
of patients on those dates.

HTH,
Tore.


Quote:
> bulk insert your raw data into a table and

> select Date, sum ( days in hospital )
> from table
> group by Date

> --
> Thanks,
> David Satz
> Principal Software Engineer
> Hyperion Solutions
> ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
> (Please reply to group)
> -----------------------------------------------------------------



> > I have this problem which I used to use pascal to solve.  I have list of
> > hospital attenders with a date of admission and the number of days they
> > spent in hospital

> > Date                        days in hospital
> > 01/04/96                             5
> > 01/04/96                             1
> > 01/04/96                             1
> > 02/04/96                             2
> > 03/04/96                             1
> > 03/04/96                             1
> > and so on

> > I want to count up the number of people in hospital on each day, so the
> > output would look like this

> > Date                        patients in hospital
> > 01/04/96                             3
> > 02/04/96                             2
> > 03/04/96                             3
> > 04/04/96                             1

> > If I was programming this I would set up an array and use a while loop
> > to increment the count in each cell like this

> > stopdate:= date+number of days in hospital
> > while date<=stopdate do
> >     array[stopdate].count:=  array[stopdate].count +1
> >    stopdate:=stopdate-1

> > How do I do this in SQL?  I have thought of using a table with all the
> > dates listed in one column and another column for the count as the
> > array.  I can match the dates but how do I increment the count?

> > Yours perplexed

> > Richard



Sat, 05 Apr 2003 03:00:00 GMT
 Using SQL to count as if using an array

Richard,

Start by creating a Calendar table with one datetime column, and one
row for each day you want to test.  Then:

  SELECT C.date, count(P.patient_id) as patients_in_hospital
    FROM Patients as P
    JOIN Calendar as C
      ON C.calendar_date BETWEEN P.admission_date
                             AND dateadd(day,days_in,P.admission_date)
   GROUP BY C.date

Roy

Quote:

>I have this problem which I used to use pascal to solve.  I have list of
>hospital attenders with a date of admission and the number of days they
>spent in hospital

>Date                        days in hospital
>01/04/96                             5
>01/04/96                             1
>01/04/96                             1
>02/04/96                             2
>03/04/96                             1
>03/04/96                             1
>and so on

>I want to count up the number of people in hospital on each day, so the
>output would look like this

>Date                        patients in hospital
>01/04/96                             3
>02/04/96                             2
>03/04/96                             3
>04/04/96                             1

>If I was programming this I would set up an array and use a while loop
>to increment the count in each cell like this

>stopdate:= date+number of days in hospital
>while date<=stopdate do
>    array[stopdate].count:=  array[stopdate].count +1
>   stopdate:=stopdate-1

>How do I do this in SQL?  I have thought of using a table with all the
>dates listed in one column and another column for the count as the
>array.  I can match the dates but how do I increment the count?

>Yours perplexed

>Richard



Sat, 05 Apr 2003 03:00:00 GMT
 Using SQL to count as if using an array
Richard,
Create a Calendar table which will contain a datetime column and will
allow you show that there were no admissions on a particular day,
which .

CREATE TABLE Calendar
(CalendarDate datetime PRIMARY KEY)

insert into calendar Values('1/1/96')
insert into calendar Values('1/2/96')
insert into calendar Values('1/3/96')
insert into calendar Values('1/4/96')
insert into calendar Values('1/5/96')
insert into calendar Values('1/6/96')
insert into calendar Values('1/7/96')
insert into calendar Values('1/8/96')
insert into calendar Values('1/9/96')
insert into calendar Values('1/10/96')
insert into calendar Values('2/4/96')
insert into calendar Values('2/5/96')
insert into calendar Values('3/4/96')

CREATE TABLE Admissions
( AdmissionID int IDENTITY(1,1)    PRIMARY KEY,
  AdmissionDate datetime NOT NULL,
  PersonID int NOT NULL,
  DaysInHospital int NOT NULL)

INSERT INTO Admissions VALUES('1/4/1996',20, 5)
INSERT INTO Admissions VALUES('1/4/1996',21, 1)
INSERT INTO Admissions VALUES('1/4/1996',22, 1)
INSERT INTO Admissions VALUES('2/4/1996',23, 2)
INSERT INTO Admissions VALUES('2/4/1996',24, 1)
INSERT INTO Admissions VALUES('3/4/1996',25, 1)
INSERT INTO Admissions VALUES('1/5/1996',26, 3)
INSERT INTO Admissions VALUES('1/6/1996',27, 2)
INSERT INTO Admissions VALUES('1/7/1996',28, 1)
INSERT INTO Admissions VALUES('1/8/1996',29, 1)
INSERT INTO Admissions VALUES('1/9/1996',30, 1)

SELECT C1.CalendarDate, Count(A1.AdmissionID) AS PatientsInHospital
        FROM calendar C1
        LEFT JOIN Admissions A1 ON C1.CalendarDate
        BETWEEN  A1.AdmissionDate AND
                 DateAdd(day, A1.DaysInHospital-1 , A1.AdmissionDate)
        GROUP BY C1.CalendarDate

Jon

On Tue, 17 Oct 2000 15:28:57 +0100, Richard Wilson

Quote:

>I have this problem which I used to use pascal to solve.  I have list of
>hospital attenders with a date of admission and the number of days they
>spent in hospital

>Date                        days in hospital
>01/04/96                             5
>01/04/96                             1
>01/04/96                             1
>02/04/96                             2
>03/04/96                             1
>03/04/96                             1
>and so on

>I want to count up the number of people in hospital on each day, so the
>output would look like this

>Date                        patients in hospital
>01/04/96                             3
>02/04/96                             2
>03/04/96                             3
>04/04/96                             1

>If I was programming this I would set up an array and use a while loop
>to increment the count in each cell like this

>stopdate:= date+number of days in hospital
>while date<=stopdate do
>    array[stopdate].count:=  array[stopdate].count +1
>   stopdate:=stopdate-1

>How do I do this in SQL?  I have thought of using a table with all the
>dates listed in one column and another column for the count as the
>array.  I can match the dates but how do I increment the count?

>Yours perplexed

>Richard



Sat, 05 Apr 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Syntax error COUNT() : Using COUNT() with char field

2. using SELECT COUNT(*) to count distinct records

3. Logging Array's in to SQL Server using VB

4. local SQL variables when using arrays of params

5. Using arrays within SQL batches

6. JDBC 2.0 datatype java.sql.array cannot be used

7. passing array to PL/SQL using OCI

8. array inserts using dynamic sql

9. question on passing arrays (pl/sql tables) using webserver2.0 HELP

10. local SQL variables when using arrays of params


 
Powered by phpBB® Forum Software