SPROC T-SQL Syntax to return results if rows exist on multiple days

Question

what I need to test for on my table is if there are rows for a given user id and order id on two separate days (DATETIME field for a timestamp).

I'm pretty sure I'd need a having clause and that's why I'm here...that frightens me terribly.

Answer 1

Having shouldn't scare you, it is just a "Where" on an aggregated field:

Select UserID, Count(*) From OrderTbl Group By UserID Having Count(*) > 1

That'll give you all the Users that have multiple orders.

Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID Having Count(*) > 1 

will give you the count if there are multiple records for the user id in @UserID and null if not.

if exists (Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID 
             Having Count(*) > 1) Select 1 else Select 0

will return a 1 if there are multiple records for the User, 0 if not.

Update: Didn't realize that you could have multiple orders per day. This query will do what you want:

With DistinctDates as (Select Distinct UserID, [DATE] From OrderTbl Where (UserID=@UserID))
Select UserID, Count(*) From DistinctDates  
Group By UserID Having Count(*) > 1 

Answer 2

I am not sure if I understood your question, but this may work for you. The HAVING is your friend and you can still use the WHERE clause. This should let you know what order and user id combo is occuring more than once in the table.

SELECT [UserId], [OrderId]
FROM OrderTable 
WHERE UserId = @UserId
AND OrderId = @OrderId
GROUP BY UserId, OrderId
HAVING Count(*) > 1