Tag: sql-server

Retrieving count – LEFT JOIN VS SELECT COUNT

I have a situation where I have two possible routes to go down and I was wandering if someone could help me decide which one is better and why. I have tried to look at the execution plan but I cannot tell which one is more performant. So what I need to do is get […]

Compare List SQL Query

I have a table like this: table t1{ida,idb,idc} and I have a list of records in tabletype variable table t2{idb,idc} now, I want to check that doesn’t exist any group of t1 same as all of t2 where group i by ida

Sql data transformation

This is my source table Reference ModifiedDate Teachers Students SchoolID ETC ————————————————————————- 1023175 2017-03-03 16:02:01.723 10 25 5 1023175 2017-03-07 07:59:49.283 15 50 15 1023175 2017-03-12 11:14:40.230 25 6 5 1023176 2017-03-04 16:02:01.723 11 35 8 1023176 2017-03-08 07:59:49.283 16 60 25 1023177 2017-03-15 11:14:40.230 15 7 2 I need the following output Reference StartDate […]

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 […]

Prefix 0 in a Phone number string SQL

I have a phone number in sql table, i want to add prefix ‘0’ where ever phone number is 10 digit, but if it is less than 10 or greater than 10 digit then no 0 prefix required. 7863176061 7724269820 2088076157 1992762084 1318912 output 07863176061 07724269820 02088076157 01992762084 1318912

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, […]

Surrogate vs. natural keys specific example

After lots of reading, I decided to make surrogate keys for my tables. Now I have a dilemma on creating a 1:1 relationship on two tables, “Orders” and “Form_VSA_albums”. 1:1 because one form can only have one order and one order can only have one form. Note, there CAN be orders with NO form but […]

Existing SSIS Package conversion to point to Azure SQL Data Warehouse

Migration of on-premise SSIS packages to Azure SQL Data Warehouse. Can someone suggest references or ideas/steps involved in modifying existing SSIS packages that loads a on-premise sql data warehouse to populate a SQL Data Warehouse? Is this possible? Regards, KK

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