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
 General SQL Server Forums
 New to SQL Server Programming
 Count query against a column over the last 6 month

Author  Topic 

Apgeiss
Starting Member

2 Posts

Posted - 2015-03-03 : 21:33:40
I have a few million rows of a data in a table that store indexing information for scanned customer documents. Fields like last name, first name, document description (descript), document type and scan date (scandate).

I want to be able to query the document description column where it'll show how many times each document description was scanned/used over the course of the last 6 months or year. Can anyone help with this query?

jleitao
Posting Yak Master

100 Posts

Posted - 2015-03-04 : 08:04:58
you need a "group by" by document, a "count" and a "where" clause by date.

Can you post your query?

------------------------
PS - Sorry my bad english
Go to Top of Page

Apgeiss
Starting Member

2 Posts

Posted - 2015-03-04 : 09:27:38
This is the best I got but it doesn't work...

SELECT SCANDATE, facility, acctno, idxuser, mrn, edoctype, descript, slevel, idxdate, comdate, count(descript)
FROM idm.bsemr
GROUP by descript
where scandate > TO_DATE('06-01-2014', 'MM-DD-YYYY')
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-04 : 14:12:36
You are using Oracle, and with SQL Server we write

SELECT SCANDATE, facility, acctno, idxuser, mrn, edoctype, descript, slevel, idxdate, comdate, count(descript) AS cnt
FROM idm.bsemr
where scandate > '20140601'
GROUP by SCANDATE, facility, acctno, idxuser, mrn, edoctype, descript, slevel, idxdate, comdate



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -