Hot answers tagged

12

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 ...


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 ...


8

Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table: CREATE TABLE #test123( c1 float, c2 float, c3 float, c4 float, c5 float ); INSERT #test123(c1,c2,c3,c4,c5); VALUES (2,3,NULL,1,2), (2,NULL,NULL,1,2), (2,3,NULL,NULL,2), ...


8

Does this get you what you want? DECLARE @x XML = ' <SearchJobConfig> <QueryID>1072</QueryID> <QueryString> <SearchCriteria name="Search query" > <ExpressionSet logicalOperator="AND"> <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="GREATER_EQUAL" dataType="string" ...


8

You can use grouping set also declare @JobRequiredProducts table (jobId int, productId int); declare @Product table(productId int, productUnitPrice decimal(8,2)) insert into @JobRequiredProducts(jobId,productId) values(1,4),(1,5),(1,6); insert into @Product(productId, productUnitPrice) values(4,175.99),(5 ,100.00),( 6 , 125.00) SELECT j.jobId, ...


7

You should use a case statement like this: SELECT * FROM test123 WHERE ( (CASE WHEN C1 IS NULL THEN 1 ELSE 0 END + CASE WHEN C2 IS NULL THEN 1 ELSE 0 END + CASE WHEN C3 IS NULL THEN 1 ELSE 0 END + CASE WHEN C4 IS NULL THEN 1 ELSE 0 END + CASE WHEN C5 IS NULL THEN 1 ELSE 0 END) < 2); The ISNULL approach is returning ...


7

This should work for you. At least for the entries in the PowerCurve table that I copied over, it works correctly. This should get you close. See comments in the code for more details. Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0. /** FIRST - we setup the data and tables we need. ...


6

You could try something like this SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, '')) WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, '')) WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '') ELSE fieldname END as ...


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

While not a great idea in general, a workaround that seems to work is using the USE PLAN hint Base query to get the XML from -- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints SELECT * FROM dbo.People WITH (INDEX(IX_Live_People__Name)) WHERE LEFT(VolatileData, 2) = '2A' AND Name = 'John' UNION ALL SELECT * FROM hist.People WITH (...


5

I have two solutions for your question : solution 1 , I use Not Exists , Except Select distinct C.CustomerName From customers C where NOT EXISTS ((Select BranchName From Branch Where BranchCity='Bahar') EXCEPT (Select BranchName From Deposit a ...


5

SELECT cli_razon_soc, SUM(CASE WHEN t .fac_saldo_tot > 500 THEN t .fac_saldo_tot ELSE 0 END) AS 'Mayor500' FROM cdc_vw_BuroCredito_Facturas95 AS t GROUP BY cli_razon_soc, fac_saldo_tot ORDER BY cli_razon_soc, 'Mayor500' As the CASE operation is being applied to each row, checking the sum doesn't make sense logically. What we need to do ...


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); ...


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, ...


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

This is a parse time error, not a runtime error. You can't use an IF check to determine if a column reference will be ok to use, because the column reference is validated first, long before the IF ever runs (this is why IF 1 = 0 also fails). Note: The script in the question will appear to work (e.g. it will print end) if you copy the script to your own ...


4

The first thing I would do is create some keys on the source tables: CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT1 (ATYPCODE); CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT2 (AGLTLVL, AGLTCODE); Then organize the data into a useful hierarchy using a recursive query to assign group and row numbers within the structure: CREATE TABLE #Data ( grp ...


4

SELECT TRIM(' "' FROM fieldname) fieldname FROM tablename TRIM() function requires SQL Server 2017.


4

Randi's answer works perfectly, and Akina's is much tidier if you are on SQL Server 2017 or better, but on older versions you can do this with slightly less code and fewer operations like reverse/stuff. Given this data: CREATE TABLE #temp(col varchar(255)); INSERT #temp(col) SELECT 'foo' UNION ALL SELECT '"bar"' UNION ALL SELECT 'blat"' UNION ALL ...


4

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 ...


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....


3

An alternative way to identify the islands is using the LAG analytic function and the SUM() window aggregate function: WITH sentinels AS ( SELECT DateStamp, Success, Error, Grp = CASE WHEN Success = LAG(Success) OVER (ORDER BY DateStamp ASC) AND Error = LAG(Error ) OVER (ORDER BY DateStamp ASC) ...


3

It might depend on what is meant by "too long": Length: If you mean strings of many characters, then try converting some of them to a MAX type (i.e. NVARCHAR(MAX) or VARCHAR(MAX) ). For example: CONVERT(VARCHAR(MAX), 'This is a test ') + CHAR(13) + CHAR(10) + ' string' Concatenations: If you mean operations involving many concatenations, there is ...


3

If I read your question correctly, what I think you are asking is - how do I use the result of one calculated column in another? Well, if you want to actually add that column to the table you could just inline the calculation within your new column - this would work but requires more maintenance and is probably not an ideal solution (this also works in a ...


3

Essentially, you need to compile a list of dates, and optionally, list of possible codes (your question specified only one, but including all to pick from is trivial), group by possible date and possible code, then left join to the data entries to pick out positive and negative values to sum. Expressed as a single query (so, no procedural logic), this can be ...


3

You are in the right track just a few things to keep in mind. Make a left join to the date table (DIMDATE). FROM DIMDATE D LEFT JOIN ACNTHEADER A Which make sure that no days are omitted. Since you are trying to add the values based on date, you need an Aggregate function in the final columns.SUM in your case. To split the columns based on the value you ...


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