I want to add logic that calculates price per claim. Below, there are two claims, one for patient 5, and another for patient 6. Original idea is to create a unique list of patient numbers in a …
Tag: tsql
Inserting an ID that increments (but is not an AUTOINCREMENT)
I have a table that I am trying to insert multiple records into using a select statement. The ID field is an INT and not autoincremented but I do need to increment in in the INSERT. The table belongs to a third party product we use for our ERP so I cannot change the property of the ID. The insert
Trigger to update a table whenever there is an insert
I am trying to use a trigger (in SQL Server) to update a table whenever there is an insert on the table but I get an error: Conversion failed when converting date and/or time from character string. Trigger used: The table table_scan is to be updated when there is NULL in start_date after an insert happens. An…
Returning multiple aggregated columns from Subquery
I am trying to extend an existing query by aggregating some rows from another table. It works when I only return one column like this: Then I tried to add another column for the aggregated volume: However, this returns the following error: Only one expression can be specified in the select list when the subqu…
Need to match a field with another that has commas on it’ s value
I would like to match the field values of “FORMULATION” from TABLE 1 to “C_TEST_ARTICLE” from table 2, that has mutiple of these formulation sepparated by commas. Table 1: +——–…
Insert row with uniqueidentifier
This is a snippet from a stored proc that creates a database. We have a table with uniqueidentifier PK. I’m trying to insert a row with an empty guid as the PK: The error messages are Can’t see what’s wrong with the VALUES clause but I guess it has something to do with the final quote, altho…
Arithmetic overflow error converting nvarchar to data type numeric in case query
I am trying to show the number of quotes won as a percentage of the total number of quotes in a time frame. For example. I have a total of 294 quotes and 173 of them are won. The result I am looking …
Unable to replace the hour in datetime using SQL
Unable to replace the hour part in date-time. The change is reflecting in both hour & year. Please help in rectifying the code. Expected output = 2021-02-13 11:35:32.337 Actual output = 1121-02-13 11:39:11.347 Expected output = 2021-02-13 22:35:32.337 Actual output = 2221-02-13 22:38:13.223 Answer REPLACE…
Add hours/minutes to datetime
I’m having an issue on how to add the Offset to the EventDateTime. Is there a way to do this? I would convert it to a decimal number but cannot because 5:30 is not 5.3 hours, it’s 5 hours and 30 minutes. The data is shown below with the expected output. Please help if possible. Sample Data: Expect…
Finding last and second last date and corresponding values
Consider the following schema (fiddle): For each meter.id I need to find the latest reading date, value, and the value difference vs previous reading. For the sample data my output would look like this (plus some other columns from meters): meterid latest value delta value 1 20090403 399 99 2 20090403 288 44 …