Using LEFT JOINs instead of INNER JOINs should solve your immediate problem. Age should always be calculated from DateOfBirth.

AMacKay,

You really need to redesign your tables so that the <Customers> table has a PRIMARY KEY called something like customerID int. Then you will need to add customerID as a...

The following should work but, like all triangular joins, if you have a lot of rows it would be better to use a cursor. Somewhere between 5000 and 10000 rows...

Do the following test with UPDLOCK:

1. Open Query Analyser twice selecting your db in both.

2. In one copy of QA run:

BEGIN TRAN

SELECT MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

3. In the other copy...

I think you need to use either an UPDLOCK or an XLOCK. An UPDLOCK should be alright here.

SET XACT_ABORT ON

BEGIN TRANSACTION

SELECT @get_PIN = MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

DELETE card_pins

WHERE serialNo= @get_PIN...

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

SELECT DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime)) AS WorkDay

 ,COUNT(DISTINCT EmployeeID) AS MaxEmp

FROM TimeClock WITH (NOLOCK)

WHERE StartTime >= '20061001' AND EndTime < '20061101'

GROUP BY DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime)...

SELECT username

FROM usermaster

GROUP BY username

HAVING COUNT(*) > 1

What you want is unclear. If the rows returned are exact duplicates then replace SELECT with SELECT DISTINCT, otherwise you will need to decide which EUS_ID row to return. eg...

Maybe:

SELECT L.LOB_ID

 ,L.EUS_ID

 ,L.LOI_START_DATE

 ,L.LOI_END_DATE

 ,E.CTY_CODE

FROM learning_object_instances L

 CROSS JOIN end_users E

 JOIN (

  SELECT L1.EUS_ID

  FROM learning_object_instances L1

  WHERE L1.LOI_START_DATE > '20060907'

  GROUP BY L1.EUS_ID

  HAVING COUNT(*) > 1 ) D

 ON L.EUS_ID = D.EUS_ID

WHERE E.CTY_CODE = 'RU'

 AND L.LOI_START_DATE > '20060907'

You may want to consider a different strategy:

1. Create views for all tables and views in your db along the lines of:

CREATE VIEW RO_YourTableName

AS

SELECT *

FROM YourTableName WITH (NOLOCK)

2. For your...

I think this can be done by creating a db that only consists of VIEWs of the tables in the main db. All the SELECTs in the VIEWs will have the...

Old style

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1,InvDetl d2

where d1.InvNum=d2.InvNum and d1.ItemID<>d2.ItemID

New style

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1

  INNER join InvDetl d2 on d1.InvNum=d2.InvNum and d1.ItemID<>d2.ItemID

or

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1

  CROSS join InvDetl d2

WHERE d1.InvNum=d2.InvNum and...

Something like:

-- *****************

-- Test Data

DECLARE @t TABLE

(

 col1 CHAR(3) NOT NULL

 ,col2 SMALLINT NOT NULL

)

INSERT @t

SELECT 'abc', 101 UNION ALL

SELECT 'def', 201 UNION ALL

SELECT 'ghi', 301

-- End of Test Data

-- *****************

-- Use...

Using the above test data, and assuming trainingname is the same as QuizName, how about:

SELECT E.JobCCNo AS CostCenter

 ,D.trainingname AS QuizName

 ,COUNT(DISTINCT E.EmpNo) AS NumberOfEmployees

 ,COUNT(DISTINCT T.EmpNo) As NumberTrained

 ,COUNT(DISTINCT E.EmpNo) - COUNT(DISTINCT T.EmpNo)...