quote:

---

Originally posted by trombley

it's "No Stat", with a space. I don't understand adding the true condition after the "and"? The Legla field may have many other values in it, I only want the calculation where "No Stat" is in the field.

---

SELECT TargetPrice =
CASE
WHEN (dbo.R_MAC_Dales_Sales_Report.PrimStat = 'Active' AND 1 = 1 ) THEN 
((([Use Alias].CurrentAppValue)* .5)- [Use Alias].TotalTax)
ELSE -999
END
FROM dbo.R_MAC_Dales_Sales_Report [Alias Name] INNER JOIN
.............

 

CREATE TABLE Table1 (Column1 int, Column2 varchar(50), Column3 varchar(50), Column4 int, Column5 int)

INSERT INTO Table1 VALUES(1, 'Active', 'No Stat', 1, 2)
INSERT INTO Table1 VALUES(2, 'Active', 'No Stat', 2, 3)
INSERT INTO Table1 VALUES(3, 'Active', 'Stat', 3, 4)
INSERT INTO Table1 VALUES(4, 'Active', 'No Stat', 4, 5)
INSERT INTO Table1 VALUES(5, 'Active', 'Stat', 5, 6)
INSERT INTO Table1 VALUES(6, 'Not Active', 'Stat', 6, 7)

SELECT Column3, TargetPrice = 
	CASE
		WHEN (Column2 = 'Active' AND Column3 = 'No Stat') THEN (((Column4)* .5)-Column5)
		ELSE -999
	END
FROM Table1

DROP TABLE Table1

SELECT TargetPrice =
	CASE
		WHEN (r.PrimStat = 'Active' AND r.Legal = 'No Stat') THEN (((l.CurrentAppValue)* .5)-r.TotalTax)
		ELSE -999
	END
FROM dbo.R_MAC_Dales_Sales_Report r
INNER JOIN dbo.Loan l
ON r.LoanID = l.LoanID

quote:

---

Originally posted by tkizer

It works fine for me on my sample table and data. Check this out:

CREATE TABLE Table1 (Column1 int, Column2 varchar(50), Column3 varchar(50), Column4 int, Column5 int)

INSERT INTO Table1 VALUES(1, 'Active', 'No Stat', 1, 2)
INSERT INTO Table1 VALUES(2, 'Active', 'No Stat', 2, 3)
INSERT INTO Table1 VALUES(3, 'Active', 'Stat', 3, 4)
INSERT INTO Table1 VALUES(4, 'Active', 'No Stat', 4, 5)
INSERT INTO Table1 VALUES(5, 'Active', 'Stat', 5, 6)
INSERT INTO Table1 VALUES(6, 'Not Active', 'Stat', 6, 7)

SELECT Column3, TargetPrice = 
	CASE
		WHEN (Column2 = 'Active' AND Column3 = 'No Stat') THEN (((Column4)* .5)-Column5)
		ELSE -999
	END
FROM Table1

DROP TABLE Table1

Do both of these return data:

SELECT * FROM dbo.R_MAC_Dales_Sales_Report WHERE Legal = 'No Stat'
SELECT * FROM dbo.R_MAC_Dales_Sales_Report WHERE PrimStat = 'Active'



Does this work fine:

SELECT TargetPrice =
	CASE
		WHEN (r.PrimStat = 'Active' AND r.Legal = 'No Stat') THEN (((l.CurrentAppValue)* .5)-r.TotalTax)
		ELSE -999
	END
FROM dbo.R_MAC_Dales_Sales_Report r
INNER JOIN dbo.Loan l
ON r.LoanID = l.LoanID

Tara Kizer
aka tduggan

---

quote:

---

Originally posted by tkizer

Do you understand why No Stat didn't work for you? I tried to explain it in my last post.

Here's an example that you can run in Query Analyzer:

CREATE TABLE Table1 (Column1 int, Column2 varchar(50), Column3 varchar(50), Column4 int, Column5 int)

INSERT INTO Table1 VALUES(1, 'Active', NULL, 1, 2)
INSERT INTO Table1 VALUES(2, 'Active', NULL, 2, 3)
INSERT INTO Table1 VALUES(3, 'Active', 'Stat', 3, 4)
INSERT INTO Table1 VALUES(4, 'Active', NULL, 4, 5)
INSERT INTO Table1 VALUES(5, 'Active', 'Stat', 5, 6)
INSERT INTO Table1 VALUES(6, 'Not Active', 'Stat', 6, 7)

SELECT Column3 AS ActualData, ISNULL(Column3, 'No Stat') AS DisplayNoStatInstead
FROM Table1
WHERE Column3 IS NULL

SELECT Column3 AS ActualData, ISNULL(Column3, 'No Stat') AS DisplayNoStatInstead
FROM Table1
WHERE Column3 = 'No Stat'

DROP TABLE Table1

The first column in the result set is the actual data in the table. The second column in the result set substitutes NULL data with No Stat. Notice in the first result set we get data back if we search for NULLs. In the second result set, we don't get any data back as Column3 does not have any rows equal to No Stat.

When I asked if this query returned any rows SELECT * FROM dbo.R_MAC_Dales_Sales_Report WHERE Legal = 'No Stat', the answer would have been No as your data doesn't have this value. You answered Yes, which wasn't true.

Let me know if it is still unclear.

Tara Kizer
aka tduggan

---