I have 3 tables, the MAIN_TABLE, the SUB_TABLE and the ID_TABLE. I need to compare the CODE in the MAIN_TABLE with the CODE in the SUB_TABLE, and if they match, search for the SUB_ID in the ID_TABLE and update the ID in the MAIN_TABLE with that ID. In the example shown below, the query should update the MAIN_…
Tag: sql-server-2014
Invalid object name ‘sys.dm_exec_input_buffer’
I’m trying to start a script in SQL Server 2014, but it can’t find the object in question. This is the query I am trying to initiate: Error message: Msg 208, Level 16, State 1, Line 1 Invalid object name ‘sys.dm_exec_input_buffer’. Answer You need to be on at least Service Pack 2. If y…
Remove minutes and seconds and then convert to UTC timezone
I have column with time stamp data like this 2021-12-09 08:01:00.520 I want to remove the minutes and second and then convert the date to UTC timezone in one line. I tried this code I get this result 2021-12-09 08:00:00.000 and I tried this code I get this result 2021-12-09 16:01:00.520 What I expected is 202…
How to use ‘between’ in CASE statement?
I need to select Shift from my production table such that if the time time is between 05:00 PM to 06:30 AM it is Shift B else its Shift A. The SQL that I have written is quite simple but does not gives me the expected results. The SQL is The Result I get is Shift_FK_ID 1 1 1 Where
Combination of dynamic pivot and static pivot in SQL Server
Dynamic pivot combined combined with static aggregates I have a table that looks something like this: I basically need to: Group by a number of fields (Place, State, Category in the example) Count per such group Sum MCount, Cost (and others, not in example) per group, these columns are static Pivot over Categ…
How to remove duplicates out of the UNION but ignore one column
Consider the following table of data: FirstName LastName Department Steve Colton Accounting Stacy Beckham Finance Mary Messi Finance Steve Colton Finance Michael Gretsky Finance As you can see Steve Colton is in both Accounting and the Finance departments. I want a query that should return Steve just once. I …
Combine two queries to get the data in two columns
This returns Using this statement Which returns I would like to combine these 2 queries in 1 to get the below result. Tried union / union all but no success 🙁 Any help will be very much helpful. Answer You can simply use the sub-query as follows:
Specific hierarchy geography
I have a table geography with three levels I need to display the level3 only for level2 = France, Benelux and for the other countries level2 = level 3 Result Example: Answer
Too many arguments passed to Stored Procedure storing image
I am trying to store a JPG image into a MSSQL Server 2014. The image path was being passed into a stored procedure and part of the process within the proc is to store the image into a VARBINARY(MAX) field (I cannot change schema). My first attempt was within the same proc like this: Even though the command th…
Naming a default constraint
I’m trying to create a default constraint here, but the system is generating a weird name for it. If I want to name it df_MY_TABLE_GUID or something, how could I specify that name be used? Answer Just specify the constraint name with the full syntax, like the UNIQUE in your example: As a matter of routi…