Assistance with 'group by' to include a date field 1

[Zahier]

I have a situation where my scanners are scanning the same document over and over, and because they get paid per document it is possible they may be doing so purposefully.

I am trying to create a simple report to count the amount of times a document is scanned by a scanner (i.e. group by "createdby")within a specified period, but to also including the date-time the image was created. My issue is that when I include the date-time in the group-by clause it returns no rows. I understand why it does this, but cannot fathom a way to include the date-time of the document in my report. "createdon" is a DATE field which cannot be grouped with the "createdby" and "docid" to give a result set, but I require the createdon value.

Select count(*) as COUNT,
DOCID as DOCUMENT_NUMBER, createdby as username, createdon
from SCOTT.ALL_DOCUMENTS
where SCOTT.ALL_DOCUMENTS.DOC_STATUS = 'SCANNED'
and CREATEDBY like 'domain1\drs%'
and DELETED =0
and (CREATEDON between to_dateP_ParamDate1,'DD-MM-YYYY HH24:MI')
and to_dateP_ParamDate2,'DD-MM-YYYY HH24:MI') )
group By createdby, docid, createdon
having (count(*)>=2)
order by DOCID asc


Please, any assistance would be appreciated.

 

[taupirho]

Try this:-


select DOCID as DOCUMENT_NUMBER, createdby as username, createdon
from scott.all_documents
where docid in
(
select docid from
(
Select count(*) as COUNT,
DOCID as DOCUMENT_NUMBER
from SCOTT.ALL_DOCUMENTS
where SCOTT.ALL_DOCUMENTS.DOC_STATUS = 'SCANNED'
and CREATEDBY like 'domain1\drs%'
and DELETED =0
and (CREATEDON between to_dateP_ParamDate1,'DD-MM-YYYY HH24:MI')
and to_dateP_ParamDate2,'DD-MM-YYYY HH24:MI') )
group By createdby, docid
having (count(*)>=2)
)
)
order by DOCID asc



In order to understand recursion, you must first understand recursion.

 

[taupirho]

Oops, I think maybe this:-

Select count(*) as COUNT,
DOCID as DOCUMENT_NUMBER

should have read

Select count(*) as COUNT,
DOCID as DOCUMENT_NUMBER,createdby




In order to understand recursion, you must first understand recursion.

 

[Zahier]

Nested queries...brilliant taupirho! I will have the Supervisors verify the resultset, but many thanks for the assistance.

 

[SantaMufasa]

Nested queries...brilliant taupirho!...many thanks for the assistance.

Sounds like a

is in order. To award a

to Taupirho, click on the link, Thank taupirho for this valuable post.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zahier]

yes, of course. The resultset proved correct. Thanks again...have a star.