asked on Some tsql
Hello,
I have a table called Documents that holds a number of documents. There are couple of columns that are interesting to me.
The first is doc_Datetime that holds the datetime that the document was added.
The second is the doc_Exported (bit) that tells me the document has been exported.
I need help with a query that will show the total number of documents recieved each day, and the number of them that have been exported.
The result set should even include days when no records have been received.
Something like:
Date Documents received Documents exported.
2012-03-18 123 80
2012-03-19 10 9
2012-03-20 0 0
2012-03-21 12 11
Thanks!
I have a table called Documents that holds a number of documents. There are couple of columns that are interesting to me.
The first is doc_Datetime that holds the datetime that the document was added.
The second is the doc_Exported (bit) that tells me the document has been exported.
I need help with a query that will show the total number of documents recieved each day, and the number of them that have been exported.
The result set should even include days when no records have been received.
Something like:
Date Documents received Documents exported.
2012-03-18 123 80
2012-03-19 10 9
2012-03-20 0 0
2012-03-21 12 11
Thanks!
Microsoft SQL ServerSQL
Last Comment
dqmq
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
select date, count(*), sum(exported) as exported
from
(select convert (varchar(10), doc_datetime ,101) as Date, case when doc_exported = 1 then 1 else 0 end as exported from YOURTABLE) as tmp
group by date
order by date
Returning rows for missing dates is tricky, I'll need to get back to you about that. Do you have a table of dates, perchance?