I am trying to append time from a column to a date and use in a case statment getdate() at time zone ‘Central Standard Time’ this is what i’ve tried so far datatype of endtime column is nvarchar and the format of the time is 21:00 and this is the error that i am getting Incorrect syntax near…
Tag: sql-server-2017
How to aggregate and Join or Union into flat Json object with arrays?
I have data that looks like: Customers Table CustomerContacts Table Here’s the result I’m looking for: What I have tried: I’m embarrassed to say I’m not even close: Answer Unfortunately, SQL Server jumped on the JSON wagon a bit late in the game (started built in support only in 2016 v…
Merge the table based on date and condition
I have following table: In this table type will be 1,2,3,4.. here date and type both are composite key. I need to merge the row based if same date exist to single row and merge based on only below condition and grp col is based on running count of date. Answer Try GROUP BY FIDDLE DEMO Output
How to rename database file programmatically?
I have two databases: database_A. Name of file is database_A.mdf. database_B. Name of file is database_B.mdf. Then I what I do: Drop database_A. Rename database_B to database_A: sp_renamedb ‘database_B’,’database_A. However, name of file is still database_B.mdf. Is it possible just by code r…
Select multiple values from a single underlying values as a table
I have a table from which I select multiple values based on two underlying values in the table select (1.2 + ca.MaxReturn), (1.0 + ca.MaxReturn), (1.0 + ca.MaxReturn * 0.50), (1….
Update a Table Variable Where Not Exists
UPDATE @Customer SET ValidaitonAction = 1 WHERE NOT EXISTS (SELECT 1 FROM DMScustomerupload WHERE AccountNumber = @Customer.AccountNumber) Where @Customer is a TABLE variable: DECLARE @…
Trying to replace a Cross Apply with a JOIN
We have a table, ProductHierarchy, which is set. The # of rows will not change. It has simple parent/child Product Hierarchy data in it. We also have a Table-valued function which takes a …
Create an index that only cares if a DateTime field is null or not?
I have many tables with a nullable DeletedDate column. Every query I write needs to check that the record is not deleted, but doesn’t care when it was deleted. I want to add an index to this column, …
SQL server: How to Modify a JSON element value in a nested JSON array
If i have below JSON (@CarDataJsontest) e.g { “house”: { “gate”: [ “Car1”, “Car911”, “Car3”, “Car4” ] } } If i need to do is to modify the car911 to car2 all i …
Get unique values using STRING_AGG in SQL Server
The following query returns the results shown below: SELECT ProjectID, newID.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],’;’) AS newID WHERE newID….