It's an old thread, Wesley - so it's quite likely that none of the respondents will reply. Are you experiencing a similar issue? If so, your best option might be...

Check your table aliases:

SELECT s.state_name

, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits

, SUM(DistinctPermits) AS DistinctPermits

FROM States S

CROSS APPLY (

SELECT w.StateID, COUNT(*) as DistinctPermits

FROM Permit P

INNER JOIN Well W1 ON P.WellID...

rlsublime (3/20/2014)

---

I am trying to add a '<19' filter to the following sql code. And ideas would be appreciated as I am a sql newbie. thanks

(Select sum(NVL(sh_productivehours,0))

...

Here's a sandpit version very similar to what you currently have. The only downside is the ugly aggregate - but it's a very simple ugly aggregate. I guess it depends...

Can you augment the sample table in my last post to account for your observations? Thanks.

SQL Server will apply the filter and the conversion in whichever order results in the lowest cost - and in this case, the order chosen is breaking your query. Here's...

You're welcome, thanks for the feedback.

There are loads of ways of doing this. Here are two.

drop Table #Sample

Create Table #Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) ...

The numeric test and the cast to bigint have to occur together and in the correct order.

CREATE VIEW ChildEmployees

AS

SELECT x.EmpID

FROM MasterEmployees

CROSS APPLY (

SELECT EmpiD CASE

WHEN ISNUMERIC(EmpID) =...

Here's a solution which will work with many values. First the UDF:

ALTER FUNCTION fn_BITWISE_OR

(

@col1 VARCHAR(3)

)

RETURNS VARCHAR(3)

AS

BEGIN

-- try row-wise

DECLARE @LastValue INT

SELECT @LastValue = 000

SELECT @LastValue = @LastValue | d.bit_flag

-- substitute...

joakim.fenno (3/18/2014)

---

OK

CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)

INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');

expected output from query :

A 101

B 011

The bit_flag column contains 12 flag (and...

Although just about every reference on the first page returned by Google specifies two operands, the TSQL BITWISE OR actually works with a chain like this:

-- on a single...

joakim.fenno (3/18/2014)

---

ChrisM@Work (3/18/2014)

---

joakim.fenno (3/18/2014)

---

ChrisM@Work (3/18/2014)

---

If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it...

joakim.fenno (3/18/2014)

---

ChrisM@Work (3/18/2014)

---

If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far...

joakim.fenno (3/18/2014)

---

I be happy to use the built in T-SQL functions if that is possible.

But the function needs to work as an aggregate function.

I have to be able to execute...