Asked for the logic yesterday, now I’ve worked a way around. I have a big table used for reporting (170k~ rows) and many of them are “duplicated” but with updated dates (e.g. old record says John Doe …
Tag: tsql
Identify rows with particular value across multiple columns
Each row has an Id and a Category Presence/Status combo. Sample code: DECLARE @sample_table TABLE ( Id INT ,Category_Alpha BIT ,Category_Beta BIT ,Category_Charlie BIT ,…
How to Run Two T-Sql Statement By Exec Command in SqlServer?
I want run 2 SQL queries in Exec instruction, how can I do this? ListOfLeaveRemainingInfo is a user-defined table type: When I run the stored procedure, I get this error: Msg 1087, Level 15, State 2, Line 33 Must declare the table variable “@LeaveRemainingTempTable” Answer Scratch the previous. @LeaveRemainingTempTable is a table variable. It therefore doesn’t exist within the scope
SQL Server : select all rows where Column does not contain any value from dynamic table of values
I’m using SQL Server 2016. I am searching TableA and want it to not return any rows where one of the terms from TableB exists in a particular column of TableA. Assume I have the following sample …
Can you perform more than operation in a T-SQL MERGE section?
I’ve been fairly successful with my attempts I need to identify and solidify the OUTPUT columns ( # thereof) and what part of the MERGE statement that #of columns truly correlates to. I am simply …
Multiple CTE where clauses referece CTW column
For example WITH UserDetail (UserId, UserName) AS ( SELECT TOP(10) U.UserId,U.UserName FROM UserTable U ), UserAction (ActionName,ActionType) AS ( SELECT TOP(10) A.ActionName,A….
Sql agent job last run status
I have N jobs with M steps in each, that could change any time(Add/delete some steps). Jobs have different schedule to run. I want a query to get last run status, job id, job name of each job. Is it …
I need to migrate data from one old table to a new table by storing appropriate CityId instead CityName
I’m migrating data from one table to another table in SQL Server, In this process what I need to do is “I have 10 columns in old table one column is ‘CityName’ which is varchar and in the new table, I …
SQL: Combining LIKE and IN in SQL , and Showing the Array of LIKE in a column
Below code works to combine Like and IN. But I need a column in the output with the values that I used for the Like: SELECT file_id FROM table1 as t WHERE archival_date = ‘20180108’ WHERE EXISTS(…
Serial number auto generation
Facing issue in auto generation of serial number. How can i use row_number() in proper manner or is there any alternative to achieve the expected results? Note: I want to write query without using CASE. Current Results: Expected Results: Answer You can try to use ROW_NUMBER window function in a subquery, then use DENSE_RANK window function to create no column