Hot answers tagged

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


7

This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question. There is no obvious choice ...


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


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


6

A CLR aggregate will almost certainly be the fastest way of doing this. But perhaps you don't want to use one for whatever reason... You say that the source for this is an expensive query. I would materialise this into a #temp table first to ensure it is only evaluated once. CREATE TABLE #test ( ID INT, name NVARCHAR(128), car NVARCHAR(128) ); CREATE ...


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


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


5

I ran a few tests using a little over 6 mil rows. With an index on the ID column. Here is what I came up with. Your initial query: SELECT * FROM ( SELECT t.id, stuff([M].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1], stuff([M].query('/car').value('/', 'varchar(max)'),1,1,'') AS ...


5

First of all I'm not sure why you are dividing by 3600 .. that would return you 1/3600th of a second. Second you are over thinking things a bit. Try this: CREATE TABLE #NotDope ( date1 datetime ) INSERT INTO #NotDope (date1) Values ('2016-01-04 08:08:47.000'), ('2016-01-04 08:25:59.000'), ('2016-01-04 08:27:47.000'), ('2016-01-04 08:00:00.000'), ...


5

If you are guaranteed to only ever use the 26 letters of the US English alphabet (both upper-case and lower-case versions) then sure, you can get away with using LIKE and/or PATINDEX with the simple range notation of [a-z] But, if you might get characters not found in the en-US alphabet yet available in various Code Pages / Collations for VARCHAR data (e.g. ...


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.


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


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


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


4

I think you are looking for output DECLARE @MyTableVar table([testID] [uniqueidentifier]); INSERT [AddressBook] ([address], [zipcode]) OUTPUT INSERTED.[testID] INTO @MyTableVar VALUES (N'address', N'zipcode'); --Display the result set of the table variable. SELECT [testID] FROM @MyTableVar; GO uniqueidentifier may not be the most efficient id ...


4

The simplest way to read and increment a numeric value in a text file would be to not do it in SQL Server, though you can still use SQL Server to cause it to happen. You can create a CMD script as follows that accepts a single input parameter for the file name and then increments the value inside of that file by 1. If the file name does not exist, it will ...


4

The very last CAST is missing a parenthesis Set @d2 = CAST(GetDate()-2 As Date should be Set @d2 = CAST(GetDate()-2 As Date) That fixes the main issue. (also, once that was fixed you had and extra END at the very end which I removed--or alternately, adding an additional BEGIN.) Declare @d1 datetime, @d2 datetime Set @d1 = '01/01/2013' Set ...


4

The sequence number of a day in a year that begins on april 1 can be calculated this way: SELECT 1+ DATEDIFF(day, DATEFROMPARTS( DATEPART(year, DATEADD(month, -3, [Date])), 4, 1), [Date]) AS FYdayNumber FROM tableWithDates; In essence, we're subtracting three months from the current ...


3

why is it failing in the UPDATE but not the SELECT...FROM? Because in your SELECT you are filtering out the rows via WHERE #ReleaseTemp.ReleaseNotes <> ''. You do not have that filter on your UPDATE statement and you probably have at least one row that has a length of 0 or 1. Try making it: UPDATE #ReleaseTemp SET ReleaseNotes = ...


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


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


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


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


3

I've got a post here that does something similar. Basically I'm using a recursive CTE to go loop over and over again replacing one "bad" character at a time. I'm using STUFF to strip 1 character (although you can use it to replace with a space) and PATINDEX to find the location of the character I want to remove. You could modify it slightly to do what you ...


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


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

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

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


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


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



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