Need some help with calculations

Transact-SQL
,
1

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 :confounded:

834×177 6.32 KB

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

2

Here is one way to do it. It may not be the fastest and most efficient but you get the idea...enough to move forward at least.

`DECLARE @TableCompanyNotes TABLE
(
NotesId INT NOT NULL PRIMARY KEY,
cpvId INT NOT NULL,
cpvNotes VARCHAR(64) NULL,
SalesOrGen BIT NOT NULL DEFAULT 0,
NextContactUserId INT NOT NULL,
NextContactDate DATETIME NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
FollowUp BIT NOT NULL DEFAULT 0
);

INSERT INTO @TableCompanyNotes
(NotesId, cpvId, cpvNotes, SalesOrGen, NextContactUserId, NextContactDate, CreateDate, FollowUp)
VALUES
(2012, 4969, 'Testing', 1, 4, '2015-12-31', GETDATE(), 1),
(2013, 4910, 'Testing', 0, 4, '2016-12-31', GETDATE(), 1),
(2014, 4910, 'Testing', 0, 5, '2016-01-07', GETDATE(), 1),
(2015, 4911, 'Testing', 1, 5, '2015-11-30', GETDATE(), 1),
(2016, 4910, 'Testing', 0, 6, '2016-01-07', GETDATE(), 1),
(2017, 4912, 'Testing', 0, 11, '2015-12-03', GETDATE(), 1);

;WITH cteData AS
(
SELECT tcn.NotesId, tcn.NextContactUserId, DATEDIFF(day, GETDATE(), tcn.NextContactDate) As DayDiff
FROM @TableCompanyNotes As tcn
)
SELECT d.NextContactUserId,
COUNT(d.NextContactUserId) As CurrentCount,
SUM(CASE WHEN d.DayDiff < 365 THEN 1 ELSE 0 END) As LessThan365,
SUM(CASE WHEN d.DayDiff >= 365 THEN 1 ELSE 0 END) As MoreThan365

FROM cteData As d
GROUP BY d.NextContactUserId;`

3

Hi James, This works just fine thank you. It is only for a report. Many thanks for you help is it much appreciated :+1: