Hi there,

Here's the solution i came up with.. I hope it helps..:-)

;WITH cte AS

(

SELECTEmpId

, StartDate

, ISNULL(EndDate,GETDATE()) as EndDate

, ROW_NUMBER() OVER (PARTITION BY Empid ORDER BY StartDate) rn

FROM Duration d

)

SELECTa.EmpId

,MIN(a.StartDate) AS...

Hi there,

I hope this helps..

DECLARE @x TABLE (col1 INT, col2 VARCHAR(10))

INSERT INTO @x

SELECT 1,'0TY/OK' UNION ALL

SELECT 1,'0TY/OK' UNION ALL

SELECT 1,'0TY/OK' UNION ALL

SELECT 1,'0TY/OK' UNION ALL

SELECT 1,'0TY/OK' UNION ALL

SELECT 2,'2KP/L'...

Hi..

This may produce your expected result.. I just added logic to concatenate the values from unpivoted table..

DECLARE @table TABLE (Emp_Name VARCHAR(50),PHN INT, MOB INT, OTH INT)

INSERT INTO @table

SELECT 'Steve',223,456,895 UNION...

Hi Mac,

I believe this has relation to your previous post.. 😀

I created table mapping between users and groups here, so it can retrieve only the groups where a particular user...

Hi there,

There's nothing wrong with your query.. Use DISTINCT keyword to eliminate the duplicate values..

Syntax here:

SELECT DISTINCT <column 1>, ... <column N>

FROM <table>

Just a side note, alias name is mostly...

My apologies for my late reply..

I used your sample data with additional AuditLevel column.. This will include '(All)' if @CheckAuditor = 0..

DECLARE @AuditTypes TABLE (AuditTypeId INT, AuditType NVARCHAR(100), AuditLevel...

The IDs are hard-coded in the solution that I have presented.. I agree with Scott that it would be more efficient and easier to maintain if separate table would be...

I agree with Robert, use a derived table to combine the names before applying FOR XML PATH.. If you want the column to have an alias, you need to use...

Hi again..

You may eliminate cross join with correlated subquery, here's my other solution..

SELECT AuditTypeId, AuditType

FROM @AuditTypes at

WHERE AuditTypeId = CASE WHEN @CheckAuditor = 0

THEN AuditTypeID

ELSE

(SELECT TOP(1) AuditTypeId FROM...

Hi there,

I'm just trying to help.. You may use correlated subquery to filter the result depending on the parameter passed.. Here's my solution:

DECLARE @AuditTypes TABLE (AuditTypeId INT, AuditType NVARCHAR(100))

DECLARE...

Hi there,

The result is also sorted by V1 and V2 in ascending order since that is the order when you inserted those records into @AAAATABLE.. Running your query produces this...

Hi there,

Just wanna help you.. I think you need to make DependentNo column from Table2 a primary key to ensure referential integrity..

This is my proposed table design..

CREATE TABLE Table1

(

UserId INT...

Hi there,

I'm running a 32-bit version of SQL Server.. Your query also produces the same error on my machine.. But, without ORDER BY clause, the query runs fine.. I think...

Hi there,

This query might produce your expected result:

SELECT vc.case_sk, vc.case_number, vc.style, vle.computename AS PartyName, cpa.create_user_id, cpa.action_sk, cpa.action_date,

cpa.action_date AS BeginDate, cpa.action_date AS EndDate, cpa.entity_sk, vle.legal_entity_sk

FROM#vle AS vle

JOIN#cpa AS...

Hi there,

Does it mean there is at least 2 occurrences of same number in IdY? Ex: IdX=770001.. 770001 appears twice in IdY.. Sample code below:

CREATE TABLE test

(

IdX INT,

IdY INT,

Sc INT,

Va...