THis?

SELECT * FROM #Fred;

; WITH CTE AS

(

SELECT OrganisationID , LocationID ,CT = COUNT(*) --, Cnt = COUNT(*) OVER ( PARTITION BY OrganisationID , LocationID)

FROM #Fred

GROUP BY OrganisationID , LocationID

)

,...

Can you provide us a sample input and expected output for that sample input?

Request 1 - Lower ur Caps while posting the question. It is sore to the eyes while reading.

Request 2 - Though you have 75% succeeded in showing what you want...

:hehe:

Sean Lange (2/27/2012)

---

ColdCoffee (2/27/2012)

---

CELKO (2/27/2012)

---

CREATE TABLE Customer_History

(customer_id CHAR(9) NOT NULL,

start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

end_date DATETIME UNIQUE, --null means current

CHECK (start_date <= end_date),

...

CELKO (2/27/2012)

---

CREATE TABLE Customer_History

(customer_id CHAR(9) NOT NULL,

start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

end_date DATETIME UNIQUE, --null means current

CHECK (start_date <= end_date),

foobar_status CHAR(??) NOT...

How quickly should the data in Balance table be updated? is it instantaneously ?

Either LoginPassword or LoginSKey (or other cols) is a VARBINARY(MAX) data-type. you can not convert 'xxxx' implicitly.. You have to wrap like this CAST( 'XXXX' AS varbinary(max)) or CONVERT(VARBINARY(MAX) ,'XXXX')

ACinKC (2/23/2012)

---

The key point to help you understand what's going on is to know what NULL actually represents....

You go to do this via Dynamic SQL

I am bit lost here... is your requirement to find the count of rows that are exactly older than 30 days?

If yes, then use this

DECLARE @Dateparam DATETIME = '3-MAR-2012'

SELECT ...

Ray K (2/22/2012)

---

Thanks for your help as always, guys!

It looks like the CTE might be the best bet. Obviously, it works fine with the data sample I put together;...

Evil Kraig F (2/22/2012)

---

Agreed, I was answering the initially asked question of what method to use...

Evil Kraig F (2/22/2012)

---

Thanks for the excellent setup. You're close, you just need to use grouping and having.

SELECT

p.ID,

p.[name]

FROM

#Degree

join #DegreeJct on #Degree.ID = #DegreeJct.DegID

join #People AS p on #DegreeJct.UserID...

How about this?

; WITH CTE AS(

SELECT DegName = DEG.Deg

,PerName = PEP.Name

,ContOcc = COUNT(PEP.Name) OVER(PARTITION BY PEP.Name)

FROM #DegreeJct ...