Syntax of cross apply is beyond me

ISeeWindings · January 12, 2018, 11:05am

cross apply
(
select case when Date1 between e.MonthFrom and e.MonthTo then 1 else 0 end as ActualNoInstr,
       case when Date2 between e.MonthFrom and e.MonthTo then 1 else 0 end as ActualNoStart,
       case when Date3 between e.MonthFrom and e.MonthTo then DatDiff(d,Date1, Date2d) else 0 end as Days,

)

gbritton · January 12, 2018, 3:15pm

what's the question?

ISeeWindings · January 18, 2018, 5:15pm

I have a table like this :
CREATE TABLE [dbo].[tblBudgetPrototype](
[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] 2019-01-03 17:01:03 NULL,
[MonthTo] 2019-01-03 17:01:03 NULL,
[Name] nvarchar NULL,

And also a Table of cases linked to this table by SourceID.
This is like a Fact table, the facts that are important are dates, of which there are 4, datecreated, datestarted, datestopped, and datefinished. Also Rate in each row.

From the datestarted to the datestopped, the difference is an integer of the number of days to be charged for. At a rate that is in the fact table. For each row in the cases with a datestarted, the amount of income is the number of days times the rate. From this the total revenue for each Source can be calculated. This needs to be in the same dataset as the BudgetSold, for comaprison. Whycantaddup

Yet the Budgets Sold can't add up right. I used dbForge SQL Query builder to make the query.

SELECT
OpenClosedCases.DateInstructed
,OpenClosedCases.PK_RefNo
,OpenClosedCases.FK_HireCarID
,OpenClosedCases.HireDailyRate
,OpenClosedCases.HireCost
,OpenClosedCases.actualfinishdate
,OpenClosedCases.FK_ClientCarGroupID
,OpenClosedCases.FK_SubContractorID
,OpenClosedCases.actualstartdate
,OpenClosedCases.SourceID
,OpenClosedCases.ContractRate
,tblBudgetPrototype.SourceID
,tblBudgetPrototype.BudgetRevenue
,tblBudgetPrototype.BudgetHires
,tblBudgetPrototype.BudgetSold
,tblBudgetPrototype.BudgetInstructions
,tblBudgetPrototype.MonthFrom
,tblBudgetPrototype.MonthTo
,tblBudgetPrototype.Name
FROM dbo.OpenClosedCases
INNER JOIN dbo.tblBudgetPrototype
ON OpenClosedCases.SourceID = tblBudgetPrototype.SourceID
WHERE OpenClosedCases.DateInstructed BETWEEN tblBudgetPrototype.MonthFrom AND tblBudgetPrototype.MonthTo
OR OpenClosedCases.actualstartdate BETWEEN tblBudgetPrototype.MonthFrom AND tblBudgetPrototype.MonthTo
GROUP BY OpenClosedCases.SourceID
,OpenClosedCases.DateInstructed
,OpenClosedCases.PK_RefNo
,OpenClosedCases.FK_HireCarID
,OpenClosedCases.HireDailyRate
,OpenClosedCases.HireCost
,OpenClosedCases.actualfinishdate
,OpenClosedCases.FK_ClientCarGroupID
,OpenClosedCases.FK_SubContractorID
,OpenClosedCases.actualstartdate
,OpenClosedCases.ContractRate
,tblBudgetPrototype.SourceID
,tblBudgetPrototype.BudgetRevenue
,tblBudgetPrototype.BudgetHires
,tblBudgetPrototype.BudgetSold
,tblBudgetPrototype.BudgetInstructions
,tblBudgetPrototype.MonthFrom
,tblBudgetPrototype.MonthTo
,tblBudgetPrototype.Name
ORDER BY OpenClosedCases.PK_RefNo, OpenClosedCases.SourceID
Please help me with a solution, if you got time.

gbritton · January 18, 2018, 5:27pm

How is this related to CROSS APPLY?

ISeeWindings · January 18, 2018, 8:57pm

I tried to do it with a cross apply with the case statements, but kept getting syntax errors, The problem was the adding "if" then, switch, inside case statement, to catch all variations of start stop dates, nulls and errors in records, such as when a case has a start date but no stop date, and it's already been closed. I need the length of hire inside a month of interest, and multiply that by a one of two rates, dependent on SourceID. Add that up for each SourceID, and compare against the Budget table values. With the cross aplly I actually got the adding up at the final stage to work, predicatably, in that it wasn't adding invisible numbers. It's not adding duplicates, cause there are no duplicates shown, in dbSQL Query results. Thanks

gbritton · January 18, 2018, 9:02pm

OK I see, now, using the data you posted, post your expected results

gbritton · January 18, 2018, 9:13pm

Think of CROSS APPLY as a kind of INNER JOIN but without an ON predicate. It's real purpose is to easily use table valued functions, though I use it for other reasons too. Sometimes it reads better than inner join, sometimes not.

ISeeWindings · January 19, 2018, 10:41am

Thia ia used the cross apply, the result is that it adds up correctly, but ther actual calculations are wildly inaccurate as I can't apply any logic to it.

gbritton · January 19, 2018, 1:44pm

Right, now can you post:

CREATE TABLE statements for the tables involved
INSERT INTO statements to populate the tables
desired results using the above

ISeeWindings · January 19, 2018, 4:10pm

he 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