Stack Overflow
Stack Overflow

Questions tagged [cross-apply]

The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard

358 questions
0
votes
3 answers

SQL Server cross apply performance issue

I have a view as below: CREATE VIEW V1 AS SELECT T1.Col1, F1.Col1, T1.Col2, T2.Col2... FROM T1 INNER JOIN T2 ON T1.Col2 = T2.Col1 CROSS APPLY UDF(T1.Col1, T2.Col2) F1 The inner join returns million…
Sathishkumar Karunamoorthy
  • 1
  • 2
0
votes
3 answers

Join One Table with Other Table for Every Row Type

My First Table and its data is : DECLARE @TempTableA TABLE (FinYearVal VARCHAR(9)) FinYearVal ---------- 2007-2008 2008-2009 2009-2010 2010-2011 2011-2012 2012-2013 2013-2014 2014-2015 Then I have another table with data as : DECLARE @TempTableB…
Dinesh Saxena
  • 71
  • 1
  • 1
  • 8
0
votes
2 answers

Writing a TSQL query to idenfity overlapping date rows

I have a table with multiple rows per staff person. Each of these rows has staff_id, start_date, and end_date. Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the…
salvationishere
  • 3,461
  • 29
  • 104
  • 143
0
votes
2 answers

Indexing WHERE clause of OUTER/CROSS APPLY with INCLUDE

I have read that indexing columns should be used when using a WHERE clause to restrict returned records (i.e. SELECT * from Table1 WHERE Field1 = 'abc'). Does this rule apply when using APPLY, as in SELECT t.Field1,x.Field2 from Table1 t OUTER…
Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35
0
votes
1 answer

SQL Server Cross Apply on itself + Cross Apply performance issues

I have the following: CREATE TABLE #MASTER_POSANDTRANS (ID CHAR(30), QUANTITY INT, START_OR_TRADE_DATE DATE) VALUES ('APPLES ','150000','20150501'),…
buellboy
  • 95
  • 6
0
votes
1 answer

Group by column value and get the difference by summing up two fields

I have a table in the format given in the below image as Database Table. From that table i want to generate a report. The expected format is given. Help me out with the query.
Selvi Moies
  • 75
  • 8
0
votes
2 answers

Inner join and cross apply. How does it get evaluated?

If Inner join is: For each row in the left table, find row in the right table where the condition is met. What's is cross apply? I have read that it's just inner join which gets evaluated row by row, but isn't inner join also evaluated row by row?…
Mike Smith
  • 139
  • 2
  • 2
  • 12
0
votes
0 answers

SQL Server, cross-apply, and DISTINCT

I had earlier problem with CROSS APPLY, which solved nicely (Using CROSS APPLY). Here is another part. I want to get one newest CarData row (all columns!) for all those cars which have new line within 24 hours. As I assume, first SELECT should get…
user3608009
  • 21
  • 1
  • 4
0
votes
2 answers

Using CROSS APPLY

I have table Car with car ID's (smallint), and another table with events related with each car. Now I want to get latest event for cars selected by certain criteria, but this does not seem to work. When I have query like this to get the latest…
user3608009
  • 21
  • 1
  • 4
0
votes
1 answer

Unpivot dynamic table columns into key value rows

The problem that I need to resolve is data transfer from one table with many dynamic fields into other structured key value table. The first table comes from a data export from another system, and has the following structure ( it can have any…
Krste A.
  • 3
  • 3
0
votes
1 answer

SQL Using Cross Apply makes the query to run very slow

The below query takes 35sec to complete, so the front end app throws 'Timeout expired' error, I fixed by increasing CommandTimeOut, but my reviewer didn't accept the fix ,because fix has to be done in sql query. Below is My sql query select * from…
Selva
  • 1,310
  • 2
  • 14
  • 31
0
votes
0 answers

Apply operator in SQL and report viewer

I have a stored procedure that I'm trying to call from report viewer and I'm getting the error below Exception has been thrown by the target of an invocation. Failed to enable constraints. One or more rows contain values violating…
Wendie
  • 84
  • 7
0
votes
1 answer

Cross apply along with additional columns

I have Two tables 1. #SetValuesTable 2. #labelTempTab Data in #SetValuesTable look like this : MNUM la1 la2 la3 la4 PropertyType 12 1 0 2 s 13 4 0 5 7 p Data in #labelTempTab…
BumbleBee
  • 10,429
  • 20
  • 78
  • 123
0
votes
1 answer

Applying XML cross apply to get data from XML file

I am applying XML cross apply to extract data from XML project file having multiple nodes by using Stored procedure. Stored Procedure is as follow INSERT INTO UCDetails SELECT Usecase.value('@UserID','VARCHAR(100)') AS UCId, --ATTRIBUTE…
BILAL HAIDER
  • 15
  • 4
0
votes
2 answers

Combine Multiple records per Foreign Key

I have a somewhat tricky table structure that was inherited from way legacy. I have a table with about 4 columns that matter. DayNight Cust_Code Name Phone Counter D ABC0111 Marty aaaaa 1 D …
DidIReallyWriteThat
  • 1,033
  • 1
  • 10
  • 39
1 2 3
…
23 24