Need T-SQL revised to show 3rd column

Question

Need T-SQL revised to show 3rd column

Express12

SSCoach

Points: 18514
More actions
August 25, 2021 at 6:12 pm

#3923989

Each year we decommission "X" number of SQL Servers and track these in a table. I need underlying SQL to add to dataset in an SSRS report reflecting SQL License cost savings since inception.
(DDL and INSERT Statements at bottom to quickly create this test env)
Assuming my starting point was $137,000.00 for all SQL Licenses, I need T-SQL to render the running Total SQL License Cost... For example:
in year 2016, 1 server was decommissioned saving $12k in SQL License cost and reducing the total SQL License cost down to $125k
in year 2017, 2 servers were decommissioned saving $30k in SQL License cost and reducing the total SQL License cost down to $95k
in year 2018, 3 servers were decommissioned saving $61k in SQL License cost and reducing the total SQL License cost down to $34k
Table Values:
ServerABC 2019/08/29 10000.00
ServerXYZ 2019/10/15 3000.00
ServerRRR 2019/12/19 3000.00
Server333 2019/01/02 18000.00
Server777 2018/10/24 40000.00
Server222 2018/08/15 18000.00
Server999 2018/08/10 3000.00
Server111 2017/09/26 12000.00
ServerMMM 2017/12/20 18000.00
ServerQQQ 2016/12/06 12000.00
This T-SQL gives me the annual savings... but I need a 3rd column returned showing the running SQL License cost like $125k for 2016, $95k for 2017, etc
SELECT SUBSTRING(Decommission_Date, 1, 4) as DecommYYYY, sum(License_Cost) As 'Monthly SQL License Savings'
FROM [dbo].[MY_DBServer_List] (NOLOCK)
GROUP BY SUBSTRING(Decommission_Date, 1, 4) HAVING SUBSTRING(Decommission_Date, 1, 4) > '1'
ORDER BY DecommYYYY DESC
-- SETUP TABLE and POPULATE ROWS:
CREATE TABLE [dbo].[MY_DBServer_List]
([ServerName] [nvarchar](50) NOT NULL, -- (PK Unique Value)
[Decommission_Date] [nvarchar](30) NULL, -- (EG: stored as 2019/03/21)
[License_Cost] [money] NULL ) -- ($$ Cost for this SQL Server License)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('ServerABC', '2019/08/29', 10000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('ServerXYZ', '2019/10/15', 3000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('ServerRRR', '2019/12/19', 3000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('Server333', '2019/01/02', 18000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('Server777', '2018/10/24', 40000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('Server222', '2018/08/15', 18000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('Server999', '2018/08/10', 3000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('Server111', '2017/09/26', 12000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('ServerMMM', '2017/12/20', 18000)
INSERT INTO [dbo].[MY_DBServer_List] ([ServerName], [Decommission_Date], [License_Cost]) VALUES ('ServerQQQ', '2016/12/06', 12000)
BT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2072 More actions · Answer 1

It appears you're looking for $137,000 less the running total of yearly license costs. Also, the 'money' data type is imprecise and has serious issues so this uses decimal(14, 2) instead.

drop table if exists #MY_DBServer_List;
go
create table #MY_DBServer_List
([ServerName] [nvarchar](50) NOT NULL, -- (PK Unique Value)
[Decommission_Date] [nvarchar](30) NULL, -- (EG: stored as 2019/03/21)
[License_Cost] decimal(14, 2) NULL ) -- ($$ Cost for this SQL Server License)

insert into #MY_DBServer_List([ServerName], [Decommission_Date], [License_Cost]) values
('ServerABC', '2019/08/29', 10000),
('ServerXYZ', '2019/10/15', 3000),
('ServerRRR', '2019/12/19', 3000),
('Server333', '2019/01/02', 18000),
('Server777', '2018/10/24', 40000),
('Server222', '2018/08/15', 18000),
('Server999', '2018/08/10', 3000),
('Server111', '2017/09/26', 12000),
('ServerMMM', '2017/12/20', 18000),
('ServerQQQ', '2016/12/06', 12000);

with sum_cte as (
    select v.DecommYYYY, sum([License_Cost]) [Monthly SQL License]
    from #MY_DBServer_List
         cross apply (values (substring([Decommission_Date], 1, 4))) v(DecommYYYY)
    group by v.DecommYYYY)
select *, 137000-sum([Monthly SQL License]) over (order by DecommYYYY) as [3rd Column]
from sum_cte
order by DecommYYYY;

DecommYYYY      Monthly SQL License      3rd Column
2016            12000.00                 125000.00
2017            30000.00                 95000.00
2018            61000.00                 34000.00
2019            34000.00                 0.00

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Express12 SSCoach Points: 18514 More actions · Answer 2

Steve, this looks perfect. Thank you for the FAST reply and a working solution - very much appreciated

BT