I would really like some help if possible. I have a table with a list of customers and dates
What I need to achieve is the following;
I need to get a summary for example. A count for all records grouped NextContactU, a count for all records less than or equal to 365 days grouped by NextContactU, then finally a count of all records that are greater than 365 days grouped by NextContactU.
I would like the layout if possible
NextContactU ---- Count of Records ---- <=365 days count --- >365 days count.
I would really appreciate any help given. I have tried all sorts and failed
I have this so far.
`SELECT NextContactUserId, COUNT(NotesId) AS lessThan365 from ( select NextContactUserID, NotesId FROM tblCompanyNotes where tblCompanyNotes.NextContactDate<DATEADD(day, 365, GETDATE()) )a group by NextContactUserID`
This gives me the following result. I just don't know how to add the rest of the columns to appear in the same row for each NextContactUserId
NextContactUserId lessThan365 ----------------- ----------- 4 1 5 2 6 1 11 1
I also have tried this which gives me the results just not very dynamic
DECLARE @CurrentCount INT DECLARE @LessThan365 INT DECLARE @MoreThan365 INT DECLARE @UserId INT SET @UserId=4; SET @MoreThan365 = (SELECT COUNT(*) FROM tblCompanyNotes WHERE NextContactDate>DATEADD(day, 365, GETDATE()) And NextContactUserID=@UserId); SET @LessThan365 =(SELECT COUNT(*) FROM tblCompanyNotes WHERE NextContactDate<=DATEADD(day, 365, GETDATE()) And NextContactUserID=@UserId); SET @CurrentCount=(SELECT COUNT(*) FROM tblCompanyNotes WHERE NextContactUserID=@UserId); Select @UserId as UserId, @CurrentCount as CurrentCount, @LessThan365 as LessThan365, @MoreThan365 as MoreThan365
the desired result I need to achieve is
UserId CurrentCount LessThan365 MoreThan365
4 2 1 1