quote:

---

Originally posted by cyberpd

I have a table, named table1, consisting of date (datetime), securityid (varchar)
let us take some sample data.
1/1/2008 A
2/1/2008 A
1/1/2008 B
2/1/2008 C
1/1/2008 D
2/1/2008 D
Now, the query should return those securityid that has not all the date related with it.
I mean, there is two unique date. The securityid "A" is related to both. So the query will ignore
this.
But the securityid "B" has only one date assossiated with it. Another date is missing. So the
query will return the securityid and date missing, e.g 2/1/2008 B
The same for "C". The query will return 1/1/2008 C
The query will ignore the securityid D because it is associated with both the dates.
Plz help me with this query.
Thanks and regards.

---

SELECT t.securityid
FROM YourTable t
CROSS JOIN (SELECT COUNT(DISTINCT date) AS TotalDates FROM YourTable) t1
GROUP BY t.securityid
HAVING COUNT(DISTINCT date) <> MIN(t1.TotalDates)

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by cyberpd

I have a table, named table1, consisting of date (datetime), securityid (varchar)
let us take some sample data.
1/1/2008 A
2/1/2008 A
1/1/2008 B
2/1/2008 C
1/1/2008 D
2/1/2008 D
Now, the query should return those securityid that has not all the date related with it.
I mean, there is two unique date. The securityid "A" is related to both. So the query will ignore
this.
But the securityid "B" has only one date assossiated with it. Another date is missing. So the
query will return the securityid and date missing, e.g 2/1/2008 B
The same for "C". The query will return 1/1/2008 C
The query will ignore the securityid D because it is associated with both the dates.
Plz help me with this query.
Thanks and regards.

---

SELECT t.securityid
FROM YourTable t
CROSS JOIN (SELECT COUNT(DISTINCT date) AS TotalDates FROM YourTable) t1
GROUP BY t.securityid
HAVING COUNT(DISTINCT date) <> t1.TotalDates

---

quote:

---

Originally posted by Peso

Column 't1.TotalDates' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Put a MIN() around t1.TotalDates to fix that problem.

---

E 12°55'05.25"
N 56°04'39.16"

---

SELECT DISTINCT	s.SecurityID,
		t.Date
FROM		@Sample AS s
CROSS JOIN	@Sample AS t
LEFT JOIN	@Sample AS w ON w.Date = t.Date
			AND w.SecurityID = s.SecurityID
WHERE		w.Date IS NULL

-- Peso 2
SELECT		s.SecurityID,
		d.Date
FROM		(
			SELECT		SecurityID
			FROM		@Sample
			GROUP BY	SecurityID
		) AS s
CROSS JOIN	(
			SELECT		Date
			FROM		@Sample
			GROUP BY	Date
		) AS d
LEFT JOIN	@Sample AS w ON w.Date = d.Date
			AND w.SecurityID = s.SecurityID
WHERE		w.Date IS NULL