Tag: tsql

Insert dynamic WHERE condition

I would like to dynamically search for results inside table. That is why I’ve created @MEST_CDO_SEARCH variable. This variable looks like part of the query, after it is dynamically filled with results: DECLARE @MEST_CDO_SEARCH NVARCHAR(MAX); SELECT @MEST_CDO_SEARCH = STUFF((SELECT ‘OR MEST.MEST_CDO LIKE ”%’ + _MEST_CDO2 + ‘%” ‘ FROM @tblEAN128 FOR xml path(”)),1,3,”) After this […]

How to get the Employee records in different scenarios

I have a stored procedure in my database to return the employee details from Employee table to my application. SPHR_Employee_Get(@P_PK INT,@P_ACTIVE TINYINT) Employee table structure is EMP_PK INT, EMP_NAME NVARCHAR(200), EMP_ACTIVE TINYINT Values for EMP_ACTIVE: 0 = InActive / 1 = Active My requirement is: I need to get the employee(s) in the following scenarios, […]

TSQL Merge 2 Ids into Temp Table

I’m working on a stored procedure in TSQL on SQL Server 2012. When I call the stored procedure. My problem: I’m inserting the values into a table Projects and want to store the inserted.ProjectId together with oID from the importing table into a temporary Table; I don’t store the oID from the importing table into […]

Divide COUNT Column By COUNT(DISTINCT(Column To give avg order size SQL SERVER

Ok I have a list of the months orders so that bit is easy. SELECT COUNT(*) AS ITEMS, For the next part easy to: COUNT(DISTINCT(PICKSET_NO))AS PICKSETS, Its the next part I cant work out: SUM(ITEMS/PICKSETS) AS AVGPICKSETSIZE FROM dbo.orders Thanks for your help on this. Here is the code in one block. SELECT COUNT(*) AS […]

Next Booking Record with Function and table

Now I am trying to find the next available booking for a patient with the help of UDF, as I am not sure I can do this in some other way. This is my UDF: CREATE FUNCTION [dbo].[fnGetNextBookingForPatient] (@PatientId BIGINT, @BookingStartTime SMALLDATETIME) RETURNS TABLE AS RETURN (SELECT TOP 1 BookingId As NextBookingId, C.Color As NextBookingCatColor, […]

Eliminate duplicates based on Order By SQL

I have a table with more than 20 columns and more the 300.000 rows out of which the relevant columns for my question are the following: column1 |column2 |column3 | date 123 | 657 | 2222 | 20 dec 123 | 658 | 2222 | Null 124 | 543 | 3333 | Null 124 | […]

Grade For Student Based on Different Conditions

Hi All, I am having marks of student in Different Subjects. Table #Maths Contain marks in maths of students named as a,b and c. Similar for #Science and #English. Now i want output as if student have more than 75 marks in two subject he will be given grade as ‘Merit’.If he is having more […]

Custom T-SQL Order By (IComparer)

Does T-SQL have any ability to do custom comparison of strings (for sorting), equivalent to .NET’s IComparer? Something like the ability to give Order By a user defined function that takes 2 strings and returns a value representing how they compare (greater than, less that, equal to)? I currently have a C# ICompararer implementation that […]

Batch deletion correctly formatted?

I have multiple tables with millions of rows in them. To be safe and not overflow the transaction log, I am deleting them in batches of 100,000 rows at a time. I have to first filter out based on date, and then delete all rows less than a certain date. To do this I am […]

How to create a new database and tables inside it in SQL Server?

I’m using T-SQL in order to create a database, and then populate it with tables. The point is that I can create the database successfully, but then when I create the tables it adds them inside the master database, not the newly created one. Here is the code segment that I have: USE master; GO […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.