report/summary query design help needed

report/summary query design help needed

Post by Michael Marti » Thu, 16 Aug 2001 02:27:11



I have a relatively complex query I'm trying to design, and I'm having
trouble
getting the basic structure to work.  Here is a simplified example of what
I'm trying to do:

I have one table with the following fields:
employeeID int
timeoffreason varchar(20)

A sample of the table would be
employeeID    timeoffreason
1                    sick
1                    vacation
1                    sick
1                    holiday
2                    sick
2                    vaction
3                    sick
3                    sick

I'm trying to design a query that would produce the following from the above
table:
employeeID    timeoffreason    timeoffreason_total  timeoffreasonPercent
------------    --------------    -------------------  ---------------------
1                    sick                   2
50.0
1                    vacation            1                              25.0
1                    holiday              1
25.0
2                    sick                   1
50.0
2                    vacation             1                             50.0
3                   sick                    2
100.00

Where employeeid would be the employee, timeoffreason would be the reason,
timeoffreason_total is the distinct total for the reason of the particular
employee (so it is grouped by the employeeID), and tiemoffreasonPercent is
the percentage of a reason of the total number of reasons for a particular
employee.

The timeoffreasonPercent is what is giving me the trouble.
I can't get the total amount of records for an employee.  count(employeeID)
gives me a total for a particular reason  So count(employeeID) and
count(timeoffreason)
give me the same number, for instance in employeeID for timeoffreason: sick,
I get 2 and 2.

which would give me 100% for the timeoffreason percent if i was to divide
the timeoffreason_total by the total records for the employeeID.

Can this be done within 1 query?  I could run separate queries to get the
totals per employee, but in reality this is a table containing 20+
millionrecords which also does a join on another table. But if anyone has
some suggestions for the simplified example given above, I should be able to
apply that to a larger scale query.

Thanks in advance,

Michael

 
 
 

report/summary query design help needed

Post by Val Mazu » Thu, 16 Aug 2001 02:49:39


Hi,

What you try to do calls pivot table.
Here is an example of SP which creates pivot table using
cursor

CREATE TABLE [dbo].[tblSales1990_91] (
        [SalesYear] [smallint] NOT NULL ,
        [Quart] [tinyint] NOT NULL ,
        [Amount] [decimal](2, 1) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tblSales1990_91] (SalesYear, Quart,
Amount) VALUES (1992, 1, 1.1)
INSERT INTO [dbo].[tblSales1990_91] (SalesYear, Quart,
Amount) VALUES (1992, 2, 1.2)
INSERT INTO [dbo].[tblSales1990_91] (SalesYear, Quart,
Amount) VALUES (1992, 3, 1.3)
INSERT INTO [dbo].[tblSales1990_91] (SalesYear, Quart,
Amount) VALUES (1991, 1, 1.4)
INSERT INTO [dbo].[tblSales1990_91] (SalesYear, Quart,
Amount) VALUES (1991, 4, 1.5)

if exists(select * from sysobjects where type='P' and
name='usp_PivotTable_10092000')
        DROP PROCEDURE usp_PivotTable_10092000

go

CREATE PROCEDURE usp_PivotTable_10092000 (

AS


if exists(select * from sysobjects where type='U' and

BEGIN


END


(SalesYear int)'


DISTINCT SalesYear FROM tblSales1990_91'





DECLARE curCur CURSOR FOR
        SELECT SalesYear, Quart, Sum(amount) FROM
tblSales1990_91
                GROUP BY SalesYear, Quart
                ORDER BY SalesYear, Quart  --ASC

OPEN curCur



BEGIN

   if NOT exists(select * from syscolumns where id =


      begin



      end







END

CLOSE curCur
DEALLOCATE curCur



return

/*
exec usp_PivotTable_10092000 'tblPivot'
*/

I hope it will help you

Val

Quote:>-----Original Message-----
>I have a relatively complex query I'm trying to design,
and I'm having
>trouble
>getting the basic structure to work.  Here is a

simplified example of what
Quote:>I'm trying to do:

>I have one table with the following fields:
>employeeID int
>timeoffreason varchar(20)

>A sample of the table would be
>employeeID    timeoffreason
>1                    sick
>1                    vacation
>1                    sick
>1                    holiday
>2                    sick
>2                    vaction
>3                    sick
>3                    sick

>I'm trying to design a query that would produce the

following from the above
Quote:>table:
>employeeID    timeoffreason    timeoffreason_total  

timeoffreasonPercent
Quote:>------------    --------------    -------------------  ---
------------------
>1                    sick                   2
>50.0
>1                    vacation            

1                              25.0
Quote:>1                    holiday              1
>25.0
>2                    sick                   1
>50.0
>2                    vacation            

1                             50.0
Quote:>3                   sick                    2
>100.00

>Where employeeid would be the employee, timeoffreason

would be the reason,
Quote:>timeoffreason_total is the distinct total for the reason
of the particular
>employee (so it is grouped by the employeeID), and

tiemoffreasonPercent is
Quote:>the percentage of a reason of the total number of reasons
for a particular
>employee.

>The timeoffreasonPercent is what is giving me the trouble.
>I can't get the total amount of records for an employee.  
count(employeeID)
>gives me a total for a particular reason  So count
(employeeID) and
>count(timeoffreason)
>give me the same number, for instance in employeeID for

timeoffreason: sick,

- Show quoted text -

Quote:>I get 2 and 2.

>which would give me 100% for the timeoffreason percent if
i was to divide
>the timeoffreason_total by the total records for the
employeeID.

>Can this be done within 1 query?  I could run separate
queries to get the
>totals per employee, but in reality this is a table
containing 20+
>millionrecords which also does a join on another table.
But if anyone has
>some suggestions for the simplified example given above,
I should be able to
>apply that to a larger scale query.

>Thanks in advance,

>Michael

>.


 
 
 

report/summary query design help needed

Post by oj » Thu, 16 Aug 2001 02:31:55


Michael,

Check out RAC solution at

http://www.angelfire.com/ny4/rac

-oj


Quote:> I have a relatively complex query I'm trying to design, and I'm having
> trouble
> getting the basic structure to work.  Here is a simplified example of what
> I'm trying to do:

> I have one table with the following fields:
> employeeID int
> timeoffreason varchar(20)

> A sample of the table would be
> employeeID    timeoffreason
> 1                    sick
> 1                    vacation
> 1                    sick
> 1                    holiday
> 2                    sick
> 2                    vaction
> 3                    sick
> 3                    sick

> I'm trying to design a query that would produce the following from the
above
> table:
> employeeID    timeoffreason    timeoffreason_total  timeoffreasonPercent
> ------------    --------------    -------------------  -------------------
--
> 1                    sick                   2
> 50.0
> 1                    vacation            1
25.0
> 1                    holiday              1
> 25.0
> 2                    sick                   1
> 50.0
> 2                    vacation             1
50.0
> 3                   sick                    2
> 100.00

> Where employeeid would be the employee, timeoffreason would be the reason,
> timeoffreason_total is the distinct total for the reason of the particular
> employee (so it is grouped by the employeeID), and tiemoffreasonPercent is
> the percentage of a reason of the total number of reasons for a particular
> employee.

> The timeoffreasonPercent is what is giving me the trouble.
> I can't get the total amount of records for an employee.
count(employeeID)
> gives me a total for a particular reason  So count(employeeID) and
> count(timeoffreason)
> give me the same number, for instance in employeeID for timeoffreason:
sick,
> I get 2 and 2.

> which would give me 100% for the timeoffreason percent if i was to divide
> the timeoffreason_total by the total records for the employeeID.

> Can this be done within 1 query?  I could run separate queries to get the
> totals per employee, but in reality this is a table containing 20+
> millionrecords which also does a join on another table. But if anyone has
> some suggestions for the simplified example given above, I should be able
to
> apply that to a larger scale query.

> Thanks in advance,

> Michael

 
 
 

report/summary query design help needed

Post by Umachandar Jayachandra » Thu, 16 Aug 2001 04:04:03


    You can do:

SELECT e1.EmployeeID, e1.TimeOffReason, COUNT( * ) AS TimeOffReason_Total,
       COUNT(*) / ( SELECT .01 * COUNT(*)
                      FROM Employees AS e2
                     WHERE e2.EmployeeID = e1.EmployeeID ) AS
TimeOffReasonPercent
  FROM Employees AS e1
 GROUP BY e1.EmployeeID, e1.TimeOffReason

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

report/summary query design help needed

Post by Steve Dassi » Thu, 16 Aug 2001 05:16:18


Greetings,
Use Rac (here upcoming v2.0) S2k server side utility.
Simple and pretty:).

create table #emp(employeeID int,timeoffreason varchar(20))
insert #emp values(1,'sick')
insert #emp values(1,'vacation')
insert #emp values(1,'sick')
insert #emp values(1,'holiday')
insert #emp values(2,'sick')
insert #emp values(2,'vacation')
insert #emp values(3,'sick')
insert #emp values(3,'sick')


employeeid Funct   Totals holiday   sick   vacation
---------- ------- ------    -------   ----- --------
1                  cnt     4        1            2       1
           percent -          25%         50%   25%
2                  cnt     2                      1       1
           percent -                          50%   50%
3                  cnt     2                      2
           percent -                        100%
Totals          cnt      8         1           5       2
           percent -          12.5%    62.5%  25%

HiYa -oj :)

stevie
www.angelfire.com/ny4/rac/

 
 
 

1. (Pdox Win4.5)Need HELP with summary reporting

Calvin, what you need to do is group the records by membership no. or some
other identifying field for each member.  You can then create the summary
field you want.  On a Report just create a group and then create a summary
field via the "Define Field" dialog box.  If you are doing it on a form you
can create your 1->M link and put in a table frame at the detail level.  If
you create a field there (using the same dialog box) you should get the sum
for the current member.  The fields will probable end up being something
like CCount(Payments."Balance") for your "No. of Payments" and
CSum(Payments."Balance") for your "Balance".  It is a little harder to get
the same effect on a form but it can be done.

Cheers...

P.S. If you need more info then feel free to email me...

2. Laserwriter print commands

3. Setting a reports recordset in VB

4. Need help about cross summary query in Oracle8i.

5. starting exam 70- 176

6. help needed - report design

7. Help needed in report design

8. Query Design Help Needed

9. Need help designing query

10. postgres query design help needed

11. SUMMARY: NEEDED - SQL Summary