这是一个简单的问题，但我不完全明白出了什么问题。用我正在使用的方法..所以我已经回到了原来需要使用的两个表。因此，如果您能从一开始就建议如何解决这个问题，我们将不胜感激。 一个表格是案例，其中包含单个案例的详细信息，其中包含可用于计算的日期、雇用时间以及适用的费率。它有一个源 ID，它与预算表相同。每个案例都有一个源 ID。另一个表是每个日历月按源 ID 划分的预算。需要比较日期的实际收入和费率，并将它们相加并比较实际与预算。
用于计算持续时间的日期是实际开始日期到实际结束日期，但更复杂的是，只需要返回利息月份的雇用天数。因此，如果招聘开始得早于相关月份，则仅将这些日期与该月的预算进行比较。 唯一的其他要求是根据公司名称设置费率，合同费率或每日租金。但我尽量保持简单。
都是演示数据...
USE [AutoHires]
GO
/****** Object: Table [dbo].[tblBudget] Script Date: 22/01/2018 09:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblBudget](
[Ref] [int] IDENTITY(1,1) NOT NULL,
[SourceID] [int] NOT NULL,
[BudgetRevenue] [money] NULL,
[BudgetHires] [int] NULL,
[BudgetSold] [int] NULL,
[BudgetInstructions] [int] NULL,
[SourceGroup] [int] NULL,
[MonthFrom] [datetime] NULL,
[MonthTo] [datetime] NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Budget] PRIMARY KEY CLUSTERED
(
[Ref] 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
SET IDENTITY_INSERT [dbo].[tblBudget] ON
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (1, 1, 300.0000, 12, 45, 80, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEA')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (2, 2, 400.0000, 15, 50, 90, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEB')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (3, 3, 700.0000, 4, 6, 50, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEH')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (4, 4, 323.0000, 6, 18, 50, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEG')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (5, 5, 567.0000, 6, 45, 56, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEF')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (6, 6, 566.0000, 12, 56, 67, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEE')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (7, 7, 566.0000, 23, 12, 44, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCED')
INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (8, 8, 668.0000, 3, 44, 23, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEC')
SET IDENTITY_INSERT [dbo].[tblBudget] OFF
第二表案件。
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCase](
[PK_RefNo] [int] IDENTITY(1,1) NOT NULL,
[SourceID] [int] NOT NULL,
[DateInstructed] [datetime] NOT NULL,
[DateClosed] [datetime] NULL,
[Insurer_tpi] [varchar](50) NULL,
[HireDailyRate] [money] NULL,
[ActualStartDate] [datetime] NULL,
[ActualFinishDate] [datetime] NULL,
[HireContractRate] [money] NULL,
[DailyRate] [money] NULL,
[AdditionalCosts] [money] NULL,
CONSTRAINT [PK_tblCase] PRIMARY KEY CLUSTERED
(
[PK_RefNo] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tblCase] ON
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (1, 1, CAST(N'2017-10-09 00:00:00.000' AS DateTime), CAST(N'2017-12-13 00:00:00.000' AS DateTime), N'avg', 2.0000, CAST(N'2017-11-14 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), 2.0000, 1.0000, 3.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (2, 1, CAST(N'2017-01-10 00:00:00.000' AS DateTime), CAST(N'2018-01-16 00:00:00.000' AS DateTime), N'Bull', 4.0000, CAST(N'2017-01-03 00:00:00.000' AS DateTime), NULL, 4.0000, 3.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (3, 3, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'bit', 3.0000, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 5.0000, 3.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (4, 4, CAST(N'2017-12-02 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'Avast', 5.0000, CAST(N'2017-12-15 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 5.0000, 4.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (5, 5, CAST(N'2017-12-08 00:00:00.000' AS DateTime), NULL, N'Kasp', 6.0000, CAST(N'2017-12-22 00:00:00.000' AS DateTime), CAST(N'2018-01-26 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (6, 1, CAST(N'2017-12-06 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'avg', 5.0000, CAST(N'2017-10-10 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (7, 1, CAST(N'2017-09-01 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'avg', 5.0000, CAST(N'2017-11-01 00:00:00.000' AS DateTime), CAST(N'2017-12-08 00:00:00.000' AS DateTime), 5.0000, 4.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (8, 4, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-11 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 4.0000, 3.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (9, 4, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Panda', 6.0000, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-30 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (10, 5, CAST(N'2017-01-10 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'Avast', 7.0000, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2018-01-24 00:00:00.000' AS DateTime), 6.0000, 4.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (11, 8, CAST(N'2017-05-17 00:00:00.000' AS DateTime), CAST(N'2018-09-07 00:00:00.000' AS DateTime), N'Bull', 3.0000, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-26 00:00:00.000' AS DateTime), 5.0000, 3.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (12, 2, CAST(N'2017-01-16 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'bit', 5.0000, CAST(N'2017-12-16 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), 6.0000, 5.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (13, 8, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2001-03-01 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 6.0000, 1.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (14, 8, CAST(N'2017-12-12 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), N'Kasp', 5.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime), 6.0000, 3.0000, 1.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (15, 8, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-28 00:00:00.000' AS DateTime), N'Panda', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 5.0000, 3.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (16, 2, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), 5.0000, 1.0000, 2.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (17, 6, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2017-12-30 00:00:00.000' AS DateTime), 5.0000, 1.0000, 3.0000)
INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (18, 5, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 6.0000, 3.0000, 1.0000)
SET IDENTITY_INSERT [dbo].[tblCase] OFF
如果希望你理解这个问题。
也许这个
select * from tblbudget
left join
(
select
[SourceID],
hirevalue = sum((datediff(d,
(case when actualstartdate < '2017-12-01' then '2017-12-01'
else actualstartdate
end),
(case when actualfinishdate > '2017-12-31' or actualfinishdate is null then '2017-12-31'
else actualfinishdate
end)
) + 1) * (case when Insurer_tpi = 'avg' then hiredailyrate else dailyrate end) )
from tblcase
where (actualstartdate >= '2017-12-01' and actualstartdate <='2017-12-31')
or
(actualfinishdate >= '2017-12-01' and actualfinishdate <='2017-12-31')
or
(actualstartdate < '2017-12-01' and actualfinishdate > '2017-12-31')
or
(actualstartdate < '2017-12-01' and actualfinishdate is null)
group by sourceid
) cases on cases.sourceid = tblbudget.Sourceid
Ref SourceID BudgetRevenue BudgetHires BudgetSold BudgetInstructions SourceGroup MonthFrom MonthTo Name SourceID hirevalue
----------- ----------- --------------------- ----------- ----------- ------------------ ----------- ----------------------- ----------------------- -------------------------------------------------- ----------- ---------------------
1 1 300.00 12 45 80 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEA 1 306.00
2 2 400.00 15 50 90 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEB 2 44.00
3 3 700.00 4 6 50 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEH 3 45.00
4 4 323.00 6 18 50 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEG 4 253.00
5 5 567.00 6 45 56 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEF 5 209.00
6 6 566.00 12 56 67 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEE 6 30.00
7 7 566.00 23 12 44 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCED NULL NULL
8 8 668.00 3 44 23 NULL 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000 SOURCEC 8 282.00
(8 row(s) affected)