1,168 questions
2
votes
2
answers
186
views
Join on one column and if null join another column
I need some help handling null columns in joins. I have three tables (below are just examples) Map, Employee and Region. Primary table is Map where I join Employee table on the MapID column to get ...
- 590
1
vote
2
answers
128
views
Pivot multiple columns [duplicate]
I have a vertical table that end users want converted to a horizontal table with some groupings. I'm writing this in SQL Server 2019.
My table looks like this:
PKID
cID
vID
vDate
1
2
81
1996-04-04
2
...
- 23
6
votes
2
answers
133
views
Parse multiple JSON items from a table cell
My table in SQL Server with an nvarchar(max) column holds multiple JSON arrays in each cell. I am trying to parse the 'result' items into separate rows.
JSON array:
[
{"successful":true,
...
-1
votes
1
answer
70
views
Parsing date & time from column in SQL
I've got a column that stores text and I'm trying to find a way to pull the datetime from the field. I've tried to use a combination of SUBSTRING,CHARINDEX, and LEN to pull the datetime from the ...
- 1,070
3
votes
3
answers
151
views
Find dates common in different rows
I have a table which has 3 columns:
task_name -- data type is varchar(50)
start_date -- data type is date
end_date -- data type is date
I want to list the dates which fall within the range (between ...
- 1,729
0
votes
0
answers
76
views
Last Synch Date of Replicated Tables on SQL Server
This question is about SQL Server replication.
I am running SQL Server 2019 v15.0.4420.2 and I get a large number of replicated tables from outside the organisation.
What I am looking for is a SQL ...
- 1,617
2
votes
1
answer
147
views
How to calculate Subtotals in SQL Queries
I can’t calculate subtotal and total using GROUP BY ROLLUP syntax in a SQL Server 2019 database:
CREATE TABLE [dbo].[G_Dashboard]
(
[orderId] int NULL,
[C_CO] varchar(4) NULL,
[D_CO] ...
- 131
0
votes
1
answer
108
views
Is there a maximum number of rows you can specify for TOP on SQL Server? [duplicate]
When writing a query with the TOP clause in Microsoft SQL Server 2019, is there a limit to how high a number you can specify before the query fails? For example:
SELECT TOP 9999999999999999
FROM table
...
- 27.8k
0
votes
1
answer
134
views
List dates between start and end date in SQL then merge data based on a calendar date [closed]
I have a table with purchasing data, for example this below. The purchased items are for the entire year, so there are hundreds of items. I'm looking for a way to list all of the dates of the month, ...
0
votes
1
answer
77
views
Concat the first and last sequential line number with a hyphen
Our table stores the line numbers of an order. Is it possible to concat them into a string, where the first and last sequential lines are separated by a hyphen, and non-sequential lines are separated ...
- 143
0
votes
1
answer
89
views
Error while "execute as" in a procedure to query table in another database on same server
I need assistance executing a procedure inside one database to query another database on the same SQL Server 2019 instance. I can run the procedure fine as myself, but I can't use execute as in either ...
- 49
0
votes
0
answers
197
views
OR is slower than two NOT EXISTS? [duplicate]
I have the following two queries. The first runs in less than 1 second. The latter query takes minutes to run. All of the columns being joined are indexed. Both queries are doing the same thing, the ...
- 1,021
0
votes
1
answer
111
views
Using SQL Server 2019 and SSIS in VS2022 - Execute SQL task with select statement trying to use multiple variables
As the title says, I'm trying to use multiple variables in a single Execute SQL Task. Something like this:
declare @payyear int
select max(PAY_YEAR) as payyear
from dbo.table1
select ? = @payyear
...
- 7,169
1
vote
2
answers
93
views
Find records using max date but exclude time
I need a query to pull records from a SQL Server table based on the max date in the InsertDTS column and the date should not be included in the output.
The InsertDTS column is defined as Datetime. I ...
- 224
1
vote
1
answer
133
views
SQL query or function to get only numerical values before and after certain word
I have a column in a table that has values like below and I'd like to get the numeric value after the keyword APP and before the next space after APP*. Thanks so much in advance!
Data Service B2B **...
- 13
1
vote
0
answers
329
views
SQL Server dacpac deployment not to drop and create indexes if they already exist
Using Azure Devops for SQL Server 2019 database dacpac deployment with Visual Studio 2019 is dropping and creating index how do I change it not drop and create index for every deployment. how to ...
- 1,047
0
votes
0
answers
39
views
Carry forward the last non null value [duplicate]
I have a simple table like this:
Api_No14 Prod_Date Oil_cf
42317448360000 2023-07-27 639.2337
42317448360000 2023-07-28 864.22
42317448360000 2023-07-29 NULL
42317448360000 2023-07-30 622....
1
vote
1
answer
110
views
Creating a balance statement
So based on a start date of 5 years ago on the 07th February – I want to generate 5 rows - one per year from the start date. If the start date was 8 years ago - 8 rows.
Year
StartDate
EndDate
1
07-02-...
- 241
0
votes
1
answer
38
views
Can I connect a Mendix app to SQL Server Reporting Services?
Is it possible to connect with an SSRS report from the Mendix application?
I have prepared a report in SSRS, but I am not able to integrate it with the Mendix server.
In Mendix, there is a native ...
1
vote
1
answer
141
views
Why is MIN($column) doing an Index scan instead of seeking to min value?
I have a table with more than a billion rows, with one of the column definitions being:
row_id int NOT NULL
And an index on it
CREATE NONCLUSTERED INDEX row_id_IX ON dbo.test_table ( row_id ASC ) ...
- 570
1
vote
1
answer
71
views
RDSProxy is making SELECT DATABASEPROPERTYEX.. that have high WAITs on our SQL Server
We use AWS RDSProxy as a middle tier between our API and the SQL Server database.
Noticing very high waits on the following statements called from RDSProxy even though they run very quickly manually:
...
- 2,524
0
votes
1
answer
301
views
SQL encrypt connection using OLE DB (ADO)
I'm using a SQL Server 2019 with a self-signed certificate.
Crystal Reports 2016 throws this error:
Microsoft OLE DB Driver 19 for SQL Server - ADO Error Code 0x80040e73 Format of the initialization ...
0
votes
0
answers
69
views
Update where not exactly the same [duplicate]
If I don't want to treat an empty string and NULL as being the same value, is there a cleaner way to write the following:
-- @name and SomeTable.Name are varchar(10) NULL
update SomeTable set Name = @...
- 1,021
0
votes
0
answers
137
views
SQL Job Manual Run vs Scheduled RUN
I am having a situation with the scheduled job running longer when it runs on scheduled time, but runs absolutely fine when executing manually.
The job is basically for creating the csv file using ...
- 19
0
votes
0
answers
120
views
Declaring variables in loop versus before loop
Test #1
I have the SQL below that iterates through a cursor which has 374 records. I've tested the
query a dozen times and it runs in 3 mins 40 secs (give or take a few seconds).
Test #2
I modify the ...
- 1,021