Thursday, July 3, 2014

Help with a T sql query

Its polite to provide some kind of test data when making a request like this.


I put together some test data for you, and demonstrated a solution.



DECLARE @forumTable TABLE (dateofService DATETIME, LOB VARCHAR(2), customerID INT, customerName VARCHAR(50), amountPaid MONEY)
INSERT INTO @forumTable (dateofService, LOB, customerID, customerName, amountPaid)
VALUES
('2014-01-01', 'A', 1, 'Malfurion Stormrage', 10),('2014-02-01', 'A', 1, 'Malfurion Stormrage', 45),
('2014-02-02', 'A', 2, 'Tyrande Whisperwind', 12),('2014-02-02', 'A', 2, 'Tyrande Whisperwind', 84),
('2014-03-03', 'A', 3, 'Thrall', 41),('2014-03-03', 'A', 3, 'Thrall', 73),
('2014-04-01', 'A', 4, 'Varian Wrynn', 22),('2014-04-04', 'A', 4, 'Varian Wrynn', 45),
('2014-05-01', 'A', 5, 'Slyvanas Windrunner', 18),('2014-05-05', 'A', 5, 'Slyvanas Windrunner', 12),
('2014-06-01', 'A', 6, 'Gelbin Mekkatorque', 17),('2014-06-06', 'A', 6, 'Gelbin Mekkatorque', 24),
('2014-07-01', 'A', 7, 'Lor''Themar Theron', 92),('2014-07-07', 'A', 7, 'Lor''Themar Theron', 18),
('2014-02-01', 'A', 8, 'Vol''Jin', 12),('2014-02-28', 'A', 8, 'Vol''Jin', 54)

SELECT customerName, customerID,
SUM(amountPaid) AS tAmountPaid, COUNT(*) AS tVisits,
SUM(CASE WHEN dateofService > DATEADD(DAY,-180,GETDATE()) THEN amountPaid ELSE 0 END) AS amountPaid180Days, SUM(CASE WHEN dateofService > DATEADD(DAY,-180,GETDATE()) THEN 1 ELSE 0 END) AS visits180Days
FROM @forumTable
GROUP BY customerName, customerID


No comments:

Post a Comment