Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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