I am not finding the solution to this issue despite looking specifically for it. The below query is me trying to get the last decimal places to appear properly. select CAST(CAST(33 as float)/100 as …
Tag: tsql
Subtract value from previous value with exception
I have some calculations where a normal LAG function would solve it for me, which Ive tried. However. My question comes with an exception. Every january and july the value resets to 1500. On row nr 2 …
Rounding script works to the nearest 15 minutes based on duration, how to shift this threshold by negative 30 seconds for decimal hours
I have a script which rounds a time length to the nearest 15 minutes. So for example 00:23:00 minutes to 00:37:59 duration will return 0.5 (decimal 30 minutes) etc continuous around the clock. I am …
How to multiply all values in one column to make one number?
In SQL Server, I am trying to multiply all values of a calculated column in a table in descending date order, to produce one number. This is so I can calculate a factor that can be applied to a different table / value, i.e. multiplying every value in the “price rate” column to produce a “pri…
UPDATE ACROSS JOIN SYNTAX
I am trying to get an update statement to function how I need. I’ve tried the following with INNER / OUTER LEFT / RIGHT JOINS. I continue to have records that are not updated in the usertype2 table despite having usertype3 child values. Am I going about this the right way? Usertype2 and Usertype3 will a…
Logic for backward updation based on parent value
I have below tables with data as below Table_Cell Table_column Table_row I need to update SwitchCol in Table_column and Switchrow in Table_row Logic is If for an column_id in Table_column, all the combination with row_id in Table_row,present in table Table_Cell is 1, then make the SwitchCol =1 for that column…
Best practice for verifying correctness of data in MS SQL
We have multiple tables with different data (for example masses, heights, widths, …) that needs to be verified by employees. To keep track of already verified data, we are thinking about designing a following table: This table links the different product id’s, tables and columns that will be verif…
Assign Unique Group Id To Sets of Rows with Same Column Value Separated by Other value
I have some data that looks like this: What I want is for each group which has the same radius value to have its own unique id, for example: What I don’t want is the second group with radius 10 to have the same groupid as the first group (not 1). I’m working on SQL Server but the solution should
How to loop through table using while loop and create another table with values needed
I have two tables: MainTable and MyTable. MyTable has unique ControlNo and ID. I need to add very first EffDate from MainTable to MyTablebased on ID and ControlNo. For that I need to look at …
Alternate approach to WITH CTE and large UNION query
I’d like to rework a script I’ve been given. The way it currently works is via a WITH CTE using a large number of UNIONs. Current setup We’re taking one record from a source table, inserting it into a destination table once with [Name] A then inserting it again with [Name] B. Essentially cre…