Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with query
Search options answers only
not deleted
user 1186
Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).
5
votes
when selecting from multiple tables I get a multiplication of the records
You should consider using the more modern INNER JOIN syntax. Using table1, table2, table3 often leads to cartesian product. It's still possible with table1 INNER JOIN table2 ON ... INNER JOIN table3 O …
- 182k
4
votes
Accepted
Flat query to Column structure
SQL Server doesn't have dynamic or variable-based pivot, so one approach could be dynamic SQL.
DECLARE @StartDate DATE = '20120101';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT TOP (7)
@sql += N' …
- 182k
3
votes
Accepted
How can i make month appear with two digits SQL Server
DECLARE @t TABLE(InsertionDate datetime);
INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125');
SELECT CONVERT(char(6), InsertionDate, 112) FROM @t;
Result:
202004
201901
- 182k
2
votes
Accepted
Find a doubled value between two columns in the same row
Your WHERE clause can include basic math like this, e.g.
WHERE GRIR.ReceivedQty = 2.0 * GRIR.OrderedQty;
...or...
WHERE GRIR.OrderedQty = GRIR.ReceivedQty / 2.0;
(And this db<>fiddle demonstrates wh …
- 182k
1
vote
Accepted
Fill down if no value in period, autoknow the periods
There is probably a slightly cleaner way to do this but I wanted to do as little to your existing query as possible (I just added a row number and slight formatting changes for my own readability). … DECLARE @d date = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1);
;WITH src AS -- mostly your existing query
(
SELECT project,
period,
rn = ROW_NUMBER() OVER
( …
- 182k
1
vote
Create a row based on a condition (column value)
Different way to skin the cat:
DECLARE @emp table
(
EmpNo int,
GivenName nvarchar(32),
PreferredName nvarchar(32)
);
INSERT @emp(EmpNo,GivenName,PreferredName)
VALUES (1,N'Paris',N …
- 182k
5
votes
Should Query Tuning be Proactive or Reactive?
individual and overall performance to known baselines and/or against the requirements (e.g. some reports run in the background or are otherwise automated, so performance - or rather speed - of every single query …
- 182k
7
votes
Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?
If you don’t believe the commands you’re issuing clear the plan cache / buffer pool, you can check those things via DMVs right before issuing your query. …
- 182k
8
votes
Extract rows of a table, that include less than x NULLs
test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL isn't effective here, run this query …
- 182k
2
votes
Fastest Aggregate function when you have series of duplicate values
Given the current design, I don't know if there will be one approach that is noticeably more efficient than any other. You may be able to minimize the pain by creating an index on just OrderID and Pri …
- 182k
1
vote
Accepted
Date variable to filter result for all previous years
There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():
DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);
SELECT ... FROM d …
- 182k
18
votes
Accepted
Combine column from multiple rows into single row
This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless yo …
- 182k
6
votes
Accepted
Invalid column name even after checking if it exists so it should skip the query part
<query that references column_name> ...;';
END
Also here is why I avoid shorthand metadata helpers like OBJECT_NAME(), SCHEMA_NAME(), etc., and instead recommend creating your own helper views. …
- 182k
1
vote
SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field
Depending on indexes etc. this should provide slightly lower I/O:
SELECT PERSON_ID, COUNTS = COUNT(APPL_ID)
FROM dbo.APPLICATIONS
GROUP BY PERSON_ID
HAVING COUNT(CASE WHEN APPL_DATE >= '20180101' THE …
- 182k
4
votes
Accepted
Identifying rows which don't match a master row
1, 1, '') + ' FROM dbo.table1_master
)
SELECT m.LocationID
FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
' + @c2;
SELECT @sql;
--EXEC sp_executesql @sql;
You can take the output of this query … as is and store the query somewhere, or you can comment out the SELECT and uncomment the EXEC and leave it as permanent dynamic SQL - in this case it will automatically adapt to column changes in the …
- 182k