I have this table Employee_Hire which has Hire date of an employee (ID) in a Department. Now I want to write a Select query which adds one more column (Last_day) to show last day he worked in that …
Tag: sql-server-2012
Creating a recursive query in SQL
I have data like this: Person A has a relationship with Person B, and person A has a relationship with Person C Person B has a relationship with D and E. I want to view result in group in SQL Server (A, B ,C,…) and (B,D,E,…) I have tried looking recursive but not getting this to implement. I need …
Insert Select into with same ID
Insert Split Delimited Strings in a Column with same ID. Lets say I have demo setup as follows: I declare some variables Note: the @addresses sting is dynamic and not fixed Now my aim is to insert into table EMPLOYEEID_Address multiple address with same the id using the STRING_SPLIT function. So I tried: But …
Set row number for batch of N rows per category
I have table I want to create a another column in which i have to put row number. Logic : For 3 rows, it should be same and change to next value for 4th row. And Whenever the PHASE is changing, it should go to next number even if the set of 3 is not complete for the previous number.
SQL Server Multiple Rows into One row
I have a table with lot of records. I provided a sample of 1 record (4 rows) Need the rows to be combined as follows How can this be achieved? Answer You can use aggregation: Note that the data you describe sounds like it comes from an aggregation query. Usually this is more easily fixed by fixing that query …
Query to join two tables with like operator
I have two table which has data as below Table1 I would like to create where clause to get rows with like operator. for ex. But my problem is that there are more than 15-20 different parameter in where clause, it can also increase latter. So I have decided to create a new table in database lets called it Tabl…
Improve pivot view performance
I have a key-value structure for storing dynamic entities (standard EAV). I made a view for this Entity with the Pivot command. I tried to set the index for this view, but it is not possible due to the use of pivot. Is there a way to drag my data into SQL at the time of insert, Update? Because performance
SQL Update Only when Max Date reached
I’ve two tables like: Table1 Table2 The scenario is: If I update the Table2 on TID2 = 2, the LastStatus on Table1 shouldn’t be updated because there’s a MAX Date on Table2 with TID1=1. So LastStatus on Table1 will only updated if there’s an update on Table2 with MAX Date. Currently, I …
How to alias column into another operation
I want to use the Elias column startdate into anthor operation to calculate Answer Use CROSS APPLY to define the alias in the FROM clause. Then it is available throughout the query: Note that I added table aliases so the query is easier to write and read.
Declaring A variable inside dynamic ssql
I have the following string and would like to replace >30 at the end of the string with @value so I can pass it through a stored procedure. How do I declare @value. My orginal code had >35′ in place of vaule and worked fine. Thanks in advance. Answer You want sp_executesql(). Read through the docu…