Home Forums SQL Server 2008 SQL Server Newbies How to get a sum of values in a table joind to table of sum values RE: How to get a sum of values in a table joind to table of sum values

  • spaghettidba - Thursday, June 14, 2018 7:25 AM

    Please post table scripts, sample data and expected output. It's easy, promised 🙂
    See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

    The first table is the cases. the second is the budget , i need to amalgamation of the two

    Cases
    USE [AutoHires]
    GO
    /****** Object: Table [dbo].[tblCase]  Script Date: 19/01/2018 15:58:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    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 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)
    SET IDENTITY_INSERT [dbo].[tblCase] OFF

    Budget

    USE [AutoHires]
    GO
    /****** Object: Table [dbo].[tblBudget]  Script Date: 19/01/2018 15:54:51 ******/
    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 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