CODEWITHSUNDEEP
sql-serverstored-procedures
JohnMathew
JohnMathew

Reputation: 528

How to find out sum of dynamic columns in SQL Pivot Query

I have a stored procedure which accepts date and month as parameters that results monthly attendance sheet. And I have to find out the monthly working hours of particular employees.

CREATE TABLE [dbo].[employee] (
  [EmpID] [int] IDENTITY(100,1) NOT NULL,
  [Name] [varchar](50) NULL,
  [DOB] [date] NULL,
  [DOJ] [date] NULL,
  [Email] [varchar](50) NULL,
  [Mob] [varchar](50) NULL,
  [Address] [varchar](max) NULL,
  CONSTRAINT [PK_tbl_employee] PRIMARY KEY CLUSTERED
  (
    [EmpID] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[Attendace] (
  [EmpID] [int] NOT NULL,
  [AttendaceDate] [date] NULL,
  [WorkHours] [int] NULL,
  [AtID] [int] IDENTITY(1,1) NOT NULL,
  CONSTRAINT [PK_Attendace] PRIMARY KEY CLUSTERED
  (
    [AtID] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

Stored Procedure

ALTER procedure [dbo].[sps_AttendanceShowModified] @mon int, @year int
As begin DECLARE @cols  AS NVARCHAR(MAX)=''; DECLARE @query AS
NVARCHAR(MAX)='';

set @query =  'SELECT * from  (
     select  e.Name, a.WorkHours, DAY(a.AttendaceDate) AS d1
from Attendace a, employee e
where e.EmpID = a.EmpID and
MONTH(a.AttendaceDate) = ' + CONVERT(VARCHAR(12), @mon)+ ' AND
YEAR(a.AttendaceDate) = ' + CONVERT(VARCHAR(12), @year)+ ' ) src pivot 
(
    max(WorkHours) for d1 in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
) piv'

execute(@query) end

Upvotes: 0

Views: 96

Answers (1)

PSK
PSK

Reputation: 17943

In your are case you can add a column in your select list like following.

sum(WorkHours) over(partition by name order by (select 1)) as Total

I have created a Demo, you can have a look.

Demo Online

Your final query should look like following.

ALTER procedure [dbo].[sps_AttendanceShowModified] @mon int, @year int
As begin DECLARE @cols  AS NVARCHAR(MAX)=''; DECLARE @query AS
NVARCHAR(MAX)='';

set @query =  'SELECT name,Total,[1],[2],[3] from  (
     select  e.Name, a.WorkHours, DAY(a.AttendaceDate) AS d1, sum(WorkHours) over(partition by e.name order by (select 1)) as Total
from Attendace a, employee e
where e.EmpID = a.EmpID and
MONTH(a.AttendaceDate) = ' + CONVERT(VARCHAR(12), @mon)+ ' AND
YEAR(a.AttendaceDate) = ' + CONVERT(VARCHAR(12), @year)+ ' ) src pivot 
(
    max(WorkHours) for d1 in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
) piv'

execute(@query) end

Note: You need to include other columns in your select.

Upvotes: 2

Related Questions