Count Distinct using two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count Distinct using two tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-02 : 07:46:34

Carl writes "Let's assume I have two invoice tables; a header and detail table.

1) The header table is keyed by the date of the batch and the number of the batch. For instance, 1st batch of 10/30/06 or the 16th batch of 10/28/06.

2) The detail table is keyed by the two fields above (batch_date and batch_number) and a sequence number. For instance, the 3rd transaction of the 1st batch of 10/30/06.

We hold off the date the detail record was entered/created (entry_date) and the date it was changed (change_date). Our error... we only hold off the day the header record was changed. We do not hold off the date the header record was created.

The details records can be retro-actively added to the header at any time. Furthermore, there is a lag time when the "batches" are actually. For instance, the batch of invoices I received on 10/28/06 might not be entered until Nov 1st. Or... some of the batches that were received on 10/28/06 were entered on the 28th, some entered on the 31st, on then some on the 1st.

I need to know who many batches (batch_date and batch_number) were added by entry date (entry_date) and by userID. The entry_user is held off with the entry_date on the detail. It is not on the header.

I have tried using only the detail tables with distincts and group by... I have tried joining the tables... I even tried counting the distinct record in a sub-select and then "summing" the count. I cannot figure it out.

Any help you can give would be great.

Carl"

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 07:58:48

Something like this?


SELECT entry_date, userID, [MyCount] = SUM(MyCount)
FROM
(
    SELECT entry_date, userID, batch_date, batch_number, [MyCount] = COUNT(*)
    FROM MyTable
    GROUP BY entry_date, userID, batch_date, batch_number
) AS X
GROUP BY entry_date, userID

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 08:02:10

select q.userid, q.entry_date, count(*)
from (select distinct entry_date, userid, batchdate, batch_number from detailTable) q
group by q.userid, q.entry_date
order by q.userid, q.entry_date

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources