Count query against a column over the last 6 month

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

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')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources