Solved

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

Posted on 2014-04-30
3
171 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

735 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