New answers tagged

1

One way is to calculate the aggregate inside a CTE (I blogged about this last month): ;WITH CTE AS ( SELECT time1, time2, t2 = SUM(time1) OVER() FROM #abcd ) UPDATE CTE SET time2 = t2/60/60; But I don't understand the value of putting the same SUM(), aggregated over the whole table, into that column for every single row. Seems easier to just store ...


3

Guessing at column names: INSERT #temp(dateColumn) SELECT dateColumn FROM dbo.WorkCalendar WHERE IsWorkDay = 1 AND dateColumn >= @begindate AND dateColumn < DATEADD(DAY, 1, @enddate); Also please try to avoid regional formats like m/d/y. These will bite you in the end, because someone will run your code on a server with different language ...


0

This query get the first Approve and first Not Approve: WITH list AS ( SELECT ea.TaskId, e.EmployeeName, ea.IsApprove , n1 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove DESC, ea.Id) , n0 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove, ea.Id) FROM @Employee_Approval ea INNER JOIN @Employee e ON ...


0

Use a Derived Table/CTE to get a single ID for "approved"/"not approved" and LEFT JOIN it two times to Employees: SELECT TaskID ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID FROM "Employee Approval" GROUP BY TaskID


0

Note that negating the criteria for a SELECT ... INTO will produce the functional equivalent of a DELETE ... INTO. And vice versa.


0

Or maybe you could use a trigger, for deleting operation. Here is an example


2

You can't do it in one line, but here's a quick hack to save having to maintain the create table code, which can come in handy if the table you're deleting from is wide. Setup DROP TABLE IF EXISTS #TEST_INPUT DROP TABLE IF EXISTS #TEST_OUTPUT CREATE TABLE #TEST_INPUT (ID INT PRIMARY KEY IDENTITY, VAL INT) INSERT INTO #TEST_INPUT (VAL) VALUES (1),(2),(3) ...


4

Not that I know of. The OUTPUT.. INTO clause requires an existing table. On a related note Using SELECT.. INTO as opposed to INSERT INTO.. SELECT or OUTPUT.. INTO can have negative side-effects depending the situation. Some examples why I think you should create the table first: If you're creating a table inside of a transaction, SQL Server may place a ...


4

The behaviors you're curious about are explained in the documentation: When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true: ...- The identity column is part of an expression. ...If any one of these conditions is true, the column is created NOT ...


1

You should check if you have permissions or not exec xp_cmdshell 'net use \\SHDVNFDBV1\shared-backup\ /USER:domain\username Passw0rd1' Do not copy to root directory as @AaronBertrand mentioned. As a side note, why are you using SQL Server to do filesystem tasks? This should be done by PowerShell e.g. Copy-Item c:\scripts\test.txt c:\test


3

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later: The Last non NULL Puzzle. Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work ...


2

This works like this: It gets numbers for each row of each ProductID ordered by SampleDate (ROW_NUMBER()). It only keeps first 2 values (biggest SampleDate which are the last 2 rows of the sample) It calculates the difference in minutes between the 2 SampleDate Query: WITH cte AS ( SELECT ProductID, SampleDate , n = ROW_NUMBER() ...


5

Here's a stab at an algorithm. It's not perfect, and depending on how much time you want to spend refining it, there are probably some further small gains to be made. Let's assume you have a table of tasks to be performed by four queues. You know the amount of work associated with performing each task, and you want all four queues to get an almost equal ...


3

How about: WHERE D.NDISCOMPLFAB LIKE DS.codigoDesenhoSubstitutivo + '_' ...which will allow an index on (NDISCOMPLFAB, Dendis) to be used effectively. Please check that the column types are the same kind between D.NDISCOMPLFAB and DS.codigoDesenhoSubstitutivo, such as both nvarchar or both varchar. If they are different, use: WHERE D.NDISCOMPLFAB LIKE ...


2

I used this code to extract the FileName with Extension. UPDATE TableName set ColumnName= right(Image_Link, CHARINDEX('/', REVERSE(ColumnName) + '/') - 1) And this code to extract and update only FileName UPDATE TableName SET ColumnName= REVERSE(SUBSTRING(REVERSE(ColumnName),CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))


3

You update column state inside the view. It refferences s.ClaimStatusName AS [Status] inside the view. From the code of the view we see that you update dimClaimStatus table (dimClaimStatus AS s). Seeing that you have 2 columns idClaimStatus and ClaimStatusName you have ID \ Name structure of the table. Inside the view you show ClaimStatusName. So you ...


7

General view updatability The key part of the CREATE VIEW (Transact-SQL) documentation is: Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query ...


3

Imagine your UPDATE is updating s with the FROM clause of your view. Then read this blog I wrote recently to see how it could be affected. http://sqlblog.com/blogs/rob_farley/archive/2016/01/12/join-effects-with-update.aspx Assuming you're not breaking the rules for updateable views, then you should be okay. Just also be wary of the things in my post.


3

This should work: WITH data AS( SELECT TtlNOfCmpSaleSpanFirst, TtlNOfCmpSaleSpanSecond, TtlNOfCmpSaleSpanThird , TtlNOfCmpSale0to3, TtlNOfCmpSale4to6, TtlNOfCmpSale7to12 , TrendValueFirst = IIF(R.TtlNOfCmpSale7to12 = 0, 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12) , TrendValueSecond = IIF(R.TtlNOfCmpSale4to6 = 0, 0, ...


3

Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again. You could use APPLY VALUES to calculate the required values once, then use the aliased column in the rest of your calculations. DECLARE @Declining FLOAT, @StableStart SMALLINT, @StableEnd SMALLINT, @Increasing SMALLINT SELECT @Declining = ...


2

First, find the person who put that job in place and have him/her tied to a chair listening to cats scratching a chalk board while you go for lunch. When done with that, compare your resource utilization and performance data for extended periods before and after the job runs. I suspect you'll see perf dips and a number of resources getting really busy ...


4

You should use the Pivot operator for this query. See the below. DECLARE @Bobsled TABLE ( [event] VARCHAR(100) , [time] DECIMAL(18, 4) , employeeID VARCHAR(25) , name VARCHAR(500) ); INSERT INTO @Bobsled VALUES ( 'Walk', '16.32', 'red12', 'red arrow' ) , ( 'Eat', '5.12', 'red12', 'red arrow' ) , ( 'Run', '32.13', ...


5

I think what you're looking for is an aggregate on employeeID where the CASE goes inside the SUM() aggregate function. SELECT employeeid, SUM((CASE WHEN [event] = 'Walk' THEN [time] ELSE 0 END)) AS walktime, SUM((CASE WHEN [event] = 'RUN' THEN [time] ELSE 0 END)) AS runtime, SUM((CASE WHEN [event] = 'Sleep' THEN [time] ELSE 0 END)) AS ...


1

Unfortunately the answer is going to be it depends. From what you have in the maintenance plan it seems whomever put them in place was trying to reset the server statistics, cache and running values at a specific time of the day to what it would be when SQL Server first starts up without actually restarting SQL Server. CHECKPOINT is going to instruct the ...


-1

Do a modulo 0.5: Insert Into b (fv, fv1) Values (.8032, 0.8032 % 0.5); See fiddle


1

You can replace fv1 by a Computed Column: Create Table #b ( fv decimal(16,4) , fv1 AS ( CASE WHEN fv > 0.5 THEN fv - 0.5 ELSE fv END ) PERSISTED )


-1

You want to set the field fv1 to be 0.5 less than the value in fv? UPDATE #b SET fv1 = ISNULL(fv, 0.00000) - 0.5 You could also make fv1 be a computed column which would make it automatic.


1

For anybody who may come across this problem, I solved using this function: -- Description: Converts ARGB to RGB(RR,GG,BB) -- e.g. 16744703 returns RGB(255,128,255) or #FF80FF CREATE FUNCTION [dbo].[ARGB2RGB] (@ARGB AS BIGINT,@ColorType AS VARCHAR(1)) RETURNS VARCHAR(16) AS BEGIN DECLARE @Octet1 TINYINT DECLARE @Octet2 TINYINT ...


6

Eithers of these 3 should work: SELECT color , HEX_1 = '#' + CONVERT(varchar(6), CAST(ABS(color) as varbinary(1)) + CAST(ABS(color/256) as varbinary(1)) + CAST(ABS(color/256/256) as varbinary(1)) , 2) , HEX_2 = '#'+ +CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2) ...


1

Community Wiki answer for content originally provided in the question by DnL SELECT -- in case that reseed will not work and u need a specific number u can use this script -- just delete the ' before DBCC --'DECLARE @identity INT SELECT @identity= '+clmn.name+' --FROM '+SCHEMA_NAME(schema_id)+'.'+tbl.name+' PRINT @identity 'DBCC ...


1

A quick and dirty way would be to have a script for the next X years. Something like: IF db_id('ctx_2015_log') is not null EXECUTE(' BEGIN USE select quotename(db_name(db_id(''ctx_2015_log''))) DBCC SHRINKFILE(ctx_2015_log) BACKUP LOG ctx_2015 TO DISK=''NUL:'' DBCC SHRINKFILE(ctx_2015_log) END '); IF db_id('ctx_2016_log') is not null ...


2

Using the script from the Question as a starting point, you might be better off using the following query: SELECT N'DBCC CHECKIDENT (''' + SCHEMA_NAME(tbl.[schema_id]) + N'.' + tbl.name + N''', RESEED); -- WITH NO_INFOMSGS;' , clmn.seed_value , clmn.increment_value , clmn.last_value FROM sys.tables tbl INNER JOIN ...


2

I fail to see how using HAVING would let you avoid reading from the same table twice. The only way I can see HAVING could be used here is in combination with a self-join – more specifically, self-cross join. That already sounds... not very promising. And yes, you are right, you would need to put all the output columns (which in your case would be all ...


3

You must pivot your data. This can be done using the Pivot operator: SELECT ID, [date] , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4 , total as [Sum] FROM ( SELECT * FROM data d CROSS APPLY (SELECT total = SUM([money]) FROM data WHERE [date] = d.[date] AND ID = d.[id]) a ) t PIVOT ( MAX([money]) FOR [hour] IN ...


1

Try something like this: SELECT ID ,[date] ,SUM(case when hour = 1 THEN money END) AS Hour1 ,SUM(case when hour = 2 THEN money END) AS Hour2 ,SUM(case when hour = 3 THEN money END) AS Hour3 ,SUM(case when hour = 4 THEN money END) AS Hour4 ,SUM(money) AS [Sum] FROM data GROUP BY ID, [date]


3

If your table's clustered index has pd_dt as the first column, this may be the most optimal query. You could simplify it by breaking it into two parts, like so: DECLARE @dt date; SELECT @dt=DATEADD(month, -12, CAST(MAX(pd_dt) AS date)) FROM DW_STAGE.ETL.Claims; SELECT * FROM DW_STAGE.ETL.Claims WHERE pd_dt>@dt; Things you could do to improve ...


2

Please refer to Create a User-Defined Event By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts. So your message should be EXEC sp_addmessage @msgnum = 50005, @severity = ...


1

Adding "with log" to your raiserror statement will make the alert fire. RAISERROR (50005, -- Message id. 16, -- Severity, 1, -- State, N'My custom message') WITH LOG; you will need sysadmin or alter trace to use with log(https://msdn.microsoft.com/en-us/library/ms178592.aspx)


5

Alternate solution Instead of using xp_cmdshell, which comes with a lot of security risks, you could run something like this from the command prompt: osql >output_file.txt -S myServer\myInstance -E -Q "PRINT @@VERSION" The -S switch denotes the name of the server and instance, -E means Windows authentication (you could instead use -U and -P for ...


9

This command: ECHO 1>C:\Pharmsuite\Vercheck.txt Does not do what you think it should do because 1> has a special meaning. This problem can be fixed by adding a space before >: ECHO 1 >C:\Pharmsuite\Vercheck.txt Or in your case: SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck It also works with this command (making sure there is a space before ECHO ...


5

The problem is, 1> has a specific meaning. It means "redirect standard output", whereas 2> would be "redirect standard error". > by itself is short-hand for 1>. You can try: SET @CMD = 'ECHO>'+@Vercheck + ' ' + @Ver Since you can perform redirection before the rest of the arguments to echo.


3

Like @ypercube suggest, aggregate both sides separately, then join them. In the following code, I've intentionally used LEFT JOIN to cater for the possibility that a project may not have a benefit or cost. WITH ben AS ( SELECT pbc.project_id, SUM(b.project_benefit) AS TOTAL_BENEFIT FROM Benefit AS b INNER JOIN ProjectBenefitCost AS pbc ON ...


1

SELECT P.id, P.name , isnull(c.sum, 0) AS TOTAL_COST , isnull(b.sum, 0) AS TOTAL_BENEFIT , isnull(b.sum, 0) - isnull(c.sum, 0) AS delta FROM Project AS P LEFT JOIN ( select BC.project_id, sum(Cost.project_benefit) as sum from ProjectBenefitCost AS BC JOIN Cost ON BC.id = ...


3

Use: SELECT * FROM Project AS P OUTER APPLY (SELECT SUM(C.project_cost) AS TOTAL_COST FROM Cost AS C JOIN ProjectBenefitCost AS BC ON P.id = BC.project_id AND BC.id = C.benefitcost_id) C OUTER APPLY (SELECT SUM(B.project_benefit) AS TOTAL_BENEFIT FROM Benefit AS B JOIN ProjectBenefitCost AS BC ON P.id = BC.project_id ...


2

This should work: SELECT LEFT(fieldtosplit , CASE WHEN LEN(fieldtosplit) > 1000 THEN 1000 ELSE LEN(fieldtosplit) END ) , CASE WHEN LEN(fieldtosplit) <= 1000 THEN '' ELSE SUBSTRING(fieldtosplit, 1001 , CASE WHEN LEN(fieldtosplit) > 2000 THEN 1000 ELSE LEN(fieldtosplit)-1000 END ) ...


6

Assuming that the [PhoneField] is 10 digits, such as '1234567890' then you could use STUFF to put the proper values in. E.g. select STUFF(STUFF('1234567890',7,0,'-'),4,0,'-'); But please note that this only answers formatting a phone number for the 10-digit answers. If you are using internationally dialed numbers, you need to investigate the various ...


2

there isn't a function that will do it for you. You can cast the phone number as a string then then parse out the parts and add hyphen declare @phone int set @phone = '1232223333' select SUBSTRING(cast(@phone as varchar(10)),1,3)+'-'+SUBSTRING(cast(@phone as varchar(10)),4,3)+'-'+SUBSTRING(cast(@phone as varchar(10)),7,4)


1

You could try something like this query: Declare @d1 date, @d2 date, @numreturned int Set @begindate = '01/01/2015' Set @enddate = '01/12/2015' Set @numreturned = 0 SELECT @numreturned = COUNT(DISTINCT CAST(csdate as date)) FROM firsttabletocheck WHERE csdate >= @begindate and csdate < @enddate; IF DATEDIFF(day, @begindate, @enddate) > ...


1

Just to add ,before you set up alerts for job notification (FAIL or SUCCESS) it is important you would require to configure Database Mail on the SQL server instance. Read here on how to configure the said above. Once Database mail has been configured, you will need to create Sql Server Agent Operators to get notification not only for SQL job failures but ...


0

You can use script from microsoft gallery How To Restore Multiple Databases In SQL Server



Top 50 recent answers are included