Solved

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 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