Using a date as primary key with an aggregation table Sybase ASE

Question

I'm currently working on a Sybase ASE rdms and I have the following table (JOB_AUDIT) filled with sample dataset :

IDJOB	BATCH	M_DATE	START_TIME	ENDTIME
1	A	12-25-2012 00:00:00	40100	40110
2	A	12-25-2012 00:00:00	40111	40118
3	B	12-25-2012 00:00:00	40118	40129
4	A	12-27-2012 00:00:00	40100	40112
5	A	12-27-2012 00:00:00	40113	40118
6	C	12-27-2012 00:00:00	40120	40134

....

What I want to do is at first to calculate the total execution time for each batch to run on a daily basis, then I want to calculate the pencentage each bach took on the daily execution.

so I created this query :

SELECT
  T1.M_DATE as Batch_DATE,
  T1.BATCH as Batch,
  count(T1.IDJOB) as Nb_jobs,
  sum(T1.END_TIME-T1.START_TIME) as Exec_Duration,
  sum(T1.END_TIME-T1.START_TIME)/T2.total as pct
FROM
  JOB_AUDIT T1
inner join 
  (select M_DATE,sum(END_TIME-START_TIME) as total from AUDIT_REP group by M_DATE) T2
ON
   T1.M_DATE = T2.M_DATE
GROUP BY  T1.M_DATE,T1.M_BATCH;

the T2 table should give me an output like this :

M_DATE	total
12-25-2012 00:00:00	28
12-27-2012 00:00:00	31

then the final table should look like :

Batch_DATE	Batch	Nb_jobs	Exec_Duration	pct
12-25-2012 00:00:00	A	2	17	0.607
12-25-2012 00:00:00	B	1	11	0.393
12-27-2012 00:00:00	A	2	17	0.548
12-27-2012 00:00:00	C	1	14	0.452

but it seems like the the join isn't working as I have the following result :

Batch_DATE	Batch	Nb_jobs	Exec_Duration	pct
12-25-2012 00:00:00	A	2	17	0.607 (17/28)
12-25-2012 00:00:00	A	2	17	0.548 (17/31) not correct
12-25-2012 00:00:00	B	1	11	0.393 (11/28)
12-25-2012 00:00:00	B	1	11	0.355 (11/31) not correct
12-27-2012 00:00:00	A	2	17	0.607 (17/28) not correct
12-27-2012 00:00:00	A	2	17	0.548 (17/31)
12-27-2012 00:00:00	C	1	14	0.500 (14/28) not correct
12-27-2012 00:00:00	C	1	14	0.452 (14/31)

So, I was wondering why I couldn't use a date a key, I'm sure there will always be a unique record per date in my T2 table as I grouped by date.

I thought the problem was that the record was a datetime to I even replaced all M_DATE fields by convert(date,M_DATE) and it changed nothing.

Note : I'm working with SYBASE ASE so I can't use window functions as it is not supported by this RDMS

Answer 1

Here is the problem --

sum(t1.endtime-t1.start_time)/t2.total 

-- I know you want to make the aggregation's result to divided with t2.total -- But the syntax here will take each matched line in t1 to sum first then divided with t2.total. So if you write it to --

sum(convert(float,(t1.endtime-t1.start_time))/t2.total)

-- Then it will give you the result you want -- Here's my test--

1> select * from JOB_AUDIT
2> go
 id          batch m_date                          start_time  endtime
 ----------- ----- ------------------------------- ----------- -----------
           1 A                 Dec 25 2012 12:00AM       40100       40110
           2 A                 Dec 25 2012 12:00AM       40111       40118
           3 B                 Dec 25 2012 12:00AM       40118       40129
           4 A                 Dec 27 2012 12:00AM       40100       40112
           5 A                 Dec 27 2012 12:00AM       40113       40118
           6 C                 Dec 27 2012 12:00AM       40120       40134

(6 rows affected)
1> select * from T2
2> go
 m_date                          total
 ------------------------------- -----------
             Dec 25 2012 12:00AM          28
             Dec 27 2012 12:00AM          31

(2 rows affected)
1> vi
[sh:vi /tmp/ctisql_SCNCSP]
1> select t1.m_date,t1.batch,count(id) nb_jobs,sum(t1.endtime-t1.start_time) as Exec_Duration,sum(convert(float,(t1.endtime-t1.start_time))/t2.total) pct
2> from JOB_AUDIT t1, T2 t2
3> where t1.m_date=t2.m_date
4> group by t1.m_date,t1.batch
5> order by 1,2
6> go
 m_date                          batch nb_jobs     Exec_Duration pct
 ------------------------------- ----- ----------- ------------- ---------------------------
             Dec 25 2012 12:00AM A               2            17                    0.607143
             Dec 25 2012 12:00AM B               1            11                    0.392857
             Dec 27 2012 12:00AM A               2            17                    0.548387
             Dec 27 2012 12:00AM C               1            14                    0.451613

(4 rows affected)

Hope it help