Users Id Name ——————— 1 Robert Baratheon 2 Cercei Lanister 3 Jon snow Transactions Id UserId Date Amount NewBalance NewTotal —————————————…
Tag: sql-server
Numbering the rows by using PARTITION BY to filter results
I am trying to use partition by to name the rows and then eliminate the rows that are not needed in the solution. The error message I receive is: “Invalid column name ‘row_num’ ” The question is as …
TSQL: How to use charindex ? – Finding a specific word in a variable string between specific characters
I have this string: ‘Level 1 – Level 2 – Level 3 – Level 4 – Level 5 – Level 6 – Level 7’ I would like to get the Level 6 word and level 5 word apart. I did try to google a few things, but they are not really helping me in this case. Because the levels will
Calculate SUM between two dates SQL SERVER
I’m having two tables Calendar ProfileRate I want to calculate SUM. Something like this What I’ve tried: Please help me. Thanks Answer you have to do several steps to solve the problem: combine calendar records with profiles establish which profiles meet the calendar range total the value for each…
Get the all unique permutation and combinations of ‘where clause conditions’ for my table in SQL Server
I would like to see all possible unique scenarios of my data in a table. I can give the Input like Operators=[<,>,=,……] Operands=[mode, StartDate,EndDate,…..] I am expecting the result like 0 and 1/1/2018<1/12/2018 0 and 1/1/2020=1/1/2020 1 and 1/1/2018>5/5/2015 1 and 3/8/2015<…
Update column value based on the multiple rows
I have a table example: (Col3 is bit datatype) I want to select distinct from the table except for col3 and update col3 value to 0 if col3 values are different. (If all col3 values are same then distinct will return distinct rows). Here I am expecting the output to be: Any help? Edit When col3 values are same…
Is it possible to use SELECT DISTINCT and GROUP BY together?
Is it possible to use SELECT DISTINCT and GROUP BY clause together? I need DISTINCT to avoid duplicate values and make a single entry of the records, and then get the Total quantity of those duplicate values. For example, I have columns like Item Name and Quantity and their records are (product A,5), (product…
Creating a “specific” form of XML, without string concatenation in SQL server T-SQL
I have some weird requirements that demand a specific XML format. Due to short deadlines and my lack of skills I decided to do a fast solution where I generate the XML through string concatenation. and this gives the output which I need. However, I’ve been told numerous times that this is not best pract…
How can I add a Count with a condition from another table to a column/equation?
I currently have a table that calculates some player stats Results I am looking to also add the stat “Overall average % of field beaten”, but I am not sure how. The relevant columns in “EventResults” table are ‘EventID’, ‘PlayerID’ and ‘Position’ Ima…
SQL Transpose for timestamps
I want to ask, how to make output below : from data like this below : with rules the ‘Clock_in’ is the earliest and ‘Clock_out’ is the latest one of the ‘Timestamps’ group by ‘NIP’ Answer If you are using SQL server you can make sure of Cast and group by. output…