SQL SUM by Quarter 
Author Message
 SQL SUM by Quarter

Hi All,

I have two Access 95 tables for tracking Vacation Time: Used and Allocated.

Their fields are identical:

Emp_ID String * 4
Date   Date
Vac        Integer
Hol        Integer
Per        Integer
Oth        Integer

With each table, I need to populate an array with the SQL SUM values for an

employee BY QUARTER.  Thus, I want to end up with two 4x4 recordsets (or a
three dimensional array), the first is time Used and the second is time
Allocated.

Let's say my search parameter is: Emp_ID = "1500".  I need to get back the
SUM of employee
1500's 1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr Vac, Hol, Per, Other field
vales for each
of the Used and Allocated tables.  From this I'll populate a grid and
calculate the
Time Off remaining.

Can I do this in one or a few SQLs, or do I need to hit the db 32 times
(4x4x2)
per employee?

Thanks in advance, Dan
--
**************************************************************************
Daniel Poor                                       email:

                                                       phone: 212-735-3000
New York City, NY USA                       fax: 212-735-2001
**************************************************************************



Mon, 28 Jun 1999 03:00:00 GMT  
 SQL SUM by Quarter

Look at the DATEPART function using "q" for quarter.

Charles Carlin

Quote:

>Hi All,

>I have two Access 95 tables for tracking Vacation Time: Used and Allocated.

>Their fields are identical:

>Emp_ID String * 4
>Date   Date
>Vac    Integer
>Hol    Integer
>Per    Integer
>Oth    Integer

>With each table, I need to populate an array with the SQL SUM values for an

>employee BY QUARTER.  Thus, I want to end up with two 4x4 recordsets (or a
>three dimensional array), the first is time Used and the second is time
>Allocated.

>Let's say my search parameter is: Emp_ID = "1500".  I need to get back the
>SUM of employee
>1500's 1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr Vac, Hol, Per, Other field
>vales for each
>of the Used and Allocated tables.  From this I'll populate a grid and
>calculate the
>Time Off remaining.

>Can I do this in one or a few SQLs, or do I need to hit the db 32 times
>(4x4x2)
>per employee?

>Thanks in advance, Dan
>--
>**************************************************************************
>Daniel Poor                                       email:

>                                                       phone: 212-735-3000
>New York City, NY USA                       fax: 212-735-2001
>**************************************************************************



Mon, 28 Jun 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. SQL Grouping by Date(Quarter, Month, etc...)

2. AM I missing something - Sum and Cumulative Sum

3. Report showing BOTH Aggregate Sum of Display records and Total Sum by Group

4. How to sum a single fiscal period for monthly expenses and also sum ytd expenses

5. table join with a sum works in sql

6. SQL SELECT SUM

7. Using IF and SUM statements with SQL?

8. Using Sum with SQL and DBGrid

9. Using Sum with SQL and DBGrid, and mvscrt*.dll problem

10. sql function SUM and ADO??

11. SQL Sum Function (Index)

12. SQL Group by (sum)

 

 
Powered by phpBB® Forum Software