hi- I am trying an SQL but could

[justsql]

hi-

I am trying an SQL but couldn't achieve with a query, any help will be highly appreciated.

There are two tables say Table A and B
A(col1,
Qual,
ID,
ref)

B(col1,(FK to Col1 on A)
type of transaction,
Date of transaction,
Status of transaction,
date of acknowledgement)

Now I have to get the result with a query in this format

qual ID Type Date ToTal Total
of trans of trans Sent Acknowledged

Any help will be highly appreciated.

Thanks

 

[rac2]

SELECT A.qual,
       A.ID,
       B.TypeTrans,
       B.DateTrans,
       COUNT(B.*) AS "Total Sent",
       COUNT(B.DateAcknowledged) AS "Total Acknowldgd"

FROM A
JOIN B ON A.Col1 = B.Col1
GROUP BY A.qual,
       A.ID,
       B.Type,
       B.DateTrans

 

[justsql]

Thanks for the reply, I forgot to add one more condition. The acknowledged count should be only for the documents that are acknowledgemed say status is "01"...

 

[rac2]

Replace JOIN B ON A.Col1 = B.Col1 with

JOIN B ON A.Col1 = B.Col1 AND B.StatusTrans = '01'

 

[justsql]

Thanks rac2 again..

But that will return all the rows that are acknowledged. I need the count for all under documents sent and count of those documents acknowledged under documents acknowledged.

 

[rac2]

Assuming that date of acknowledgement is null for documents that are sent but not acknowledged, the first query will give you the counts you need.

I don't know what a status code of 01 means and I don't know what your data looks like. I hope my suggestions help you solve your problem.

Good luck.

 

[justsql]

Thanks rac2...