Hot answers tagged

21

This works too: SELECT salesid, SUM(num) FROM #BE GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END; Assumptions: Id starts at 1, hence it can use THEN 0. salesid ELSE salesid+id+1 would work as well 0 is considered positive number, hence the >= 0 (Is zero positive or negative?). Although x+0=x seems to make the = sign unnecessary, it helps ...


20

Try this: SELECT salesid, sum(num) as num FROM #BE WHERE num > 0 GROUP BY salesid UNION ALL SELECT salesid, num FROM #BE WHERE num < 0; If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a ...


10

First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a SqlConnection, like any other .NET app, and submit T-SQL, or execute a Stored Procedure. Yes, you can call a SQLCLR (whether it is C# or VB....


8

It looks like your two conditions can be reduced to just one: They do not exist in table A with a status of 1. Based on that, your SQL query could look like this: SELECT B.* FROM B WHERE NOT EXISTS ( SELECT * FROM A WHERE A.Status = 1 AND B.Item = A.Item AND B.Date IN (A.Date1, A.Date2, A.Date3, A.Date4)...


7

Use for xml path to create XML instead of casting. select @YourVariable for xml path(''), type Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.


6

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @...


5

This will happen if you have ties for the lowest frequency The formula for figuring out the percentile rank is the following (rk -1)/(rn -1) where rk equals the rank of the value and rn equals the count of the items. The below shows an example along with a calculated column showing how the PERCENT_RANK is calculated. As the highest RANK is 3 when ...


5

The answer is quite simple. You're using a load of ORs, which should be ANDs: object_name(object_id) NOT LIKE '%BACKUP%' or object_name(object_id) not like '%backup%' OR object_name(object_id) NOT LIKE '%Wi%' In your case, object_name(object_id) not like '%backup%' will still match OR object_name(object_id) NOT LIKE '%Wi%' and so will return the row ...


5

No, I am not aware of any built-in function that does exactly this. But, you can still accomplish this without doing anything too complicated. You could use the built-in CRYPT_GEN_RANDOM function (introduced in SQL Server 2008 R2) which generates random values based on a supplied length. The output is in hex/binary values so each byte returned is ...


4

You should read up on MVCC (Multi Version Concurrency Control), also known as record versioning, record shadowing and even multi generational architecture. What it means is that when a transaction reads a record, what it actually gets is a copy of the record. Then another transaction reads the record - gets another copy. Transaction 1 changes its copy of ...


4

How is it that N number of users can access the same physical data at the same time? This is done using transactions (honors ACID properties) & concurrency models. SQL server supports Optimistic concurrency as well as Pessimistic concurrency (default concurrency model) which inturn uses different isolation levels (read committed is the default ...


4

There are various ways to do this type of query (called anti-join or anti-semijoin): Correlated NOT EXISTS subquery. In my opinion, the most self explanatory: SELECT a.* FROM a WHERE NOT EXISTS ( SELECT 1 FROM b WHERE b.id = a.id AND b.value = a.value ) ; LEFT JOIN with IS NULL check. Very common solution, too, but ...


4

CTE: You can embed you SELECT with RANK() into a CTE and then UPDATE the CTE. WITH cte AS ( SELECT *, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count) FROM @data ) UPDATE c SET rank_in_department = r FROM cte c; Don't forget the ; terminator at the end of the line preceding the CTE statement. Sub Query: ...


4

That is perfectly valid XML. If you extract the bbb element using the .value method of the XML data-type, it will be de-entitised at that point, eg You can extract and add the element with no special handling or replacing, eg using the .modify method of the XML data-type: DECLARE @xml XML = '<zzz xmlns="http://example.com"> <aaa>aaa</...


3

Since no one else has mentioned this as a possibility, you can do this all directly via TSQL commands. First, you can directly write out to a file via either a linked server (if the file will have a static path) or via OPENROWSET or OPENDATASOURCE functions if the files are more dynamic in nature. Details on how to do this for excel files can be found here:...


3

merge production as target using raw as source on target.refno = source.refno when matched and souce.date > target.date then update set target.date = souce.date, target.value= souce.value when not matched then insert ... merge


3

Assuming your variables are always NOT NULL WHERE ( ReceiveDateTime < @DocOldestTreshold OR ( ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold ) ) Is equivalent to WHERE ReceiveDateTime < @DocOldestTreshold OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold) Which is ...


3

You can use NOT EXISTS for this: SELECT * FROM TableB b WHERE NOT EXISTS (SELECT 1 FROM TableA a WHERE a.Item = b.Item AND a.Date = b.Date AND a.Status = 1 ) Since multiple entries can exist, NOT EXISTS can be simpler than a solution with JOIN. If your criteria ...


3

Comparing the two plans reveal this snippet: <Warnings> <SpillToTempDb SpillLevel="1" /> </Warnings> The two plans enter execution with the same memory grant, but one has actual rows 3910 (and spills) while the other has 1502. As far as I can tell, the difference comes ...


2

Using SELECT * in production code is evil, for a number of reasons. If you absolutely must do this, the procedure you're looking for is sp_refreshsqlmodule, which works much like the built in sp_refreshview, rather than sp_recompile.


2

This is really a T-SQL question and would be better off on StackOverflow. That said, you can use the following to determine if the date falls on a weekend: SELECT DATENAME(dw,GETDATE()) -- Friday SELECT DATEPART(dw,GETDATE()) -- 6 And determining if the date falls on a holiday (by querying against your holiday table) should be trivial. I recommend you ...


2

If you have two objects with the same name, it is almost certain that one is in a different schema. So to differentiate, you need to include the schema name in the call to sp_help as well. EXEC sys.sp_help N'dbo.tablename'; EXEC sys.sp_help N'otherschema.udtname'; You should always be using schema-qualified object names anyway: Bad habits to kick : ...


2

This query will give you a list of your table names and the field name into a temp table. IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData GO CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max)); DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME'; INSERT INTO #BaseData (...


2

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


2

Man, I'm terribly sorry. I'm 'answering' stating that there's no simple solution as the one you devised. You presented us a very interesting challenge. I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself. If you, please, ...


2

Many alterings in one transaction with rollback and commit - it is not a dream. It is possible. Here is a scaffold for your script (following MS guidelines with improvements): BEGIN TRANSACTION BEGIN TRY -- place your script in this TRY block -- your DDL instructions: ALTER TABLE1... ALTER TABLE2... -- data modifications: EXEC(' ...


2

To solve this one, I did the following: Created the tables: CREATE TABLE tablea (skua VARCHAR(8), statusa INT, qtya INT, datea date); CREATE TABLE tableb (skub VARCHAR(8), qtyb INT, dateb date); Populated the tables: tablea) INSERT INTO tablea VALUES ('F-001100', 1, 400, '2016-07-14'); INSERT INTO tablea VALUES ('F-002200', 0, 750, '2016-07-28'); ...


2

You should be able to turn your top set of data into the tabular set of data below it by doing something like this: SELECT Customer, Count(CASE Month(Date_Column) WHEN 1 THEN [TXN#] ELSE Null END) AS Jan, Count(CASE Month(Date_Column) WHEN 2 THEN [TXN#] ELSE Null END) AS Feb, Count(CASE Month(Date_Column) WHEN 3 THEN [TXN#] ELSE Null END) AS ...


2

Thanks for the suggestion, but I want to replace rows -- not update field by field in every modified row. So after some research I think that the best thing I would have to do is to first delete all the old rows that will be replaced with new data. DELETE FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableB.refno = TableA....


2

This is not possible to do within the context of a single execution of whatever is in the Query pane. T-SQL commands are not interpreted by SSMS. They are sent to SQL Server to be processed. Only a GO [execution_count] statement (batch separator, GO being the default but can be changed) and a subset of the SQLCMD commands -- !!, :exit, :quit, :r, :setvar, :...



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