Solved

SQL Report

Posted on 2014-01-31
5
443 Views
Last Modified: 2014-01-31
I have the following column of data in a table and want a count of each destinct value with a bit of a twist.

Currently I run the following code but need to narrow it down more.  Each field value starts with either Opened, Imported, Moved etc;  followed with additional info.  I need to narrow it to a count of the number of lines that start with each ....   So I would end up with a count for each  Opened, Imported, Moved etc; regardless of what comes after it.

CURRENT CODE

+++++++++++++++++++++++++++++++++++++++++++++++++
SELECT TOP 100 AUDITACTION
       , COUNT(*) AS TypeCount
       , SUM(COUNT(*)) OVER () AS TotalCount
       , (COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () ) *100 AS TypePercent
FROM AUDITTRAIL
WHERE ((USERNAME = 'Sovran' OR USERNAME = 'Sovran1') AND (AUDITTIME > CONVERT(DATETIME, '2014-01-1 00:00:00', 102)))
GROUP BY AUDITACTION
ORDER BY TypePercent Desc
+++++++++++++++++++++++++++++++++++++++++++++++++


DATA SAMPLE
+++++++++++++++++++++++++++++++++++++++++++
Opened HX FORMS tab
Opened ARCHIVE tab
Opened DX REPORTS tab
Accessed Chart (Desktop)
Opened SX REPORTS tab
Opened CONSENTS tab
Opened OUTSIDE MEDICAL RECORDS tab
Opened CORRESPONDENCE tab
Opened PHYSICAL THERAPY tab
Opened SX / DX ORDERS tab
Opened DME tab
Opened HOSPITAL tab
Opened RX tab
Opened LABS tab
Opened WORK COMP tab
Opened WORK RELEASE tab
Imported document into WORK COMP tab
Moved document from ARCHIVE tab to CORRESPONDENCE tab
Moved document from ARCHIVE tab to LABS tab
Moved document from CORRESPONDENCE tab to DME tab
User logged in successfully
Moved document from ARCHIVE tab to HX FORMS tab
Moved document from ARCHIVE tab to OUTSIDE MEDICAL RECORDS tab
Opened CHART NOTES tab
Moved document from ARCHIVE tab to PHYSICAL THERAPY tab
User logged out
Opened PACS tab
Moved document from ARCHIVE tab to RX tab
Moved document from ARCHIVE tab to HOSPITAL tab
Moved document from LABS tab to DX REPORTS tab
Moved document from CORRESPONDENCE tab to OUTSIDE MEDICAL RECORDS tab
Moved document from CORRESPONDENCE tab to WORK COMP tab
Moved document from HOSPITAL tab to SX REPORTS tab
Changed Encounter Date from Archive - Lab/Xray to MRI PIC
Changed Encounter Date from Archive - Questionnaire to Questionnaire
Changed Encounter Date from Questionnaire to Questionnaire
Moved document from CORRESPONDENCE tab to CONSENTS tab
Changed Encounter Date from Archive - Phone Msg to Phone Message
Changed Document Name from 8/29/2011 to 8/7/2003
Moved document from DME tab to OUTSIDE MEDICAL RECORDS tab
Moved document from DME tab to LABS tab
Changed Encounter Date from ARCHIVE - Correspondence to ROI
Changed Encounter Date from Archive-Outside Medical to NorthStar Neurology Office Visit
Changed Encounter Date from Archive - Correspondence to Dr. Tarbet Chart Note
Changed Encounter Date from Archive/Rx to RX
Changed Encounter Date from Archive - Correspondence to BMC LAB REPORT
Moved document from ARCHIVE tab to WORK RELEASE tab
Changed Encounter Date from ARCHIVE - Correspondence to Billing Note
Imported document into OUTSIDE MEDICAL RECORDS tab
Moved document from CORRESPONDENCE tab to PHYSICAL THERAPY tab
Changed Document Name from 8/29/2011 to 7/21/2004
Changed Encounter Date from Archive - PT to PT RX
Moved document from ARCHIVE tab to DME tab
Changed Encounter Date from Archive/Messages to Phone Message
Changed Encounter Date from Archive - Correspondence to Questionnaire
Moved document from ARCHIVE tab to DX REPORTS tab
Changed Encounter Date from Archive/ Work Release to Work Release
Changed Encounter Date from Archive - PT to Evaluation Summary
Changed Encounter Date from ARCHIVE - Correspondence to ORTHOPEDIC & FRACTURE SURGERY
Changed Document Name from 8/29/2011 to 9/9/1900
Changed Document Name from 1/22/2010 to 3/5/2004
Changed Encounter Date from Archive - Correspondence to PROGRESS NOTE
Changed Encounter Date from ARCHIVE - Correspondence to Work Release
Moved document from CORRESPONDENCE tab to WORK RELEASE tab
Changed Encounter Date from pHONE mESSAGE to Phone Message
Changed Encounter Date from Evaluation Summary to Evaluation Summary
Changed Encounter Date from Archive - Rx to RX
Changed Encounter Date from ARCHIVE - Correspondence to Incoming/Outgoing Letter
Moved document from LABS tab to SX REPORTS tab
Moved document from ARCHIVE tab to CONSENTS tab
Moved document from DME tab to CORRESPONDENCE tab
Changed Document Name from 11/17/2009 to 3/11/2008
Changed Encounter Date from Diagnostic Study Request to Diagnostic Study Request
Changed Document Name from 1/22/2010 to 9/9/1900
Moved document from CORRESPONDENCE tab to DX REPORTS tab
Changed Encounter Date from Archive - Hospital Records to Scope Pic
Changed Encounter Date from Archive -Outside Medical to SCMC LAB REPORT
Changed Encounter Date from Archive - Correspondence to BMC CHART NOTE
Moved document from OUTSIDE MEDICAL RECORDS tab to LABS tab
Changed Document Name from 12/10/2009 to 11/20/2003
Changed Document Name from 11/12/2009 to 11/9/2007
Changed Encounter Date from Archive - Correspondence to Attorney Correspondence
Changed Document Name from 5/17/2010 to 11/29/2007
Moved document from DME tab to SX REPORTS tab
Changed Document Name from 11/17/2009 to 8/11/2004
Changed Document Name from 11/17/2009 to 9/9/1900
Changed Document Name from 12/10/2009 to 11/22/2004
Changed Encounter Date from ARCHIVE - Correspondence to FAX
Changed Encounter Date from ARCHIVE-QUESTIONAIRE to Questionnaire
Changed Encounter Date from Archive - Correspondence to ADVANCE DIRECTIVE
Changed Document Name from 12/10/2009 to 5/28/2008
Changed Document Name from 8/29/2011 to 5/2/2005
Changed Encounter Date from Attorney Correspondence to Attorney Correspondence
Changed Document Name from 12/10/2009 to 5/7/2003
Changed Document Name from 6/2/2011 to 12/7/2006
Changed Encounter Date from Archive - Lab/Xray to MR-LT-KNEE
Moved document from ARCHIVE tab to SX / DX ORDERS tab
Changed Document Name from 11/17/2009 to 11/29/2000
Changed Encounter Date from Work Release to Work Release
Changed Encounter Date from Incoming/Outgoing Letter to Incoming/Outgoing Letter



Thanks a million ...

Joel
0
Comment
Question by:jtbrown1111
  • 3
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39825484
check this out

select count(*), AuditAction from AuditTrail Group by AuditAction

Open in new window


you have to add the rest of the where clause as is to the above select statement to reduce the count
0
 

Author Comment

by:jtbrown1111
ID: 39825700
Thanks Surendra,

The code you provided does much the same thing as the code I listed in my original questions.  I'd like to take it one step further and where I have the following three types of values I'd get a count of 3 for " Opened " and not 1 for " Opened Consents tab" and 1 for " Opened Outside Medical Records tab" etc; .....

Thanks ...




Opened CONSENTS tab
Opened OUTSIDE MEDICAL RECORDS tab
Opened CORRESPONDENCE tab
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39825784
Lose the TOP 100 PERCENT it is pointless.

Something like this perhaps:
SELECT  SUBSTRING(AUDITACTION, CHARINDEX(' ', AUDITACTION) - 1, 50)
        COUNT(*) AS TypeCount,
        SUM(COUNT(*)) OVER () AS TotalCount,
        (COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()) * 100 AS TypePercent
FROM    AUDITTRAIL
WHERE   ((USERNAME = 'Sovran'
          OR USERNAME = 'Sovran1'
         )
         AND (AUDITTIME > CONVERT(DATETIME, '2014-01-1 00:00:00', 102))
        )
GROUP BY SUBSTRING(AUDITACTION, CHARINDEX(' ', AUDITACTION) - 1, 50)  --Change length as appropriate
ORDER BY TypePercent DESC

Open in new window

0
 

Author Comment

by:jtbrown1111
ID: 39825816
Anthony,

Almost there .....  I need to strip everything after the first blank and count ....  This is what I'm getting from your code ...

 HX FORMS tab
 ARCHIVE tab
 DX REPORTS tab
 Chart (Desktop)
 SX REPORTS tab
 CONSENTS tab
 OUTSIDE MEDICAL RECORDS tab
 CORRESPONDENCE tab
 PHYSICAL THERAPY tab
 SX / DX ORDERS tab
0
 

Author Comment

by:jtbrown1111
ID: 39825825
Got it .....   Thanks Anthony

SELECT  LEFT(AUDITACTION,CHARINDEX(' ',AUDITACTION)-1),
        COUNT(*) AS TypeCount,
        SUM(COUNT(*)) OVER () AS TotalCount,
        (COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()) * 100 AS TypePercent
FROM    AUDITTRAIL
WHERE   ((USERNAME = 'Sovran'
          OR USERNAME = 'Sovran1'
         )
         AND (AUDITTIME > CONVERT(DATETIME, '2014-01-1 00:00:00', 102))
        )
GROUP BY LEFT(AUDITACTION,CHARINDEX(' ',AUDITACTION)-1)  --Change length as appropriate
ORDER BY TypePercent DESC 

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access recordset not updateable 8 38
SQL Help - 12 37
GRANT, REVOKE, DENY 4 16
Log Backup 2 12
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now