SQL Server 2012 :: Using COALESCE And Also Trying To Multiply And Divide

Sep 3, 2014

Need to multiply the following by 12 then divide this by 52.

,COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0) as 'weekly rent'Everywhere

I try and put the *12/52, I get errors.

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Divide Amount Between Months

Mar 18, 2015

Background:

Void Start Date: When a property becomes empty or vacant

Let Date: When the property is filled in again

I have the sample data below and like to show the void loss per month basis as below:

1) Allocate the amount from voidloss column between months based on voiddays:

for example for propcode 3698 the amount 13,612.56 needs to be divided between September and October based on the VoidDays. i.e of the 39 voiddays, 25 where in September and 14 in October hence 8726 will be allocated to September and 4886.56 to October

2) After allocating the amount sum the amount by controlgroup and total the voiddays per month. It will be great if we can divide the voiddays between months and sum them by controlgroup as well.

So in the end result we should have

ControlGroup, Month, Year, VoidLoss, VoidsdayinMonth
106 September 2014 8726 25
106 October 2014 4886.56 14
106 December 2014 2940 7

Declare @voidloss Table
( History_IND INT
,PropCode VARCHAR(10)
,VoidCategory VARCHAR(10)
,ControlGroup VARCHAR(10)
,VoidStartDate date

[Code] .....

View 3 Replies View Related

SQL 2012 :: Coalesce WHERE Clause With XML Data

Oct 9, 2014

Looking to coalesce a WHERE clause with XML data. I cannot supply test data.

If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.

set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src
where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')

View 1 Replies View Related

SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements

May 27, 2014

I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.

Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().

SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

View 9 Replies View Related

SQL Server 2014 :: Overriding Data Precedence In COALESCE Expression

Aug 5, 2015

I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?

And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.

DECLARE @x BIT;
DECLARE @default BIT = 1;
DECLARE @something sql_variant;

SELECT @something = COALESCE(@x, 1);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

SELECT @something = COALESCE(@x, @default);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

SELECT @something = COALESCE(@x, CAST(1 AS BIT));
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

View 4 Replies View Related

Divide By Zero Error SQL SERVER

Jul 31, 2006

Hi
Is there a way to prevent this error?
I'm getting it from query line:
10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)),1) 'Atingimento Semestre'
Thanks a lot

View 2 Replies View Related

SQL Server 2008 :: How To Divide Value In Row 1 By The Value In Row 2 In Same Column

Mar 4, 2015

I try to take the value from row 1 divided by the value from row 2 but it is not working, here is the following:

Here is my table called Trans

ID Period Sales Profit
1 Current 20 5
2 Previous 40 20

I want to take 20 divided by 40(20/40), 5 divided by 20(5/20)

Here is the result I want:

ID Period Sales Sales_Per Profit Profit_Per
1 Current 20 50% 5 25%
2 Previous 40 50% 20 25%

Here is my query:

Select
(t1.Sales/NULLIF(t2.Sales,0) * 100) as Sales_Per
From Trans t1
INNER JOIN Trans t2
on t1.Id = t2.ID - 1

View 2 Replies View Related

Sum() Multiply()

Mar 15, 2007

Hi all. I have 2 tables to join with.

table1 - leavecredits
empdcno type dateassigned earnedDay availedDay
222 SL 3/4/2007 8.0 2.0
222 PL 4/4/2007 8.0 2.0
222 ML 5/4/2007 8.0 0.0
333 PL 6/4/2007 8.0 3.0
444 SL 4/6/2007 8.0 1.0

table2 - leaveledger
empdcno leavedate hrsleave hrswork type
222 6/4/2007 2.0 8.0 SL
222 6/5/2007 8.0 8.0 SL
222 6/6/2007 8.0 8.0 SL
222 6/7/2007 8.0 8.0 SL
222 6/8/2007 8.0 8.0 Sl
222 6/9/2007 8.0 8.0 SL
333 6/7/2007 8.0 8.0 PL

I am creating a report for leaveledger of all employees.

Expected Result:

empdcno type dateassigned earnedDay earnedHrs availedDay availedHrs balDay balHours

222 SL 3/4/2007 8.0 42.0 2.0 2.0 16.0 6.0 48.0
333 PL 6/4/2007 8.0 8.0 1.0 3.0 24.0 5.0 40.0

formula:
earnedHrs = in leaveleadger table 8 x 5 + 2 = 42.0
availedHrs = availedDay x hrswork
balday = earnedDay - availedDay
balHours = (earnedDay - availedDay) x hrswork

thanks
-Ron-

View 3 Replies View Related

Multiply

May 15, 2007

hi all
i want to multiply each row, does anyone has more simple solution other than using cursor???

-- Prepare sample data
DECLARE@tbl1 TABLE (num1 float )

INSERT@tbl1
SELECT'1' UNION ALL
SELECT'2' UNION ALL
SELECT'3'

select * from @tbl1

declare @no1 as float, @total as float
set @total=1

DECLARE c1 CURSOR FOR
select * from @tbl1
OPEN c1

FETCH NEXT FROM c1
INTO @no1

WHILE @@FETCH_STATUS = 0
BEGIN
set @total=@total*@no1

FETCH NEXT FROM c1
INTO @no1
END

CLOSE c1
DEALLOCATE c1

select @total AS SubMultiply


~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View 12 Replies View Related

SQL Server 2008 :: Divide Total Rate By Number Of Records

Jun 22, 2015

my table includes detailed records with total Rate repeated in each record:

CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,

[code]....

View 1 Replies View Related

How To Multiply Value In A Field

Nov 1, 2007

Hello, I have a quick question. I'm currently using MS SQL 2005.

I have a main table which has many fields. I created a view#1 which imports all the rows

from the main table but I only select the "City" field, "PurchaseDescription" field and a

"Cost" field. Only the city name is unique. There are a total of 50 different city names,

and each name can have more than one "PurchaseDescription" each with an associated "Cost". I

would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to

contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to

"USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the

"PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as

"Cost".


For example,

The view#1 will have the following rows & fields (I had to pad the field with dots just to

make the output look viewable on this thread)

City..............PurchaseDescription.........Cost
--------------------------------------------
LA.................desk........................... ..4.5
LA.................USB............................ ..5.0
LA.................USB............................ ..6.0
SD................chair........................... ...4.0
SD................door............................ ..10.0

The view#2 should have the following rows and fields


City..............PurchaseDescription.........Cost .......Cost2
---------------------------------------------------------
LA.................desk........................... ..4.5....... 4.5
LA.................USB............................ ..5.0....... -5.0
LA.................USB............................ ..6.0....... -6.0
SD................chair........................... ...4.0....... 4.0
SD................door............................ ..10.0....... 10.0

I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need

a final view that would look like view#2.

Could you please help if you can?

Thank you very much for your time and have a safe Halloween!

View 3 Replies View Related

How To Multiply Two Value From Two Fields

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

GL_ID|GL_Name_VC |Period_01|Period_02 |Total_YTD
================================================
1000 | Inventory | 8,000 | 2,000 |10,000

Percentage| 10% | 20% | 0
================================================
Total | 800 | 400 | 1,200

The Total row is calculated by multiplying the percentage row by the Inventory amount in
each Period.

Guys, hope someone out there can help me with the sql command for the above report?

View 1 Replies View Related

Multiply Fields

Mar 12, 2007

Hi. Just a question. Did I use it proper to multiply fields and assign to a variable?

select l.* , e.paytype, e.sectioncode, e.empno,dc.fullname,
earnedDays = lc.earned, availedDays = lc.availed,
earnedHours = (lc.earned * e.hrswork), availedHours = (lc.availed * hrswork),
balHours = (earnedHours - availedHours), balDays = (earnedDays - availedDays)
from hrempleaveledger as l
inner join......
inner join......
.
.
.


I can't surf through the net because i have no permission from the administrator.

Thanks.
-Ron-

View 6 Replies View Related

How To Multiply Value In A Field By -1

Nov 1, 2007

Hello, I have a quick question. I'm currently using MS SQL 2005.

I have a main table which has many fields. I created a view#1 which imports all the rows from the main table but I only select the "City" field, "PurchaseDescription" field and a "Cost" field. Only the city name is unique. There are a total of 50 different city names, and each name can have more than one "PurchaseDescription" each with an associated "Cost". I would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to "USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the "PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as "Cost".


For example,

The view#1 will have the following rows & fields (I had to pad the field with dots just to make the output look viewable on this thread)

City..............PurchaseDescription..........Cost
-----------------------------------------------------
LA.................desk.............................4.5
LA.................USB..............................5.0
LA.................USB..............................6.0
SD................chair.............................4.0
SD................door.............................10.0

The view#2 should have the following rows and fields


City..............PurchaseDescription........Cost.......Cost2
---------------------------------------------------------
LA.................desk..........................4.5........ 4.5
LA.................USB...........................5.0....... -5.0
LA.................USB...........................6.0....... -6.0
SD................chair..........................4.0........ 4.0
SD................door..........................10.0....... 10.0

I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need a final view that would look like view#2.

Could you please help if you can?

Thank you very much for your time and have a safe Halloween!

View 9 Replies View Related

Multiply Fields

Jan 31, 2008

In derived fields, I need to multiply numbers from 2 fields. Assume that I need to multiply numbers from field B and field C. Although, B is found by COUNT (field A). So, I do the following: B*AltName of A. It gives me an error. Thank you for any help!

MG

View 4 Replies View Related

Multiply Rows With T - SQL

Apr 18, 2006

I need to multiply rows of a table with each other. I found a function like this
exp(sum(ln(floatfield1))).

But I can not find the ln function. Is there any other possibility ?



Thanks for any help.

Uwe

View 4 Replies View Related

Multiply Numbers In A Column

Mar 21, 2002

Hi

I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.

There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :

SELECT EXP(SUM(LOG(ColumnName))) FROM tblName

This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.

I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.

If anyone has any ideas or suggestions, then that would be much appreciated.

thanks....Tom

View 1 Replies View Related

Get Group And Multiply Records

Dec 20, 2013

1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result

======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]

[Code] .....

View 14 Replies View Related

Transact SQL :: How To Multiply Against A Negative Value

Jun 11, 2015

what am I doing wrong here? I can multipley * 1.25, but not -1.25. Every google entry I find is how to convert positive to negative numbers... I want to use update to multiply and lower the price by 25% (I used the function a bit to liberally to multiply by 125% and now want to bring the values down to earth)

update production.product set listprice = (listprice * -1.25)
select max(listprice) from production.product

View 8 Replies View Related

Transact SQL :: Two Decimals Multiply Each Other Become Zero

May 18, 2015

Script as follows :

select cast( 0.0050000000 as decimal(38,23)) * cast(0.0000010000 as decimal(38,23))
select cast( 0.0050000000 as decimal(28,23)) * cast(0.0000010000 as decimal(28,23))
select cast( 0.01 as decimal(28,15)) * cast(0.0000000001 as decimal(28,15))
select cast( 0.01 as decimal(28,16)) * cast(0.0000000001 as decimal(28,16))

The result was following:

0.0000000--was zero
0.000000005000000000000000000
0.00000000000--was zero
0.0000000000010

View 3 Replies View Related

How Can I Multiply Values Within A Dataset?

Mar 6, 2008

I have this case where I need to multiply the value of my dataset and finally deduce one to it. For instance if I have a dataset with three values, 2, 5, 15, I need to end up with a value of 150 (2*5*15). How can I achieve this?

View 3 Replies View Related

SUM Multiply Based On Column Entry

Sep 5, 2012

SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
FROM SR.dbo.PayTrays
WHERE WorkDate > '2012-01-01'
AND WorkDate < '2012-12-31'
GROUP BY EmployeeId, PiecemealType, ActivityId
ORDER BY EmployeeId, PiecemealType

PiecemealType is the type of tray
1= 15 Items per tray
2= 12 Items per tray
3= 8 Items per tray
4= 6 Items per tray

I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.

View 2 Replies View Related

Multiply Of Columns Based On New Group

Dec 19, 2013

Product IdIsPrimaryQuantity
P0011
P001.102
P001.204
P001.305
P0021
P002.106
P002.207
P002.309
P002.4010
P002.5011

Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL

Product IdSubProductQuantity
P001 40
P001 P001.3NULL
P002 41580
P002 P002.5NULL

This is same as [URL] ....

View 5 Replies View Related

Analysis :: DAX Row Level Multiply First Then Sum Up In Total

Jul 30, 2015

I've got measure a in one table and measure b in another table and calculation a*b.

            a  b   calc

            2  4   8

            5  2   10

SUM     7   6   18

How to get 18 as total instead of 7*6=42. I'd like to get row level calculation to sum up for the total level...

View 3 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

Creating A Query That Access Multiply Databases

Dec 10, 2007


Dear Readers,
Is it possible to create a query that access multiple sql express databases? Or is it possible to link a table in one database from another?

View 1 Replies View Related

COALESCE Help

Nov 5, 2007

I have a pulldown menu which has like  4 options
producta productb productc and all
I am trying to retrieve the maximum  build number value for these products and display on the gridview as per some other conditions like user selected OS etc
 Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC
and I am trying to use coalesce but unable to get my result.
I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.
 If I do the following with no max funciton, i see all the values but i just want max from each branch.
DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results
where branch in ('ProductA','Product B','ProductC')
select @buildList
 
Please let me know how to do this.

View 8 Replies View Related

Coalesce With LIKE?

Apr 29, 2008

I have a stored procedure which receives a dynamically built WHERE clause.  This is then appended to the sql query within like....'select * from table' +@where_clause.
I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this.  I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
In my application the user can enter 1-to-many textboxes as search criteria.  What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'"
Else
sqlwhere = " where lname like '" & tx_lastname.Text & "'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'"
Else
sqlwhere = " where fname like '" & tx_firstname.Text & "'"
End If
End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated. 

View 15 Replies View Related

How To Use Coalesce

May 11, 2008

A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it?  1 SELECT (SELECT Location
2 FROM Location_Table
3 WHERE (Property_Table.LocationID = LocationID)) AS Location,
4 (SELECT TypeOfProperty
5 FROM Type_Table
6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7 FROM Property_Table
8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
 

View 8 Replies View Related

To Coalesce Or Not

Jun 21, 2007

I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?



WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME

View 2 Replies View Related

Coalesce

Jan 29, 2004

Hi.

I have a piece of a store procedure I don't quite understand, as follows:

SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))

What exactly is the COALESCE function doing here with the parameter?

View 9 Replies View Related

Coalesce Help

May 14, 2008

How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there

cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],

View 10 Replies View Related

What Is Use Of Coalesce

Oct 4, 2013

I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.I came across a piece of code posted in the forum about the different uses of coalesce.

use adventureworks
DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

[code]...

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved