t-sql 2012 calculation

Question

t-sql 2012 calculation

wendy elizabeth

SSCoach

Points: 16915
More actions
April 6, 2017 at 12:37 pm

#329118

In t-sql 2012, I would like to complete a subtraction calculation to show number of input records minus the number of records where a column called [State ID] is null.
I would like the calculation to display own the output window and/or the messages window when executing SSIS 2012 manager. I would like a message to say what the
count is like a header column and I would like the actual count.
Here is an sql example of what I am looking for:
declare @custcnt int = (SELECT count(distinct([State ID])) FROM CImport) - (SELECT count(*) from CImport where ([State ID] = (' ') OR [State ID] is null))
Can you show me the sql on how to accomplish my goal?

Viewing 8 posts - 1 through 7 (of 7 total)

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

pietlinden SSC Guru Points: 63434 More actions · Answer 1

I would like the calculation to display own the output window and/or the messages window when executing SSIS 2012 manager.

False. This is an SSIS question.

sgmunson SSC Guru Points: 110639 More actions · Answer 2

wendy elizabeth - Thursday, April 6, 2017 12:37 PM
In t-sql 2012, I would like to complete a subtraction calculation to show number of input records minus the number of records where a column called [State ID] is null.
I would like the calculation to display own the output window and/or the messages window when executing SSIS 2012 manager. I would like a message to say what the
count is like a header column and I would like the actual count.
Here is an sql example of what I am looking for:
declare @custcnt int = (SELECT count(distinct([State ID])) FROM CImport) - (SELECT count(*) from CImport where ([State ID] = (' ') OR [State ID] is null))
Can you show me the sql on how to accomplish my goal?

Maybe the question is better stated as "What's wrong with the query you've already provided?" Does is not give you the correct results? That query also counts a field with a single space the same way as it does a NULL value, yet it would NOT treat an empty string the same, despite that having the same net effect in terms of the nature of the data. Maybe the criteria for the query needs re-examining?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

When I run the query, I do not see any results displayed

pietlinden SSC Guru Points: 63434 More actions · Answer 4

Got some table definitions and some sample data and some expected results? You know, same old same old.

Phil Parkin SSC Guru Points: 246711 More actions · Answer 5

wendy elizabeth - Thursday, April 6, 2017 3:06 PM
When I run the query, I do not see any results displayed

And nor should you. Your query does not return any results.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Joe Torre SSChampion Points: 10248 More actions · Answer 6

SELECT Count(Distinct [State ID]) , CASE WHEN [State ID] IS NULL OR [State ID] = ' ' THEN 1 ELSE 0 END InvalidStateId FROM CImport;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

What exactly are you trying to count? It's not clear - you specify " number of input records " but then you use DISTINCT in your code. Perhaps this will help you to better define your requirement:

-- This is just a little sample data to play with ;WITH CImport AS (SELECT * FROM (VALUES ( 'NY'),('CA'),('FL'),('FL'),('NY'),('NY'),(' '),('NY'), (NULL),(NULL),(NULL),(' '),('IL'),('IL')) d ([State ID]))

SELECT
[State ID],
[Rowcount] = COUNT(*)
FROM CImport
GROUP BY [State ID]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden