I am so close to answering this question, but can’t figure out the final bits. I am using the AdventureWorks2019 database. My code is: I cant figure out how to order the ‘over’ part of the query. I know it needs to be “rows between unbounded preceding and current row”, but becaus…
Tag: sql-server
Compare 2 time datatype
I have a table in SQL Server which has 2 columns StartTime AND EndTime. The datatype of both columns in time(7). So when I view data in the table it can look like this: 08:33:00.0000000 or 19:33:00.0000000 I want to return all the rows in the table where StartTime and EndTime conflicts with another row. Examp…
Unable to assign value to variable with convert
If i make it like that it returns me a 10 digit number, but if i try it like that it returns only 1 digit.Why,isn’t it the same thing? Answer In this line: The length is not being specified. The default length is 30, and char will pad the rest of the length with spaces. So @ID is already filled,
Trigger to detect whether DELETE or UPDATE is called by stored proc
I have a scenario where certain users must have the rights to update or delete certain records in production. What I want to do put in a safeguard to make sure they do not accidentally update or delete a large section (or entirety) of the table, but only a few records as per their need. So I wrote a simple
Need to lookup part data when there are multiple rows of the same part
I have a SQL Table that has parts with their own 2D barcodes. There may be multiple rows of the same part. Each row has a column with the Date_Time stamp. What I need is to get the latest part data and view one of the columns to see if that value is INT 1. Example below: Look up the
Extract nested data in JSON variable in SQL
I need some help extracting the data I need out of a JSON variable in SQL: I need to get management_account_id, Month and cost_to_client/details in a table, the problem I am facing is that the month is higher up on the hierarchy than the details section, so I’m going into each individual month to access…
Questions with Pivot SQL Server
Questions with Pivot SQL Server Good morning people I’m trying to pivot the table below with the sql server using the VALOR1 Column and ignoring the VALOR2 and VALOR3 column: Before After SqlQuery The problem is that I realized that in order to have one row per year, the VALOR2 and VALOR3 fields must be…
SQL – Select from Multiple databases
I have 10 databases all in the same instance and schema with identical tables. Looking to create a SELECT query which can use a variable of the database names and return all of the records into a single dataset. I’ve been looking around and put the following together as a test (the real select statement…
Split values from many columns accordingly over multiple rows
Firstly i obatin data from Excel and convert them into DataTable in C# project. Secondly i parse this DataTable into JSON string and send it to database as a stored procedure parameter. I want to perform the merge operation on some table with values from this JSON string parameter. Values from this parameter …
Pivot monthly data from the past years
Here’s the data from my original query: Store Name Month Transaction Count 0001 – Loc1 2018December 245 0002 – Loc2 2018December 261 0001 – Loc1 2018November 232 0002 – Loc2 2018November 218 0001 – Loc1 2018October 123 0002 – Loc2 2018October 144 0001 – Loc1 201…