Hot answers tagged

14

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=. Interval with ...


11

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join: SELECT * FROM user_branch T1 LEFT JOIN dimcustomer2 T2 ON T1.BRANCH_CODE = T2.BRANCH_CODE INNER JOIN customer_guarantee T3 ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM Let us assume that some rows in T1 have no matches in T2. More ...


10

SELECT record_id, rec_date , CASE WHEN rec_date::time < '08:00' THEN 'Night' WHEN rec_date::time >= '20:00' THEN 'Night' ELSE 'Day' END AS indicator FROM records; Why? It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in ...


9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


9

I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning. Joined ...


6

Rewrite your query such a way: SELECT a.*, x.cnt FROM #temp a CROSS APPLY ( SELECT COUNT(VAL) AS cnt FROM #temp b WHERE a.val <= b.val ) x --WHERE x.cnt = 1 If you uncomment the where clause you would get 333 | 1 as a result. You request a row from the outer table which doesn't have duplicates or bigger values.


5

One way you can do this is with a PIVOT. Here is the setup code: create table grades ( ID int, Mapped_Heading varchar(10), Source_Value varchar(10) ); create table students ( ID int, Name varchar(10), Age int ); insert grades values (1,'English', 'pass'), (1,'Math', 'pass'), (1,'History', 'fail'); insert students values (1, ...


5

Your syntax won't work with PostgreSQL, but I can answer your question for PostgreSQL anyway: Your query will often work as you intend because the three statements will often use the same execution plan. There is no guarantee for that though. One possibility where even a sequential scan in PostgreSQL will return a different order is if there is already a ...


5

The only thing I can think of, is to convert the value to a string, remove all trailing zeros, convert it back to a numeric and then use the scale() function: scale(trim(trailing '0' from the_column::text)::numeric) The following example: create table t1 (val numeric); insert into t1 values (1.955000000), (1.10), (1.1010), (1.102030); ...


4

Snowflake currently supports recursive CTEs. I just build an sql to parse an account tree to consolidate amounts at the branch level. From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination with main as ( select a.scode as src , b.scode as tgt from accttree a join accttree b on (a....


4

Simple addition and subtraction. Although the code is SQL code, the math works the same in PL/SQL. This code: alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; with data as ( select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual ) select ...


4

A FULL OUTER JOIN would be one way: create table table1 (column1 int, column2 int null) insert into table1 values (1,1), (2,3), (3,2), (10,12), (5,10) select * from table1 t1 full outer join table1 t2 on t1.column1 = t2.column2 where t1.column1 is null or t2.column2 is null Or wrap that in EXISTS to get it as a single set. select x.* from table1 x where ...


4

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code: DECLARE @cmd nvarchar(max) ; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL ' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS , db = ''' + d....


4

Improving the Original Query The query as written will use the nonclustered index that you created, but it won't seek into it - the whole index gets scanned. I reproduced the query and data locally - the DATEDIFF version has a plan that looks like this: Table 'Events'. Scan count 1, logical reads 28323 SQL Server Execution Times: CPU time = 1891 ms,...


4

According to docs BETWEEN transforms into: a BETWEEN x AND y is equivalent to a >= x AND a <= y That means that x must be lesser than y. Also in the docs you can find there is a BETWEEN SYMMETRIC, that can help in your question. BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that the argument to the left of AND be ...


4

You can divide by the unit and add the average order size: SELECT avg("price USD" / grams) AS average_price_per_gram, avg(grams) AS average_order_size This statement will return a single number: the average over all values specified in te WHERE condition. If you want several result rows, grouped by month, add an appropriate GROUP BY clause: GROUP ...


4

Here is what you should do - obviously it depends on precisely what you mean by "price" and "average" - I think that I've covered all the bases (see the fiddle here): CREATE TABLE blueberry_order -- a table is a set which is always singular -- except where this would be a keyword i.e. "order" ( ...


4

I think this will give you want you want: select project_id, version, stop, t_id, status, mp, case when rowno = 1 then 'Y' else 'N' end is_last_version from ( select project_id, version, stop, t_id, status,...


4

Analysis Your inner SQL statement is always TRUE (for non-null values). You are probably wanting the max(version) for each project_id, not each version. Additionally, these scalar sub-queries are known to be slow at times. Solution Use an Analytical function. PLAN vs Yours, right side, is doing two Full Table Scans vs my one. This becomes worse with larger ...


3

JSON FTW. As a JS developer I'm partial to Object mapping. Here's an example of what that would look like in PG: SELECT COALESCE( ( (json_build_object( 1, 'APPLE', 2, 'GOOGLE', 3, 'MSFT', 4, 'AMAZON' )::jsonb )->>(2::text))::text, 'UNKNOWN' ); -- RETURNS 'GOOGLE'


3

Ideally you would normalize your data. If you can not do that, then there are at least two ways to accomplish this. DB FIDDLE For Both Examples You can view the execution plans of these simplified queries here. UNPIVOT > JOIN > PIVOT Method One way is to UNPIVOT your Table1, then JOIN to Table2, and then PIVOT back to a single row. declare @Table1 ...


3

Your particular example uses and inner join. The results will always be the same whether you use ON or WHERE. I think of the ON clause as applying the predicate to the row from the right-hand table before it is combined with the left-hand row. The WHERE applies after the combination is formed. This is just how I think inside my head. The query optimizer ...


3

Disclaimer, everything is nullable in your tables so IS NULL predicates may give false positives. Your query comes a long way, all you need is to add certain predicates: 1 and 2. SELECT Table1.Brand , Table1.Origin, Table1.Destination, Table1.Price, Table2.Price, Table1.Price - Table2.Price as diff FROM Table1 ...


3

Assuming no NULL values in any field of any record, and no duplicates by (brand, origin, destination) in any separate table... Core query: SELECT Brand, Origin, Destination, Price, MAX(CASE WHEN source = 1 THEN price END) price1, MAX(CASE WHEN source = 2 THEN price END) price2 FROM ( SELECT Brand, Origin,...


3

You could see your inner count query Select COUNT(VAL) from #temp b where a.val <= b.val; as (Select COUNT(VAL) from #temp b where 333 <= 333,222,111) = 1 (Select COUNT(VAL) from #temp b where 222 <= 333,222,111) = 2 (Select COUNT(VAL) from #temp b where 111 <= 333,222,111) = 3 Showing that only 333 from #temp a has one match, as it ...


3

tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name: select * from customers tablesample system (1) where last_name = 'powell'; Note that the where clause will be applied after the table has been sampled. It will not return 1% of all customers that have the last name 'powell'. But instead it will apply ...


3

You could try applying the filter (WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')) Directly on the index instead of it being implemented as a filter further in the execution plan. You can do this by not applying functions on columns in your where clause and adding an index. The group by is also ...


3

I have recreated your table in tempdb using the Adventure Works Database. This code basically selects all the records that have column contains ('A', 'B', 'C', 'D', 'E', 'F') and then does a distinct count on the [contains] column. When the result is six the record must have a row of all of ('A', 'B', 'C', 'D', 'E', 'F'). Create Table #Table (Userpin int, ...


3

String concatenation in that form combined with ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the ORDER BY (ordering won't be guaranteed, but maybe that's ok, since your TOP 100 doesn't have an ORDER BY, so that's not guaranteed to be deterministic either). If ...


3

You're basically asking, "What's the fastest way to join two tables?" The answer is going to depend on a lot of things, even coming down to how much time SQL Server has to spend when optimizing the query. Your best bet, rather than asking a general theoretical question, would be to run the query both ways and compare: The output from SET STATISTICS IO ON, ...


Only top voted, non community-wiki answers of a minimum length are eligible