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
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
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')
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
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.
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
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)
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!
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.
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
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
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?
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.
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].
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?
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
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.
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)
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
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?
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],
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')