I want to get a report of documents that are scanned into the "xxx" scanning queue.

[Jenny_Le1]

Hello,

I am first time user onbase running SQL queries, I need some input on how to put this together.

Part 1

I want to get a report of documents that are scanned into the "xxx" scanning queue. What I need to see is how many documents have been scanned for the previous week, broken down per day and per document type and per user.

so far this is what i got but i'm don't think it what suppose to be for above request

select sl.usernum, aq.batchnum, aq.queuename, sl.logdate, aq.batchname,
id.itemtypename, aq.numberdocuments, aq.totaldocuments, aq.totalpages
from hsi.scanninglog sl, hsi.archivedqueue aq, hsi.useraccount, hsi.doctype id
where aq.queuename = 'xxx'
and sl.logdate >=dateadd(day,datediff(day,0,GetDate())- 7,0)

Part 2

We want another report that tells us by day (for the previous week), how many documents went into the "abc" lifecycle, and how many left the "abc" lifecycle.

and for this i got

select wf.statenumto, wf.entrytime, wf.exittime, wf.usernum
from hsi.wflog wf
join hsi.useraccount ua on ua.usernum = wf.usernum
where statenumto = 108
and wf.entrytime >=DATEADD(day,datediff(day,0,GetDate())- 7,0)

greatly appreciate any help

[Chris_Arpajian · Accepted solution]

Hi Jenny,

Just a quick tip but did you look into using our Report Services module?  I see your organization is licensed for this module and it does come with multiple scanning and Workflow reports "out of the box".  Even if these don't give you 100% of what you wanted, depending on your access you could see the SQL used in these default reports as a basis to create your own SQL Query or a new report in Report Services.  Especially if you choose to export a report template and Import as a new report which you can then modify to your liking.

I hope that may help in some way.

Warm Regards,

Chris

[Jenny_Le1]

Thanks Chris, 

I didn't realize about this, I'll look into this further more.

[Nathan_Kumaras3]

Hi I saw your SQL  query. You are almost there, Please do use the date field in your group by in the Where statement. 

As Chris mentioned earlier in his comment, you can also see the same kind of queries in reporting services module.

[Jenny_Le1]

n/a