I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, ‘ABC1’, ‘XYZ1’, 2, 20 union select 2, ‘ABC1’, ‘XYZ2’, 3, 25 union select 3, ‘ABC2’, ‘XYZ2’, 1, 30 union select 4, ‘ABC2’, ‘XYZ1’, 4, 35 declare @sql … Read more
Use CHAR_INDEX twice: SELECT *, SUBSTRING(path, pos1 + 1, pos2 – pos1 – 1) FROM tests CROSS APPLY (SELECT NULLIF(CHARINDEX(‘\’, path), 0)) AS ca1(pos1) CROSS APPLY (SELECT NULLIF(CHARINDEX(‘\’, path, pos1 + 1), 0)) AS ca2(pos2) — NULLIF is used to convert 0 value (character not found) to NULL Test on db<>fiddle 1 solved How to … Read more
Not clear whether you want your result as a VARCHAR or an actual TIME type, so here’s both: DECLARE @Table TABLE ( TXN_TIME VARCHAR(6) ) INSERT INTO @Table SELECT ‘053124’ UNION SELECT ‘173932’ UNION SELECT ‘011815’ UNION SELECT ‘120349’ UNION SELECT ‘134207’ SELECT TXN_TIME, LEFT(TXN_TIME,2) + ‘:’ + SUBSTRING(TXN_TIME,3,2) + ‘:’ + SUBSTRING(TXN_TIME,5,2) + ‘:000000’ … Read more
I would read the “not certified on any” to be a check for the existence of a row. If a matching row exists, then don’t return the employee. Only return the emplouee if a matching row doesn’t exist. How would you find a matching row, to find out if an employee is “certified on any”? … Read more
You seem want : select t.* from table t where not exists (select 1 from table t1 where t1.group = t.group and t1.name=”xxxx”) 1 solved Multi not like expression sql
declare @timediff table(id int identity(1,1),Name varchar(50),logtime datetime) insert into @timediff Select Name,logtime from timediff declare @datetimeDiff int declare @i int=1 while(@i<=(Select count(*) from @timediff)) Begin Select @datetimeDiff=datediff(hour,logtime, (Select logtime from @timediff where id=@i+1)) from @timediff where id=@i if(@datetimeDiff>=1) BEGIN Select @datetimeDiff –You can write your update code here END Set @i=@i+2 END 0 solved Need … Read more
First, figure out which records need to be updated: select * from tbl_order o inner join tbl_group g on g.grp_id = o.grp_id inner join tbl_indicator i on i.grp_nbr = g.grp_nbr and i.sect_nbr = g.sect_nbr where g.indicat != i.indicat Now, modify the query to update those records with the correct grp_id. Notice that I’ve added an … Read more
You are passing date and time in to your procedure, but when you convert those to dynamic SQL, you are using format 101, which removes the time portion. Try changing the 101 to 126 (there are 2 places within your code where 101 is used, make sure you change both of them. 0 solved compare … Read more
Constraint is on the table not a single statement Kind of ugly but Put it in a transaction and take a tablock begin transaction ALTER TABLE branchOffice NOCHECK CONSTRAINT ALL insert into branchOffice with (tablock) — Re-enable the constraints on a table ALTER TABLE branchOffice WITH CHECK CHECK CONSTRAINT ALL commit transation; 3 solved T-SQL … Read more
‘Transaction’ is a pair of take + return. It’s identity is computed from source data so OPERATORs could be grouped the way you need. The query may fail on data with unpaired OPERATORs. declare @tbl table ( OPERATOR int, PRODUCT varchar(50), [USER NAME] varchar(100), [TIME STAMP] datetime); insert into @tbl(OPERATOR, PRODUCT, [USER NAME], [TIME STAMP]) … Read more
I think DATEDIFF would be your best option. I know you are looking for the difference in time within the same day, but couldn’t you just pass DATEDIFF two DateTime types with the same date, but different times? // Should return 300 DATEDIFF(mi, ‘2015-02-19 08:00:00’, ‘2015-02-19 13:00:00’) 2 solved How do I get the minute … Read more
Something like this? SELECT TOP 4 g.GalleryID ,g.GalleryTitle ,g.GalleryDate ,MAX(m.MediaThumb) AS MaxMediaThumb FROM Galleries g INNER JOIN Media m ON g.GalleryID = m.GalleryID GROUP BY g.GalleryID, g.GalleryTitle, g.GalleryDate 3 solved How can I select 4 distinct values from 2 tables containing many rows of data in SQL Server?
One more method based on JSON. It will work starting from SQL Server 2016 onwards. SQL Server 2016 — DDL and sample data population, start DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(250)); INSERT Into @tbl (tokens) VALUES (‘250 Cambridge ST 305′), (’30 ACKERS AVE 2’); — DDL and sample data population, end … Read more
You can use APPLY : SELECT t.ID, MIN(t.startdate) AS StartDate, MAX(t.Enddate) AS EndDate FROM table t OUTER APPLY ( SELECT TOP (1) t1.Enddate FROM table t1 WHERE t1.id = t.id AND t1.startdate >= t.startdate AND t1.enddate IS NOT NULL ORDER BY t1.startdate ) t1 GROUP BY t.ID, t1.Enddate; solved SELECT Record that have start date … Read more
SELECT COALESCE(s.state, c.state) AS state , COALESCE(s.city, c.city) AS city , COALESCE(s.Suppliers, 0) AS Suppliers , COALESCE(c.Consumers, 0) AS Consumers FROM ( SELECT Tb_Supplier.State , Tb_Supplier.City , COUNT(Tb_Supplier.Name) AS Suppliers FROM Tb_Supplier GROUP BY Tb_Supplier.City , Tb_Supplier.State ) AS s FULL OUTER JOIN ( SELECT Tb_Consumer.State , Tb_Consumer.City , COUNT(Tb_Consumer.Name) AS Consumers FROM Tb_Consumer GROUP … Read more