Sure try the key phrase "foreign key". You act as if foreign keys to the same table are somehow mysteriously different from foreign keys to other tables, but they're...

It's hard to give you any help without more details. For instance, you don't mention how you are copying your Excel file to the database—SSIS, OPENROWSET, etc.

Drew

A simple modification of my previous response will give you the results you want. Of course, including the HAVING clause excludes both of your records.

DECLARE @QuarterEnd DATE = '2011-12-31'

SELECT...

I think that you are overcomplicating things by using the wrong aggregate. Instead of using COUNT(1) you should be using COUNT(DISTINCT <some expression>)

I think this does the exact same...

dwain.c (3/26/2012)

---

You can try something like this and but it will also match ABC in 0ABC (if this is what you want):

DECLARE @clients TABLE (clientno VARCHAR(20))

INSERT INTO @clients

SELECT '55566' As...

Did you try the CROSS APPLY? The only change will be that you need to change the WHERE clause to bo.userid = u.[id]

Drew

You'll want to use an APPLY. Since you're data doesn't match your current results, this is a pure guess at what you want.

SELECT u.[ID], bo.BackOfficeID

FROM Users AS u

CROSS APPLY...

Image has been deprecated. You should avoid using it in new development. Use varbinary(max) instead.

Drew

jothibhatt_s (3/27/2012)

---

I want to create a MAT quarter based on the below data and use it as an attribute in cube.

I want to handle it in database instead of handling...

That subject is too long for a forum post, but you can search the web for "database normalization". Some quick points

* You have to search multiple fields instead of...

You don't say what you want it to be a function of. I assume jobkey and maybe date.

My recommendation is DON'T use a scalar UDF. You will probably...

The other thing is that it's not a general solution. Consider what would...

You don't really want to create tables like that, because it makes your data much harder to work with. Sometimes you may need to output data in that format...

The problem is your UDF. It uses a loop within a loop. Look at the following article by Jeff Moden: Tally OH! An Improved SQL 8K “CSV...

Here is the version using NOT EXISTS.

WITH sq AS (

SELECT

RelationshipID

,ParentID

,ChildID

,RoleID

,StatusID

,CreateDate

,ActionID

,SelectOrNot

,ROW_NUMBER() OVER (PARTITION BY ChildID ORDER BY CreateDate) AS sort

FROM @RoleRanking AS rr

WHERE NOT EXISTS (

SELECT *

FROM @RoleRanking AS sub

WHERE sub.ChildID...