Me too, seems to be this

http://www.sqlservercentral.com/Forums/Topic829828-338-1.aspx

Can't help...

SELECT OrderNumber

FROM OrderDetails

GROUP BY OrderNumber

HAVING COUNT(SINumber)=0

SELECT ID

FROM MyTable

GROUP BY ID

HAVING COUNT(DISTINCT CODE)>1

Faye Fouladi (12/3/2009)

---

I want this to return 0 when there is no records found.

Select IsNULL(Count(*), 0) As Count, RT.Report_ID, ReportName, ReportCategory From

tTracking_tblReportsUsage tbTracking

Right Join tblReportsNew RT On...

I suspect this would be more efficient with a numbers/tally table

declare @TargetStr varchar(8000),

@SearchedStr varchar(8000),

@Occurrence int

set @TargetStr='a'

set @SearchedStr='abbabba'

set @Occurrence=3;

WITH Occurrences AS (

SELECT Number,

...

The...

Without DDL and sample data, this is little more than a guess.

WITH NavigationHierarchy (menuItemId, parentId, displayName, linkUrl, sortOrder, treeLevel) AS

(

SELECT mi.menuItemId, mi.parentId, mi.displayName, mi.linkUrl,...

GSquared (11/30/2009)

---

Do a cross join between departments and grades, then a correlated subquery on employees from that.

Would look something like:

select DeptID, DeptName, GradeID, GradeName,

(select count(*)

from Employee

where DeptID = Department.DeptID

and GradeID...

Try this

SELECT s1.StartDate,

MIN(t1.EndDate) AS EndDate

FROM Overlapping s1

INNER JOIN Overlapping t1 ON s1.StartDate <= t1.EndDate

AND NOT EXISTS(SELECT * FROM...

See if this helps

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate='20100901'

SET @EndDate='20100921'

SELECT CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END AS StartDate,

CASE WHEN ToDate<=@EndDate THEN...

amazona (11/27/2009)

---

Sorry Mark, I didn't see your solution before posting my version of the same thing.

I find it strange that TSQL doesn't have built-in cross-tab query functionality whereas Access does....

Maybe this?

update cants

set first_priority = 1

from (select max(date) date, d.Id

from cants d

where ...

Lynn Pettis (8/4/2008)

---

There may be a better way (ie without the subquery, set based) but here is something that at least works. If I get any more brilliant ideas,...

Try this

WITH CTE AS (

SELECT TownName, CountyName,

COUNT(*) OVER(PARTITION BY TownName) AS cn

FROM Town

INNER JOIN County ON County.CountyID = Town.CountyID

)

SELECT TownName, CountyName

FROM CTE

WHERE cn>1

This will give you a list of all overlapping activities

SELECT a.actid,

b.actid AS Overlapped_actid

FROM @tActivities a

INNER JOIN @tActivities b ON b.actid>a.actid

AND a.astarttime<b.astoptime

...