Questions tagged [cross-apply]
The cross-apply tag has no usage guidance.
0
votes
1answer
37 views
Refactor SQL to left join on TVF requiring input variable
I would like to join two tables and a table-valued function. The TVF take a date as input variable.
The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible
...
7
votes
1answer
108 views
How to get cross apply to operate row by row on a view?
We have a view that is optimized for single item queries (200ms no parallelism):
select *
from OptimizedForSingleObjectIdView e2i
where ObjectId = 3374700
It also works on small sets of ...
2
votes
1answer
154 views
Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table
Given the following query:
SELECT
p.ProductName,
CASE
WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
THEN
1
ELSE
0
END
...
1
vote
1answer
58 views
is there a way to improve this query by not re calculating functions? cross apply?
when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.
this is the picture from the ...
5
votes
3answers
1k views
Using CROSS APPLY OPENJSON causes Azure to hang
I have a table with around 8 million rows with a schema of:
CREATE TABLE [dbo].[Documents](
[Id] [uniqueidentifier] NOT NULL,
[RemoteId] [int] NOT NULL,
[Json] [nvarchar](max) NULL,
[...
4
votes
1answer
156 views
Convert columns into rows for use with indexed/materalized view
I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
3
votes
1answer
327 views
Speed Up Cross Apply Without Index Hint
I have a very small table with 12 rows in it that can be created with the following statement:
CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
...
0
votes
0answers
215 views
Using clustered columnstore indexes in conjunction with non-clustered columnstore indexes
Table A has approximately 500 million records in it and is of the form similar to the following
| ID | FirstName | LastName | MiddleName |
------------------------------------------
| 1 | John |...
3
votes
2answers
149 views
Return batches of rows based on one row
Say I have a table like this in SQL Server 2008:
id | name | qty
-------------------
1 | john | 1
2 | bill | 3
3 | mary | 2
4 | jill | 5
I would like to query this ...
6
votes
3answers
1k views
How does this derived table with cross apply work?
I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is ...
2
votes
1answer
1k views
Performance improvement Outer Apply
I have a query which runs little slower.
SELECT b.BreakdownClassificationId,
k.IsinCode,
k.SedolCode,
ClassificationDate,
NAME,
InstrumentType,
...
5
votes
1answer
4k views
Using CROSS APPLY with GROUP BY and TOP 1 with duplicate data
I have a table that contains Status information about Product items over time. Each row has a Modified DATETIME. I want to get the latest Status row, using the MODIFIED field, for each ProductNumber ...
1
vote
1answer
621 views
Issue with SUM OVER PARTITION BY in conjunction with GROUP BY
I am getting a typical group by error that one would see when a field is not in a group by or an aggregate in the select.
Column 'v.EDTAmount' is invalid in the select list because it is not ...
4
votes
1answer
1k views
While using Cross Apply, can I force “empty” records to appear?
I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS ...
1
vote
1answer
94 views
Where and how to add selective totals in SSRS
I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to ...
1
vote
2answers
92 views
TSQL help my feeble mind grasp Pivot/Unpivot on two columns
I've been trying to wrap my feeble mind around this one for some time, but I am having difficulty. I have looked at a few other examples like this SE.DBA answer, but I am having difficulty applying it ...
4
votes
1answer
129 views
Does the apply operator use row by row processing?
I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it ...
0
votes
1answer
1k views
CROSS APPLY query with CASE and running totals [closed]
I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after ...
0
votes
1answer
51 views
How to use reference to another table in aggregate function
I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:
SELECT [GroupId] = cr.[GroupId],
[...
7
votes
1answer
731 views
Cross Apply Fails When DateTime Added
In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
1
vote
2answers
2k views
How to keep row ordering with DISTINCT command or how to add row number to cross apply
I have problem that after distinct, order direction of Variable column change.
Here is example
Data Source
DECLARE @Table TABLE
(
FirstName NVARCHAR(255),
LastName NVARCHAR(255),
...
3
votes
1answer
239 views
Translate query using APPLY to run under compatibility level 80
I want to use the following query:
SELECT
SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
...
8
votes
2answers
178 views
Most efficient way to generate a diff
I have a table in SQL server that looks like this:
Id |Version |Name |date |fieldA |fieldB ..|fieldZ
1 |1 |Foo |20120101|23 | ..|25334123
2 |2 |Foo ...
6
votes
3answers
8k views
No advantage of using Cross Apply or CTE over inline sub-query
I came across a query like this:
SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i
I changed it to following
;WITH cte_count
AS
(
SELECT COUNT(1) c, OrderId FROM ...
3
votes
1answer
1k views
Insert a range of numbers from a call to sp_sequence_get_range
Given the following code:
CREATE SEQUENCE dbo.NextTestId AS [bigint]
START WITH 10 INCREMENT BY 2 NO CACHE
GO
DECLARE
@variableNumberOfIdsNeeded INT = 7, -- This will change for each call
@...
5
votes
1answer
11k views
CROSS APPLY on Scalar function
I have this:
SELECT
A
,B
,dbo.Func(C)
,dbo.Func(D)
,dbo.Func(E)
,F
FROM abcdef
WHERE
0 = dbo.Func(C) + dbo.Func(D)
I have read that this is not good practice because the function is called million ...
0
votes
1answer
2k views
Pivoting some row data into columns (SQL Server 2008 R2)
Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another.
Full-Disclosure: I did ...
29
votes
2answers
55k views
Outer Apply vs Left Join Performance
I am Using SQL SERVER 2008 R2
I just came across APPLY in SQL and loved how it solves query problems for so many cases,
Many of the tables I was using 2 left join to get the result, I was able to ...
4
votes
1answer
566 views
How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
3
votes
1answer
876 views
Cross Apply without a function
I would like to run a query very similar to this on one of my production databases:
-- Setup the table (Already there in my real scenario)
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
...
0
votes
3answers
137 views
How to convert query results into groups of common results
I have a query that returns results like this:
MyValue MyGroup
_______________|_______________
56 | I
_______________|_______________
12 | I
...
5
votes
1answer
970 views
Build a three table join with a recusive table in the middle?
I have three relevant tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key
Part part number
PartGroupID Foreign key
sample data:
1 ...
3
votes
1answer
2k views
CROSS APPLY-ing a parameterised function
Given the following definitions:
CREATE TABLE MyTable
(
col1 int,
col2 int,
col3 int,
col4 int
)
CREATE FUNCTION fn_MyFunction
(
@Param1 int,
@Param2 int
)
RETURNS TABLE AS ...
