January 28, 2016 at 11:54 pm
Hi I have a following table
CREATE TABLE #Test
(
Id int,
Principal float,
Interest float,
CashFlowDate date,
)
INSERT INTO #Test
VALUES (1,200,50,'2016-01-01'),
(2,300,0,'2016-01-02'),
(3,0,100,'2016-01-03'),
(4,200,300,'2016-01-05'),
(5,100,200,'2016-01-06')
select * from #Test
I had used UNION to get principal and interest in separate row using below query, the query has different clause and date range is different for principal and interest
SELECT Id,
Principal AS Amount,
CashFlowDate
FROM #Test
WHERE (Principal > 0 ) AND (CashFlowDate between '2016-01-01' AND '2016-01-05')
UNION ALL
SELECT Id,
Interest AS Amount,
CashFlowDate
FROM #Test
WHERE (Interest > 0 ) AND (CashFlowDate between '2016-01-02' AND '2016-01-05')
DROP TABLE #Test
As from the above code there will be 2 table scan one for principal and other for interest, i am trying to get the data in 1 table scan. Below query will bring that data without WHERE CLAUSE
SELECT Id,
CASE a WHEN 1 THEN Principal ELSE Interest END AS Amount,
CashFlowDate
FROM #Test,(SELECT 1 a UNION ALL SELECT 2) b
My problem is in the above query i am not able to implement the WHERE clause, i need a way to implement that. Is there any solution.
please let me know if you need any further info
Thanks
January 29, 2016 at 5:27 am
Experts any help?
January 29, 2016 at 5:43 am
SELECT
t.Id,
x.Amount,
t.CashFlowDate
FROM #Test t
CROSS APPLY (
SELECT [Amount] = CASE
WHEN t.CashFlowDate between '2016-01-01' AND '2016-01-05' THEN t.Principal ELSE NULL END
UNION ALL
SELECT [Amount] = CASE
WHEN t.CashFlowDate between '2016-01-02' AND '2016-01-05' THEN t.Interest ELSE NULL END
) x
WHERE x.Amount > 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2016 at 5:55 am
You could use row constructors for this too:
SELECT
t.Id,
x.Amount,
t.CashFlowDate
FROM #Test t
CROSS APPLY (
VALUES
(CASE WHEN t.CashFlowDate between '2016-01-01' AND '2016-01-05' THEN t.Principal ELSE NULL END),
(CASE WHEN t.CashFlowDate between '2016-01-02' AND '2016-01-05' THEN t.Interest ELSE NULL END)
) x (Amount)
WHERE x.Amount > 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2016 at 9:08 am
Yet another option. However, I'm not sure which of the proposed versions would be the best option.
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND ((CashFlowDate between '2016-01-01' AND '2016-01-05' AND AmtType = 'Principal')
OR (CashFlowDate between '2016-01-02' AND '2016-01-05' AND AmtType = 'Interest'));
January 29, 2016 at 9:16 am
Luis Cazares (1/29/2016)
Yet another option. However, I'm not sure which of the proposed versions would be the best option.
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND ((CashFlowDate between '2016-01-01' AND '2016-01-05' AND AmtType = 'Principal')
OR (CashFlowDate between '2016-01-02' AND '2016-01-05' AND AmtType = 'Interest'));
Me neither - but they all read the #Test table only once. The output doesn't distinguish between the sources for [Amount] - but that might not matter.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2016 at 12:11 pm
Luis Cazares (1/29/2016)
Yet another option. However, I'm not sure which of the proposed versions would be the best option.
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND ((CashFlowDate between '2016-01-01' AND '2016-01-05' AND AmtType = 'Principal')
OR (CashFlowDate between '2016-01-02' AND '2016-01-05' AND AmtType = 'Interest'));
I *think* this can be simplified to:
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND CashFlowDate between '2016-01-01' AND '2016-01-05';
January 29, 2016 at 12:27 pm
Hugo Kornelis (1/29/2016)
Luis Cazares (1/29/2016)
Yet another option. However, I'm not sure which of the proposed versions would be the best option.
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND ((CashFlowDate between '2016-01-01' AND '2016-01-05' AND AmtType = 'Principal')
OR (CashFlowDate between '2016-01-02' AND '2016-01-05' AND AmtType = 'Interest'));
I *think* this can be simplified to:
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND CashFlowDate between '2016-01-01' AND '2016-01-05';
The date ranges are different, I also had the same idea until I compared the results.
It could probably be simplified to this:
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE Amount > 0
AND CashFlowDate BETWEEN CASE WHEN AmtType = 'Principal' THEN '2016-01-01' ELSE '2016-01-02' END AND '2016-01-05';
January 29, 2016 at 12:30 pm
Oops. Checked and double checked the dates multiple times and still overlooked the difference.
My bad.
January 29, 2016 at 10:06 pm
Thank you very much for your response, i will test n reply back after weekends 🙂
January 31, 2016 at 11:44 pm
Thanks chris, Luis for the solution.
This is just the scenario i created, In production there is no temporary table #Test only physical table which consists of 2 million data. This 2 million data is a stage table which i have to transform using SSIS package dividing the principal and interest amount from single row to 2 rows.
In Luis solution i can identify the amount type, which i forget to mention(Chris pointed that :-))
I have one question if there is LEFT join happens between the #Test table and another table based on join condition ID, after that CROSS apply. what will be the sequence of operation, i am listing out the sequence please correct me if it is wrong.
1.Left join operation applied based on ID
2.Resultset from step1 is used for CROSS apply.
3.then the date range filter applies.
Is it correct or It is the date range filter applies first and then the rest of the operations.
Thanks
February 1, 2016 at 1:29 am
SQL006 (1/31/2016)
Thanks chris, Luis for the solution.This is just the scenario i created, In production there is no temporary table #Test only physical table which consists of 2 million data. This 2 million data is a stage table which i have to transform using SSIS package dividing the principal and interest amount from single row to 2 rows.
In Luis solution i can identify the amount type, which i forget to mention(Chris pointed that :-))
I have one question if there is LEFT join happens between the #Test table and another table based on join condition ID, after that CROSS apply. what will be the sequence of operation, i am listing out the sequence please correct me if it is wrong.
1.Left join operation applied based on ID
2.Resultset from step1 is used for CROSS apply.
3.then the date range filter applies.
Is it correct or It is the date range filter applies first and then the rest of the operations.
Thanks
The order in which SQL Server performs these operations may not bear any resemblance to the order in which they appear in your query. Can you post this new query so folks have a better idea of what you are trying to do?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 1, 2016 at 1:06 pm
SQL006 (1/31/2016)
Thanks chris, Luis for the solution.This is just the scenario i created, In production there is no temporary table #Test only physical table which consists of 2 million data. This 2 million data is a stage table which i have to transform using SSIS package dividing the principal and interest amount from single row to 2 rows.
In Luis solution i can identify the amount type, which i forget to mention(Chris pointed that :-))
I have one question if there is LEFT join happens between the #Test table and another table based on join condition ID, after that CROSS apply. what will be the sequence of operation, i am listing out the sequence please correct me if it is wrong.
1.Left join operation applied based on ID
2.Resultset from step1 is used for CROSS apply.
3.then the date range filter applies.
Is it correct or It is the date range filter applies first and then the rest of the operations.
Thanks
In addition to what Chris said:
The only way to know for sure in what order SQL Server evaluates a query is to look at the execution plan. You can do so by running the query in SSMS with the option to include the actual execution plan enabled. But note that there is no guarantee that the same plan will be used everytime; SQL Server will switch to a different plan if it thinks that that would be more efficient.
If, for whatever reason, you want or need an explanation of an execution plan, then first run the query in SSMS as explained above, right-click in an empty area of the plan pane, and choose the "save as" option. This will create a .sqlplan file which is the XML representation of the plan. If you attach that to a post, others can download it and look at your plan.
Be aware that the plan contains table and index names. Constant values used in the query and sometimes parameter values are included as well. Actual row data is not returned, though some of the metrics can reveal information about your data -e.g. if your query filters for FirstName = 'Hugo' and the plan shows no rows, I can deduct that I am not in your database. Just adding this to make sure that you don't accidentally disclose sensitive information.
February 2, 2016 at 7:24 pm
SQL006 (1/31/2016)
Thanks chris, Luis for the solution.This is just the scenario i created, In production there is no temporary table #Test only physical table which consists of 2 million data. This 2 million data is a stage table which i have to transform using SSIS package dividing the principal and interest amount from single row to 2 rows.
In Luis solution i can identify the amount type, which i forget to mention(Chris pointed that :-))
I have one question if there is LEFT join happens between the #Test table and another table based on join condition ID, after that CROSS apply. what will be the sequence of operation, i am listing out the sequence please correct me if it is wrong.
1.Left join operation applied based on ID
2.Resultset from step1 is used for CROSS apply.
3.then the date range filter applies.
Is it correct or It is the date range filter applies first and then the rest of the operations.
Thanks
Are you trying to filter the results further after joining to another table? Not sure on your worry about the order of operations. If you are joining a ID in one table to a FK in another you will have a one to many mappings potentially. In the example given , Id 4 matches both on (principal and date range) AND on (interest and its date range). So ID 4 could appear 1:n for the both the conditions --> 1:2n times. You could add further conditions on the join to prevent this if that is what you are after.
Taking on Chris's solution ::
SELECT
t.Id,
x.Amount,
t.CashFlowDate,
CASE WHEN x.amount=t.principal then 'principal' else 'interest' end as amountType /* can join on this column as well */
FROM #Test t
CROSS APPLY (
VALUES
(CASE WHEN t.CashFlowDate between '2016-01-01' AND '2016-01-05' THEN t.Principal ELSE NULL END),
(CASE WHEN t.CashFlowDate between '2016-01-02' AND '2016-01-05' THEN t.Interest ELSE NULL END)
) x (Amount)
WHERE x.Amount > 0
Let us know
----------------------------------------------------
February 3, 2016 at 5:27 am
Thank you guys for your valuable inputs. I checked the execution plan for the UNION and the CROSS APPLY query, i am surprised that the UNION query is still faster than CROSS APPLY query by 1-2 seconds,even though in UNION query it accessing the table twice in comparison to CROSS APPLY where it accessing the table only once.
For the execution order of query i referred the itzik ben-gan logical order processing.
Thanks for all your suggestions.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply