I have three columns used in a table I'm trying to get a distinct count on. userID, messageFrom, messageDate.
messaging is the table name
messageDate is smalldatetime in the format of MM/DD/YY 12:00:00 AM (always 12am)
userID is int, to whom the message is going to
messageFrom is nvarchar(128), who's sending the message
I'm trying to find the count of how many unique messageFrom's are sent to a particular userID on a daily basis from the beginning of the year. In this code, userID 1008. What's desired is if 2 identical messageFrom's are sent in the same day, it only counts as one. If 2 identical messageFrom's are sent in two days, it counts as two.
I'm unsure if the code below is correct as it's showing data, just much less than I expected.
SELECT COUNT(*) FROM
(SELECT DISTINCT messageFrom,messageDate
AND messageDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS t