how to sum a column depending of another colum into different vars?

Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example

DECLARE @.Initial decimal(18,2), @.incomings decimal(18,2), @.outgoings decimal(18,2)

SELECT
@.initial = CASE WHEN type = 1 THEN SUM(amount) END,
@.incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@.outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

What I am trying to do is to sum all of the incomings transactions into @.incomings, all of the outgoing transactions into @.outgoings and the initial transaction into @.initial where
The incoming transactions is type 2,
outgoing transactions is type 3

Thanks for the help

@.puy

Hi, this should work for you:

SELECT
@.initial = SUM(CASE WHEN type = 1 THEN amount ELSE 0 END),
@.incomings = SUM(CASE WHEN type = 2 THEN amount ELSE 0 END),
@.outgoings = SUM(CASE WHEN type = 3 THEN amount ELSE 0 END),
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi, sorry for the late answer...

Your query just returns almost the same as mine, it just return the value for the initial with the difference that mine return null the other, yours return 0.

Here it is the query I ran and the results

declare @.initial decimal(18,2), @.incomings decimal(18,2), @.outgoings decimal(18,2)

SELECT
@.initial = SUM(CASE WHEN tipo = 1 THEN monto ELSE 0 END),
@.incomings = SUM(CASE WHEN tipo = 2 THEN monto ELSE 0 END),
@.outgoings = SUM(CASE WHEN tipo = 3 THEN monto ELSE 0 END)
FROM Caja
WHERE fecha = '05/14/2006' AND estacion = 'apuyinc'
GROUP BY tipo, monto
select @.initial, @.incomings, @.outgoings

select tipo,monto,fecha,estacion from caja where fecha = '05-14-2006' and estacion = 'apuyinc'

initial incomings outgoings
-- -- --
15000.00 .00 .00

(1 row(s) affected)

tipo monto fecha estacion
- --
1 15000.00 2006-05-14 00:00:00 APUYINC
2 3480.00 2006-05-14 00:00:00 APUYINC
2 3140.00 2006-05-14 00:00:00 APUYINC
2 11010.00 2006-05-14 00:00:00 APUYINC
7 -5000.00 2006-05-14 00:00:00 APUYINC

(5 row(s) affected)

As you can see, it should retrieve 17630 when tipo = 2...

Any other suggestion will be highly appreciate...

thanks,

@.puyinc

P.S. Sorry for changing the language, these were the original column values ;)

|||Yes, we messed up with the variables / or the grouping, if you just want to have the sums in common, not grouped by any column this should be the following:

DROP TABLE #someTable

CREATE TABLE #SomeTable

(

tipo int,

monto int,

fecha datetime,

estacion varchar(100)

)

SELECT * FROM #SomeTable

INSERT INTO #SomeTable

SELECT 1,15000,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,3480,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,3140,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,11010,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 7,-5000,'2006-05-14 00:00:00','APUYINC'

SELECT

@.initial = SUM(CASE WHEN tipo = 1 THEN monto ELSE 0 END),
@.incomings = SUM(CASE WHEN tipo = 2 THEN monto ELSE 0 END),
@.outgoings = SUM(CASE WHEN tipo = 3 THEN monto ELSE 0 END)

FROM #SomeTable

WHERE fecha = '05/14/2006' AND estacion = 'apuyinc'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you for the help, it works now.

I added the "group by" clause in some moment that gave me a syntax error and force me to add it. But now it looks like it is not necesary.

How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at HRM_tblParty: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie