Today vs. same day last 3 weeks

I have data like in the below example, each cell is number of events. | 29/09/2016 | 06/10/2016 | 13/10/2016 | Today | Hour | |————|————|————|——-|——| | 470 | 649 | 652 | 140 | 0 | | 159 | 322 | 354 | 925 | 1 | | 874 | 399 | 412 | […]

SQL Server : sort a 3 columns table into a 7 colums table

I have a table with 3 columns name, value, ts like this: and I want to transform it into a table with 7 columns I1, I2, I3, P, COS, Wh, TS like: The data in table 1 is raw data, at every time stamp, there will 6 data being recorded. These 6 data (I1,I2,I3,P,COS,WH) is […]

SQL Converting to Decimal then Round to Nearest Half Number

I have 1 Table: Table 1: CREATE TABLE #TempTable (Time decimal(10,6), ID int) INSERT INTO #TempTable (Time, ID) VALUES (0.5,1), (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,0.10),(10,11) Which gives me: select * from #TempTable +———+—-+ | Time | ID | +———+—-+ |0.500000 | 1 | |1.000000 | 2 | |2.000000 | 3 | |3.000000 | 4 | |4.000000 | 5 | […]

Joining 3 tables and removing nulls from 2nd and 3rd table in SQL Server

I would like to join 3 tables and remove null in second and third tables where records doesn’t match due to join. I kind of got the result but not able to remove null and hence why when I take this query into SSRS I am losing a record. Here are my 3 tables and […]

Proper way to restore database transaction level from read uncommitted

I have to get data from a table which is excessively updated. Dirty read is not a problem for me. I decided to use read uncommitted in my stored procedure. Then I added this line before select: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED I learned that this code scope is connection, unlike nolock. I heard […]

MS SQL no warning when inserting DateTime to Date column

create table t1 (d date not null) insert into t2 (d) values (‘1988-02-01 14:23:30’); // inserting whole dateTime, no warning or error select * from [Test].[dbo].[t1] // 1988-02-01 Where can I set that ms sql server would throw error in case we are inserting whole datetime to date column. I noticed this because C# translates […]

Left Outer Join Query Extremely Slow – SQL Server

Huge issue with an SQL Server Query! So the query below utilizes JOIN commands in the query, but takes no less than 15-20 minutes to run! No idea how to better optimize this code 🙁 Any ideas on how to fix this issue? SELECT dbo.DB_Timetrack.[JOB #], dbo.DB_Timetrack.[Last Name], dbo.DB_Timetrack.[First Name], dbo.DB_Timetrack.HOURS, dbo.DB_Timetrack.BEGDATE, dbo.DB_Timetrack.LOCATNID, dbo.DB_Timetrack.DESCR, dbo.DB_Timetrack.CODE, […]

check is there any weekday inside time given sql server

I have been wondering is there any operator/function in sql server to tell about weekday between given day. Example :11-19-2016 to 11-29-2016 I want check if there is tuesday between that day?

Foreign key with large table vs small table

Does foreign key relation with larger table has any performance impact ? CREATE TABLE CHARTOFACCOUNT –Contains all accounts ( AccountNo VARCHAR(23) PRIMARY KEY, –, ) CREATE TABLE DEPOSITACCOUNT –Contains all deposit accounts(row less than CHARTOFACCOUNT) ( AccountNo VARCHAR(23) PRIMARY KEY, –, ) CREATE TABLE DEPOSITACCOUNTSTATUS –Contains all Deposit account status 1 to 1 relation ( […]

How to merge two SQL scripts?

My task is to automate some actions (deleting old rows from a DB), which now are done manually. Currently I have two MSSQL scripts. The first one fetches IDs, I have to work with, as well as a number of rows to delete for every ID. And it looks like that: SELECT e.batchId, b.Name, count […]

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