Urgent help

Urgent help

Post by Alan » Fri, 25 Jul 2003 08:35:13



I have the following table BucketTable :

BucketID    Time    WaterLevel
------------------------------------------
1                9:00    Empty
1                10:00    Half
1                11:00    Full
2                9:20    Empty
2                11:30    Half
2                13:00    Full
3                11:00    Empty
3                14:10    Half
3                16:00    Full

This table keeps the timestamp of the water level of the buckets.
I want to write a SQL statement to show the following :

BucketID   Empty    Half    Full
1                9:00        10:00    11:00
2                9:20        11:30    13:00
3                11:00       14:10    16:00

I tried

Select BucketID,
            IF(WaterLevel='Empty', Time, ''),
            IF(WaterLevel='Half', Time, '')
            IF(WaterLevel='Full', Time, '')
FROM Bucket
ORDER BY BucketID;

However, it did not work as it gave me 9 records rather than 3.
I also tried DISTINCT and GROUP BY, it gave me 3 records but only with the
Empty column has time.

 
 
 

Urgent help

Post by Bob Badou » Fri, 25 Jul 2003 08:40:59



Quote:> I have the following table BucketTable :

> BucketID    Time    WaterLevel
> ------------------------------------------
> 1                9:00    Empty
> 1                10:00    Half
> 1                11:00    Full
> 2                9:20    Empty
> 2                11:30    Half
> 2                13:00    Full
> 3                11:00    Empty
> 3                14:10    Half
> 3                16:00    Full

> This table keeps the timestamp of the water level of the buckets.
> I want to write a SQL statement to show the following :

> BucketID   Empty    Half    Full
> 1                9:00        10:00    11:00
> 2                9:20        11:30    13:00
> 3                11:00       14:10    16:00

> I tried

> Select BucketID,
>             IF(WaterLevel='Empty', Time, ''),
>             IF(WaterLevel='Half', Time, '')
>             IF(WaterLevel='Full', Time, '')
> FROM Bucket
> ORDER BY BucketID;

> However, it did not work as it gave me 9 records rather than 3.
> I also tried DISTINCT and GROUP BY, it gave me 3 records but only with the
> Empty column has time.

Try GROUP BY using the MAX aggregate on the times.

 
 
 

Urgent help

Post by John Gilso » Fri, 25 Jul 2003 13:44:07



> I have the following table BucketTable :

> BucketID    Time    WaterLevel
> ------------------------------------------
> 1                9:00    Empty
> 1                10:00    Half
> 1                11:00    Full
> 2                9:20    Empty
> 2                11:30    Half
> 2                13:00    Full
> 3                11:00    Empty
> 3                14:10    Half
> 3                16:00    Full

> This table keeps the timestamp of the water level of the buckets.
> I want to write a SQL statement to show the following :

> BucketID   Empty    Half    Full
> 1                9:00        10:00    11:00
> 2                9:20        11:30    13:00
> 3                11:00       14:10    16:00

> I tried

> Select BucketID,
>             IF(WaterLevel='Empty', Time, ''),
>             IF(WaterLevel='Half', Time, '')
>             IF(WaterLevel='Full', Time, '')
> FROM Bucket
> ORDER BY BucketID;

> However, it did not work as it gave me 9 records rather than 3.
> I also tried DISTINCT and GROUP BY, it gave me 3 records but only with the
> Empty column has time.

SELECT BTEmpty.BucketID,
               BTEmpty.Time AS Empty,
               BTHalf.Time AS Half,
               BTFull.Time AS "Full"
FROM BucketTable AS BTEmpty
            INNER JOIN
            BucketTable AS BTHalf
            ON BTEmpty.BucketID = BTHalf.BucketID AND
                   BTEmpty.WaterLevel = 'Empty' AND
                   BTHalf.WaterLevel = 'Half'
            INNER JOIN
            BucketTable AS BTFull
            ON BTFull.BucketID = BTEmpty.BucketID AND
                   BTFull.WaterLevel = 'Full'

Regards,
jag

 
 
 

Urgent help

Post by Lennart Jonss » Fri, 25 Jul 2003 16:28:50



> I have the following table BucketTable :

Try:

create table bucket (
        bucketid int not null,
        measure time not null,
        level char(5) not null,
        primary key (bucketid, measure, level),
        check (level in ('Empty', 'Half', 'Full'))
);

insert into bucket values (1, '9:00' , 'Empty');
insert into bucket values (1, '10:00', 'Half');
insert into bucket values (1, '11:00', 'Full');
insert into bucket values (2, '9:20' , 'Empty');
insert into bucket values (2, '11:30', 'Half');
insert into bucket values (2, '13:00', 'Full');
insert into bucket values (3, '11:00', 'Empty');
insert into bucket values (3, '14:10', 'Half');
insert into bucket values (3, '16:00', 'Full');

select b0.bucketid, min(b1.measure), min(b2.measure), min(b3.measure)
from bucket b0, bucket b1, bucket b2, bucket b3
where
        b0.bucketid = b1.bucketid and
        b0.bucketid = b2.bucketid and
        b0.bucketid = b3.bucketid and
        b1.level = 'Empty' and
        b2.level = 'Half' and
        b3.level = 'Full'
group by b0.bucketid;

HTH
/Lennart
--
the above email no longer works due to spam.
values'lennart'||CHR(46)||'jonsson'||CHR(64)||'enlight'||CHR(46)||'net'

 
 
 

Urgent help

Post by Bets » Sat, 26 Jul 2003 04:38:38



Quote:> > I have the following table BucketTable :

> > BucketID    Time    WaterLevel
> > ------------------------------------------
> > 1                9:00    Empty
> > 1                10:00    Half
> > 1                11:00    Full
> > 2                9:20    Empty
> > 2                11:30    Half
> > 2                13:00    Full
> > 3                11:00    Empty
> > 3                14:10    Half
> > 3                16:00    Full

> > This table keeps the timestamp of the water level of the buckets.
> > I want to write a SQL statement to show the following :

> > BucketID   Empty    Half    Full
> > 1                9:00        10:00    11:00
> > 2                9:20        11:30    13:00
> > 3                11:00       14:10    16:00

This is a simple crosstab query.  The SQL looks like this:

TRANSFORM First(tblBuckets.Status) AS FirstOfStatus
SELECT tblBuckets.BucketID
FROM tblBuckets
GROUP BY tblBuckets.BucketID
PIVOT tblBuckets.Time;

By the way, "Time" is not a good choice for a field name.  It would be
better to call it "TimeCheck", or "StatusTime" or something that is more
obviously user-created.

HTH-  Betsy

 
 
 

1. URGENT URGENT HELP

Hello,
my problem is:
on a batch file in Win Nt40 I need close the connection on DBASE , (now i
must use the sybase central )make a backup and open the connection.
please what is the string to close Sybase and after reload Sybase?

Tanks Flavio

2. Upgrade Question-Will this work via Wizard?

3. Urgent help needed please help

4. Calling stored procedures from VB

5. Please Help Urgent help needed!!!!

6. Logical OR testing

7. Urgent Help Needed AP/SCO 6.1 - HELP!!

8. How get keyset from ADO command?

9. Urgent help with Triggers in MS-SQL 2000

10. Urgent Help Needed

11. Urgent help -Recovering a deleted table

12. Urgent Help Please

13. URGENT HELP - keeping integrity while not sharing the same DB