September 4, 2015 at 9:20 am
I have a dataset that contains all the pay records for the current year. I'd like to add a year-to-date and a month-to-date and hopefully not have to sum them separately and then join the results. Here is my data:
CREATE TABLE [dbo].[_Test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[CheckDate] [date] NULL,
[Units] [decimal](19, 5) NULL,
[Rate] [decimal](19, 5) NULL,
[Amount] [decimal](19, 5) NULL,
[TaxAmount] [decimal](19, 5) NULL,
[DeductAmount] [decimal](19, 5) NULL,
[BenefitAmount] [decimal](19, 5) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[_Test] ON
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (1, N'1', CAST(N'2015-01-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (2, N'2', CAST(N'2015-01-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (3, N'1', CAST(N'2015-02-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (4, N'2', CAST(N'2015-02-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (5, N'1', CAST(N'2015-03-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (6, N'2', CAST(N'2015-03-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (7, N'1', CAST(N'2015-04-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (8, N'2', CAST(N'2015-04-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (9, N'1', CAST(N'2015-05-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (10, N'2', CAST(N'2015-05-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (11, N'1', CAST(N'2015-06-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (12, N'2', CAST(N'2015-06-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (13, N'1', CAST(N'2015-07-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (14, N'2', CAST(N'2015-07-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (15, N'1', CAST(N'2015-08-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (16, N'2', CAST(N'2015-08-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (17, N'1', CAST(N'2015-09-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (18, N'2', CAST(N'2015-09-01' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (19, N'1', CAST(N'2015-01-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (20, N'2', CAST(N'2015-01-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (21, N'1', CAST(N'2015-02-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (22, N'2', CAST(N'2015-02-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (23, N'1', CAST(N'2015-03-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (24, N'2', CAST(N'2015-03-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (25, N'1', CAST(N'2015-04-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (26, N'2', CAST(N'2015-04-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (27, N'1', CAST(N'2015-05-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (28, N'2', CAST(N'2015-05-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (29, N'1', CAST(N'2015-06-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (30, N'2', CAST(N'2015-06-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (31, N'1', CAST(N'2015-07-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (32, N'1', CAST(N'2015-08-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(10.00000 AS Decimal(19, 5)), CAST(400.00000 AS Decimal(19, 5)), CAST(100.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)), CAST(50.00000 AS Decimal(19, 5)))
GO
INSERT [dbo].[_Test] ([Id], [EmployeeID], [CheckDate], [Units], [Rate], [Amount], [TaxAmount], [DeductAmount], [BenefitAmount]) VALUES (33, N'2', CAST(N'2015-08-15' AS Date), CAST(40.00000 AS Decimal(19, 5)), CAST(12.00000 AS Decimal(19, 5)), CAST(480.00000 AS Decimal(19, 5)), CAST(120.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)), CAST(60.00000 AS Decimal(19, 5)))
GO
SET IDENTITY_INSERT [dbo].[_Test] OFF
GO
I want to return a paycheck's data along with the month to date and year to date values included given a single date. I'm thinking something like this might work, but i'm having trouble setting up my window.
select employeeid, CheckDate, Units
sum(Units) over (Partition by employeeid, CheckDate
ORDER BY CheckDate
ROWS between unbounded preceding and current row) [MTD Units],
sum(Units) over (Partition by EmployeeId, CheckDate
ORDER BY CheckDate
ROWS between unbounded preceding and current row) [YTD Units],
, Rate, Amount,
sum(Amount) over (Partition by employeeid, CheckDate
ORDER BY CheckDate
ROWS between unbounded preceding and current row) [MTD Amount],
sum(Amount) over (Partition by EmployeeId, CheckDate
ORDER BY CheckDate
ROWS between unbounded preceding and current row) [YTD Amount],
from _Test
where CheckDate = '6/15/15'
The reason I was thinking using the Over would work, because if i take off my WHERE clause and do the following, I do get a sum of all the items on every line.
select employeeid, CheckDate, Units
, Rate, Amount
,sum(Amount) over (Partition byEmployeeId) [All]
from _Test
empidCheckDateUnitsRateAmountAll
12015-01-0140.0010.00400.006800.00
12015-02-0140.0010.00400.006800.00
12015-03-0140.0010.00400.006800.00
22015-01-1540.0012.00480.007680.00
22015-02-1540.0012.00480.007680.00
22015-03-1540.0012.00480.007680.00
I was thinking I could restrict it with a window.
My desired output is as such:
IdEmpIDCheckDateUnitsMTD UnitsYTD UnitsRateAmountMTD AmountYTD AmountTaxAmountDeductAmountBenefitAmount
2912015-06-1540.0080.00520.0010.00400.00800.005200.00100.0050.0050.00
3022015-06-1540.0080.00520.0012.00480.00960.006240.00120.0060.0060.00
Any ideas?
Thanks,
Brian
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply