Confusing result in SQL Server

I have these queries: String sql = “update time_tracker set logout = ?, totaltime = DATEDIFF(second, login, logout) from Time_Tracker where userid= ? AND CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE())”; And select DATEDIFF(second, login, logout) as timeDiff from Time_Tracker where CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE()) When I run the first query and do a select * […]

T-SQL help in getting the row count, coluumn count and size of each table

I am trying to fetch the table name, number of rows, number of columns and size of table. I have written this stored procedure but I am not getting the correct row count: ALTER PROC abc (@table_name VARCHAR(30)) AS BEGIN SELECT t.NAME ,count(c.column_id) AS no_of_cols ,count(*) FROM sys.tables t JOIN Sys.columns c ON t.object_id = […]

Identify Gaps of Records in Date Range

Question: I want to identify what Records do not have two records per day. Rank tells me if there’s a second record for each day…. SELECT TR.tourid, TR.tourruntimestamp, TC.tourname, TC.tourfrequency, TC.unit, TC.division, RANK() OVER ( PARTITION BY TR.tourid ORDER BY TR.tourruntimestamp) AS [Rank] FROM DBO.tbltourrun AS TR INNER JOIN DBO.tso_piml_tour_config AS TC ON TC.tourid = […]

SQL to calculate age based on year and month

I want to get a list of names who are turning 14 year old and 11 months. Can someone to help me fix the SQL below? Thanks! SELECT NAME FROM TABLE WHERE (cast(datediff(DAY, DOB, getDate() -1) /(365.23076923074) as int)>=14 AND cast(datediff(MONTH, DOB, getDate() -1) % (12) as int)>=11) OR (cast(datediff(DAY, DOB, getDate() -1) /(365.23076923074) as […]

Converting Time and Date represented as INT into DateTime structure in LINQ

I am currently querying the msdb.dbo.sysjobhistory table to find the status of the jobs executed today and show them in a browser. The definition of this table looks like this. Here run_date and run_time correspond to the date and time when a particular job was executed both represented as int of course. I am trying […]

Two foreign keys reference one table and null able foreign key

I am new to Database tables and relationships .I need some help for the below requirements Work flow 1. Hospital will have Male Patient 2. Hospital will have Female Patient 3. Hospital Will have Couple Patient but in RegTable it will stored as separate record for male and female. For the above requirements i have […]

Find duplicates with different values in T-sql?

I got situation to find duplicates from different sourceport system. for Ex: I got table like below: declare @table table (id int,portnumber int, [sourceport] varchar(50), sourcereportedDate datetime ) insert into @table values (1, 1111, ‘north’ , ‘2016-08-20 09:44:30.847’) insert into @table values (2, 1111, ‘north’ , ‘2016-08-21 09:44:30.847’) insert into @table values (3, 1111, ‘north’ […]

T-SQL Date Check between Parent and Child Tables using after triggers

Here’s an example: We have 2 tables: CREATE TABLE [TASK] ( [pk_TASK] [int] IDENTITY(1,1) NOT NULL, [TASK_FROM_DATE] DATETIME NOT NULL, [TASK_TO_DATE] DATETIME NULL ) CREATE TABLE [JOB] ( [pk_JOB] [int] IDENTITY(1,1) NOT NULL, [FK_TASK] INT NOT NULL, [JOB_FROM_DATE] DATETIME NOT NULL, [JOB_TO_DATE] DATETIME NULL ) I am aiming to encapsulate the logic in a trigger […]

Passing DateTime parameter to stored procedure using EF(DB first)

I created a stored procedure in SQL Server 2012 ALTER proc [dbo].[select_alltypes] @cdin_startunstufdate1 DateTime = null, @cdin_startunstufdate2 DateTime = null AS BEGIN SET NOCOUNT ON; SELECT 0 as shipid, i.cdin_cdindexid, p.pinv_PerformaInvID, coalesce(i.cdin_serial, 0) as depno, coalesce(convert(datetime, left(convert(nvarchar, i.cdin_startunstufdate, 120), 10), 120),’-‘) as deidate, coalesce(i.cdin_goodsDesc, ‘-‘) as gooddesc, coalesce(i.cdin_Customdeclar, ‘-‘) as custdec, coalesce(i.cdin_NoofPackages, 0) as pkg, […]

SQL Solve a Function with a While Loop

Using SQL server, write a while loop to calculate and print the functional value of f(x) = 3x&3 -2X^2 + 15 for all odd non negative integers <= 25. ( 1, 3, 5, …23,25). This is what I have written out so far And this is the result I get So theoretically I should be […]

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