Find and count Incomplete records in a table

Question

Find and count Incomplete records in a table

eyejay

Mr or Mrs. 500

Points: 542
More actions
December 7, 2017 at 10:09 am

#151139

Hi, I have a problem which is stretching my basic T-SQL coding capabilities and hope you might be able to help. I have a table of records, some are complete and some are not and as part of an audit I'm trying to set up I need to determine if a record is incomplete (ie there is at least one blank field) and then for each User who enters records produce an output ratio of complete to incomplete records.
I have included some very simple code to create a demo table and the output I'm hoping to achieve.
CREATE TABLE #TempTable (
"User" varchar(100),
"Date" datetime,
"Event1" varchar(100),
"Event2" varchar(100),
"Event3" varchar(100) );
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('001', '01/01/2017', 'ABC', 'DEF', 'GHJ');
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('002', '01/01/2017', 'ABC', '', 'GHJ');
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('003', '01/01/2017', '', 'DEF', '');
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('001', '01/02/2017', 'ABC', 'DEF', 'GHJ');
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('002', '01/02/2017', 'ABC', 'DEF', 'GHJ');
INSERT INTO #TempTable ("User", "Date", "Event1", "Event2", "Event3") VALUES ('003', '01/02/2017', '', '', 'GHJ')
Which produces the following output from SELECT * FROM #TempTable
User Date      Event1 Event2 Event3
001 2017-01-01 ABC    DEF    GHJ
002 2017-01-01 ABC           GHJ
003 2017-01-01        DEF
001 2017-01-02 ABC    DEF    GHJ
002 2017-01-02 ABC    DEF    GHJ
003 2017-01-02               GHJ
What I'd like to end up with if you analyse this between the dates 2017-01-01 TO 2017-01-02 is:

User

Total Entries

Complete Entries

001

2

2

002

2

1

003

2

0

I don't know how to start to analyse the records in the table to look for the incomplete records and then produce the query that generates the output above. I appreciate any help in getting me started with this, thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

How's this:
WITH myCTE (u, entries, complete) AS ( SELECT u = , Entries = 1, Complete = CASE WHEN event1 <> '' AND event2 <> '' AND event3 <> '' THEN 1 ELSE 0 END FROM #TempTable ) SELECT u, TotalEntries = sum(entries) , CompleteEntries = sum(complete) FROM myCTE GROUP BY u

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 2

Steve,

Thank you, thank you, thank you!
That's just perfect and absolutely what I needed and so quick.

🙂

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736478

December 7, 2017 at 12:52 pm

#1971267

You are welcome.

Please mark that as the answer

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

eyejay - Thursday, December 7, 2017 12:43 PM
Steve,
Thank you, thank you, thank you!
That's just perfect and absolutely what I needed and so quick.
🙂

"Quick" is what happens when someone takes the time to post readily consumable data that actually works and it actually matches the problem being described. Well done on your part!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Shifting gears and perhaps I'm over-anticipating/over-engineering , but it looks a bit like someone is trying to do a workload analysis. If that's the case, let's look into a bit more detail and expose the "Big Picture" in the process because, just looking at the number of entries completed paints a dismal picture as to the overall status.

I don't know the weight of each Event but, if they're all the same, then Events completed show that 72% of the overall work has been completed whereas the Entries completed depict that only 50% of the work has been done. It also does a bit of an analysis as to which event(s) may be more troublesome to complete than the rest.

Here's the code...

WITH CTE AS ( SELECT IsTotalRow = GROUPING("User") ,"User" ,TotalEntries = COUNT(*) ,EntriesComplete = SUM(IIF(Event1='' OR Event2='' OR Event3='',0,1)) ,TotalEvents = COUNT(*)*3 ,E1Complete = SUM(IIF(Event1='',0,1)) ,E2Complete = SUM(IIF(Event2='',0,1)) ,E3Complete = SUM(IIF(Event3='',0,1)) FROM #TempTable GROUP BY "User" WITH ROLLUP ) SELECT "User" = IIF(IsTotalRow=0,"User",'Total') ,TotalEntries ,EntriesComplete ,EntriesToGo = TotalEntries-EntriesComplete ,PercentEntriesComplete = CONVERT(INT,EntriesComplete*100/TotalEntries) ,E1Complete ,E2Complete ,E3Complete ,TotalEvents ,EventsComplete = (E1Complete+E2Complete+E3Complete) ,EventsToGo = TotalEvents-(E1Complete+E2Complete+E3Complete) ,PercentEventsComplete = CONVERT(INT,(E1Complete+E2Complete+E3Complete)*100/TotalEvents) FROM CTE ORDER BY IsTotalRow,"User" ;

Here are the results using the provided data...

User TotalEntries EntriesComplete EntriesToGo PercentEntriesComplete E1Complete E2Complete E3Complete TotalEvents EventsComplete EventsToGo PercentEventsComplete ----- ------------ --------------- ----------- ---------------------- ---------- ---------- ---------- ----------- -------------- ---------- --------------------- 001 2 2 0 100 2 2 2 6 6 0 100 002 2 1 1 50 2 1 2 6 5 1 83 003 2 0 2 0 0 1 1 6 2 4 33 Total 6 3 3 50 4 4 5 18 13 5 72

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

p.s. Here's hoping that you really are using SQL Server 2014. IIF came out with 2012 and it resolves to CASE operators behind the scenes but it sure does simplify code. In case you've never used it, it syntactically boils down to...

IIF(SomeConditionIsTrue, ThenThis, ElseThis)

The equivalent CASE operator would be...

CASE WHEN SomeConditionIsTrue THEN This ELSE This END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

Nice enhancement, Jeff. I knew someone might come up with a more detailed code solution

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Steve Jones - SSC Editor - Tuesday, December 12, 2017 2:09 AM
Nice enhancement, Jeff. I knew someone might come up with a more detailed code solution

Thanks, Steve. You kicked it off with your code and solved the OP's problem. I just tweaked it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 9

Hi Jeff,

Many thanks for "shifting gears". Your tweaking of Steve's original solution is very useful. As you correctly spotted I am actually trying to work on a workload analysis but as my SQL coding is not that advanced I just needed help getting past my main problem and hoped I might figure a bit more out myself, so your extra code will be very helpful. Sadly, I'm still on SQL2005 :crying: but my IT department keep promising to migrate me to SQL2014 so I'll have to use the CASE statements for now but I agree the IIF does look much cleaner.

Thanks again for picking up my thread and offering a very helpful solution.

Ian

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

eyejay - Tuesday, December 12, 2017 10:41 AM
Hi Jeff,
Many thanks for "shifting gears". Your tweaking of Steve's original solution is very useful. As you correctly spotted I am actually trying to work on a workload analysis but as my SQL coding is not that advanced I just needed help getting past my main problem and hoped I might figure a bit more out myself, so your extra code will be very helpful. Sadly, I'm still on SQL2005 :crying: but my IT department keep promising to migrate me to SQL2014 so I'll have to use the CASE statements for now but I agree the IIF does look much cleaner.
Thanks again for picking up my thread and offering a very helpful solution.
Ian

My pleasure, Ian. Thank you very much for the feedback.

Now I have to figure out why I thought you were working in 2014. I could have sworn this was in the 2014 forums 2 days ago.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)