Easy to see, hard to put in SQL

Easy to see, hard to put in SQL

Post by Paul Hasting » Sat, 01 Dec 2001 05:09:05



Hi -

I am trying to figure out a query that will take  a set of serial numbers
which can be at multiple levels and produce an output which summarizes the
highest serial numbers at each level. Any row which has data at that level
and no higher gets a count of one for that level. The summary provides a
count of the totals for each level.

This is hard to put into words (and for me SQL) but it is fairly easy to
see. Here is a sample table and the output I am looking for.

Input table

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
1
2
3
4           hae1sn25
5           hae1sn24
6           hae1sn23
7           hae1sn22
8           hae1sn21
9           hae1sn17   hae2sn17
10          hae1sn16   hae2sn16
11          hae1sn13   hae2sn13   hae3sn13
12          hae1sn12   hae2sn12   hae3sn12
13          hae1sn11   hae2sn11   hae3sn11
14          hae1sn10   hae2sn10   hae3sn14

Output table (or set of columns)

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
3           5          2          4

Can anyone help me past my confusion?

Thanks In Advance,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
Voice: (478) 923-2662
FAX: (478) 923-5625

 
 
 

Easy to see, hard to put in SQL

Post by Rush Stron » Sat, 01 Dec 2001 08:25:59


This should get you started:

    SELECT ;
        SUM(IIF(NOT EMPTY(Level_0) AND EMPTY(Level_1+Level_2+Level_3), 1,
0)) AS Count_0,  ;
        SUM(IIF(NOT EMPTY(Level_1) AND EMPTY(Level_2+Level_3), 1, 0)) AS
Count_1,  ;
        SUM(IIF(NOT EMPTY(Level_2) AND EMPTY(Level_3), 1, 0)) AS Count_2,  ;
        SUM(IIF(NOT EMPTY(Level_3) AS Count_3  ;
    FROM [etc.]

 - Rush


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625



 
 
 

Easy to see, hard to put in SQL

Post by Anders Altber » Tue, 04 Dec 2001 04:39:35


Hi
Why not

Create Cursor Levels (level N(2), serial c(10))
Insert into levels values (0,'')
Insert into levels values (0,'')
Insert into levels values (0,'')
Insert into levels values (2,'sn17')
Insert into levels values (2,'sn16')
Insert into levels values (1,'sn25')
Insert into levels values (1,'sn24')
Insert into levels values (1,'sn23')
Insert into levels values (1,'sn22')
Insert into levels values (1,'sn21')
Insert into levels values (3,'sn13')
Insert into levels values (3,'sn12')
Insert into levels values (3,'sn11')
Insert into levels values (3,'sn14')
Browse nowait
Select level, count(*) group by level from levels
browse nowait

-Anders


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625


 
 
 

1. Easy to see, hard to put in SQL

Hi -

I am trying to figure out a query that will take  a set of serial numbers
which can be at multiple levels and produce an output which summarizes the
highest serial numbers at each level. Any row which has data at that level
and no higher gets a count of one for that level. The summary provides a
count of the totals for each level.

This is hard to put into words (and for me SQL) but it is fairly easy to
see. Here is a sample table and the output I am looking for.

Input table

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
1
2
3
4           hae1sn25
5           hae1sn24
6           hae1sn23
7           hae1sn22
8           hae1sn21
9           hae1sn17   hae2sn17
10          hae1sn16   hae2sn16
11          hae1sn13   hae2sn13   hae3sn13
12          hae1sn12   hae2sn12   hae3sn12
13          hae1sn11   hae2sn11   hae3sn11
14          hae1sn10   hae2sn10   hae3sn14

Output table (or set of columns)

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
3           5          2          4

Can anyone help me past my confusion?

Thanks In Advance,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
Voice: (478) 923-2662
FAX: (478) 923-5625

2. Processes Running

3. What is easiest way to put Access database to web, is it ASP+SQL+Javascript

4. Russian data in MsSQL-server / IIS5

5. 1 very hard and 1 very Easy question

6. running db2 scripts under dos command prompt

7. query help, hard for me, I hope easy for you

8. syncronizing databases

9. Two questions... one easy, one hard

10. FoxPro questions, 2 easy, 1 harder.. : )

11. Prolly easy question: putting values in listbox

12. sql*loader puts in in, how do I put it out

13. Client on 1 hard drive and server on other hard drive