I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID Here is a sample dataset that I have created: Answer I think that the simplest approach to this gaps-and-is…
Tag: sql-server
Is It Possible to Round a Number Smaller Than 1 In SQL While Keeping In Decimal Form
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 …
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 …
How to fix that procedure in sql
I created procedure which count not null rows in the column, but query throws errors: @tableName is not declared and invalid object name tempTable. I don’t know why code throws that errors, because all variables are declared. Also when I make that program in another way, that code throw Msg 1087, Level …
Partitioning rows into groups by accumulative time interval
I got a search sessions log that looks like this: My task is to partition each row into session groups. Session groups are up to five minutes. For example: Those TOP 3 sessions will form a group session 1 – if we accumulate the minutes between each row, we will get 3 minutes and the 4th would accumulate…
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…
How do I configure one SQL Server to connect to another, so it appears query data is coming from only one connection?
Somewhere recently I read of a method to connect one SQL Server to another, so clients could connect and run queries that appeared to come only from the first server. I cannot remember the proper …
Efficient way to handle below query? in SQLServer or PostgreSql (Self join)
I have an employee table as below I need to select employee_id, manager_id, result. result should have true or false depends on below conditions. If the employee is manager for someone then true If the employee has a manager then true I came up with a query, but I need to know if there are any other better wa…
Get the immediate parent child relation in a table
I have a table with following value I need the output data from the above in the following format : Thanks in advance for any guidance on this. EDIT : I used the while loop approach to achieve this but trying to avoid the while loop. The while loop logic worked if proper sequence of ranklvl is there for a
How to Get list of ids from same query when returning as JSON
apps table I have a query, SELECT * FROM apps WHERE app_status = “ACTIVE” FOR JSON PATH; This query has an app_id field. I also want a field to accompany the json output with a field ids with all the app_id as a list. So instead of: I would have: Answer This returns the correct JSON Data Query Out…