Solved

TSQL syntax to get total for documents type in a open text field

Posted on 2014-04-30
3
165 Views
Last Modified: 2014-05-01
I have this tally I need to build, but I don't have a set field.  In the data below anything that is in [brackets] is considered a document type.  I need to be able to get a total for all document type processed.  

Is there an sql syntax I could use to get this total?  The problem is that there may more document types than depicted here...

results expected would be:
[BATCH COVER SHEET] = 1
[TRANSFER FORMS] = 10
ETC...
DESC
===============================================================
Document[BATCH COVER SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[TRANSFER FORMS],New Pages[2],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[FACE SHEET],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[SIGNATURE AUTH FORM],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[PHYSICIAN ORDERS],New Pages[41],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[HISTORY AND PHYSICAL],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EMERGENCY DEPT],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[PROGRESS NOTE],New Pages[5],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[TRANSFUSION],New Pages[3],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]
Document[EKG],New Pages[1],Batch Prog[QCI], Id[4236894]

Open in new window

0
Comment
Question by:epicazo
3 Comments
 
LVL 9

Expert Comment

by:edlunad
ID: 40032715
something like this should do it...

SELECT        SUBSTRING([desc], CHARINDEX('[', [desc]) + 1, CHARINDEX(']', [desc]) - CHARINDEX('[', [desc]) - 1) AS DocType, COUNT(SUBSTRING([desc], CHARINDEX('[', [desc]) + 1, CHARINDEX(']', [desc]) - CHARINDEX('[', [desc]) - 1)) AS Total

FROM            [YourTable]

GROUP BY SUBSTRING([desc], CHARINDEX('[', [desc]) + 1, CHARINDEX(']', [desc]) - CHARINDEX('[', [desc]) - 1)

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40033486
a variant is to use cross apply so that the calculations are available by their aliases which not only handy but can helps avoid errors if the source data doesn't contain [~~~~~]
SELECT
      doc
    , count(*)
FROM table1
CROSS apply (
             SELECT CHARINDEX('[', [desc]) + 1 as lft, CHARINDEX(']', [desc]) AS rght
            ) ca1
CROSS apply (
             SELECT CASE WHEN rght > lft THEN SUBSTRING([desc],lft,rght-lft) END AS doc
            ) ca2
WHERE doc IS NOT NULL
GROUP BY doc
;

|                  DOC | COLUMN_1 |
|----------------------|----------|
|    BATCH COVER SHEET |        1 |
|                  EKG |       10 |
|       EMERGENCY DEPT |       10 |
|           FACE SHEET |       10 |
| HISTORY AND PHYSICAL |       10 |
|     PHYSICIAN ORDERS |       10 |
|        PROGRESS NOTE |       10 |
|  SIGNATURE AUTH FORM |       10 |
|       TRANSFER FORMS |       10 |
|          TRANSFUSION |       10 |

http://sqlfiddle.com/#!3/0bdeb/7

Open in new window

0
 

Author Closing Comment

by:epicazo
ID: 40034994
perfect!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

15 Experts available now in Live!

Get 1:1 Help Now