I have a few Personnel Details tables in a database in SQL Server 2014. There is a view that pick up selected data from these tables by implementing a few joins. One condition to select the row is if its columnn isActive = 1 in NominalRoll table. There is a report in SQL Server Reporting Server that calls thi…
Tag: tsql
T-SQL Equivalent of regular expression ‘b’
I’m in the process of converting a CLR function that utilizes regex to a SQL function. I know that SQL Server does not fully support regex, but i just need one case to work which is a word search. Search Field Value: {“Id”:1234, “Title”: “The quick brown”} Regex Patte…
TSQL – Conditionally “Cross Join” Records
Code (staging sample data): Detail: @LookupTab is basically a filter that will have either 0, 1, or both values (111 – Option 1 and 112 – Option 2). @DataTab is the actual data in the table (can be huge). The Val field in this table can either be 111 (Option 1), 112 (Option 2), or 223 (Both Option…
Identity column as part of primary key
Suppose, I have a composite primary key that consists of 3 columns: [ShardKey], [SiteId] and [ServiceId]. Column [ServiceId] is an identity column and should start from 1 for a new combination of [ShardKey] and [SiteId], but SQL server fails – the column [ServiceId] never starts from 1, it just incremen…
Get IN and OUT date from booking table
For example I have this kind of table ———————————————————————- | ID | USER | READER | DATETIME | ——————–…
T-SQL sub select to retrieve rows with columns that have values instead of NULLs
This example is as close as I can demonstrate for the problem I’m trying to solve. For this case the sub-select has some mostly duplicated rows with the only difference being that some have a delvieryDate and some do NOT. If a row of data is available with the date, I want that. If not I’ll need t…
Query to fetch in-out time for all employees from one table
I need to find first in and last out time for all employees from a table. I have tried the following query. This query return the records for a particular employee. But I have to find the data for all employees. Sample Table data: Answer you can use this.
SQL append data to table using dense_rank()
I’ve been trying to piece together bits of code that I’ve googled but haven’t had any luck. I’m trying to add a new column to an existing table. I can view the column using the below code from a select query: I basically want to append the results from this select query to my original …
How to get conditional SUM?
I am trying to get a conditional sum based on another column. For example, suppose I have this dataset: If there is a type of 1 then I only want that data for that data, else if there is only 0 then I want that data for that date. So for 12/16/2019 I would want the value 0. For 12/23/2019
Tag column values contained in other rows
Suppose I have the following table as an input : +———–+————–+—————-+——————+ | tableName | duplicate_Id | Index_name | Column_List | +———–+——–…