SQLTeam.com | Weblogs | Forums

Syntax of cross apply is beyond me


#1
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,

)

#2

what's the question?


#3

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] [datetime] NULL,
[MonthTo] [datetime] 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.


#4

How is this related to CROSS APPLY?


#5

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


#6

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


#7

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.


#8

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.


#9

Right, now can you post:

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


#10

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

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