In database I have two table. I want to join two tables and get last row each SensorType Sensors table: Id CodeNumber PortNumber SensorType IsActive 13 Dht11 3 0 1 14 Dht11 3 2 1 17 Global 100 4 1 18 Dht11 3 1 1 19 Dht11 3 3 1 SensorsValue table (SensorId is foreign key Sensors table): Id SensorId
Tag: tsql
Convert multiple rows into single row with more columns
I have a SQL Server table of properties related by an id and want to do a query to check if an id has a set of properties. Basically I want to do a transform as follows, grouping by the Id and adding a column for each unique property: Starting table: Id Prop Value 1 P1 V1 1 P2 V2
How can I check if the ID’s of a table match another ID’s of another table in SQL
So I have 4 parameters, one of them its given as an input parameter of a stored procedure (its like a code that represents an unique action), and I need to match these ID’s with another ID’s in another table, so I need to check in table 2 the ID’s “DeliverTypeID”, “Schedule…
Get the first record from Table 2 where timestamp is greater than timestamp in Table 1
I have two tables. EVENT_NARATIVE EventNum SEQ Message Message_Time GRADE_HISTORY EventNum Grade GradeChangeTime The EVENT_NARATIVE Table has a lot of data (millions of rows) and each Event (EventNum) can have multiple EVENT_NARATIVE records. The GRADE_HISTORY table contains thousands of rows and each EVENT c…
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…
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
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…
How to check the current row with next row and update the current row not using while loop and cursor
Is there any way other than using the cursor or while loop to check the current row with the next row and update the current row and do this through all rows in the data set? For example, this is the table we have: This while loop go through each row and update @new_value based on some conditions and then
Update column by removing text in braces
I need to update single column for all the rows in a table. The update should happen in such way that data needs be filtered out by removing all text where braces are appended for the string and swap the strings separated by ‘,’. Example: As per the above example, I need the final data to be somet…