SQL Count Help?

[saroren]

Front End - Delphi
Backend - Progress Database
ODBC - SQL92


I need to find no of items shipped each day.

So i am using

select count(*), tr_effdate
from tr_hist, where tr_type = "ISS-SO"
and tr_effdate >= 06/01/06
and tr_effdate <= 06/30/06
group by tr_effdate.

This gives me
Count Date
5 06/01/06
5 06/02/06
5 06/03/06
5 06/04/06
.
.

But i need a result like below

5 06/01/06
10 06/02/06
15 06/02/06

(the count should keep on adding with the previous day counts)


Tried the below syntax in Progress Explorer, but i get syntax error.

SELECT t1.tr_effdate, COUNT(t1.tr_effdate) AS DayCount, (SELECT COUNT(*) FROM tr_hist t2
WHERE t2.tr_effdate BETWEEN '1 Jun 2006' AND t1.tr_effdate) AS MonthToDate
FROM tr_hist t1
GROUP BY t1.tr_effdate
ORDER BY t1.tr_effdate


it works for

SELECT t1.tr_effdate, COUNT(t1.tr_effdate) AS DayCount FROM pub.tr_hist t1
GROUP BY t1.tr_effdate
ORDER BY t1.tr_effdate

but if i add the (SELECT COUNT(*) FROM pub.tr_hist t2
WHERE t2.tr_effdate BETWEEN '1 Jun 2006' AND t1.tr_effdate) AS MonthToDate
it give syntax error.


Please help me with the query?

Thanks

 

[lklingaman]

OK, this is an slow, ugly solution, but it will solve your problem.

If I am extrapolating from the results set show correctly, you are looking for a total to be displayed by day of all sales between the beginning of the date range and the current date of the date range.



SELECT MAIN1.MC1, SUM(MAIN1.MC3)
FROM (SELECT T1.C1 AS MC1, T2.C1 AS MC2, SUM(T2.C2)
AS MC3
FROM (SELECT E1.tr_effdate AS C1
FROM tr_hist E1
WHERE E1.tr_effdate BETWEEN '6/1/2006' AND
'06/30/06' AND tr_type = "ISS-SO"
GROUP BY E1.tr_effdate) AS T1,
(SELECT E2.tr_effdate AS C1, COUNT(*)
AS C2
FROM tr_hist AS E2
WHERE E2.tr_effdate BETWEEN '6/1/2006' AND
'06/30/06' AND tr_type = "ISS-SO"
GROUP BY E2.tr_effdate) AS T2
GROUP BY T1.C1, T2.C1
HAVING T2.C1 <= T1.C1) AS MAIN1
GROUP BY MAIN1.MC1
ORDER BY MAIN1.MC1

I have not been able to reference between two tables in the same select statement through the Progress ODBC driver.

imho:a real limitation.


If you have any questions on the working please post them here and I can explain the different parts and how it works.

Larry Klingaman