Tag: tsql

When to use a negative index for substring

When does it make sense to call SUBSTRING with a zero or negative index, considering the same result can be achieved with an index of 1. These queries produce identical results. They truncate the rightmost character. But why does TSQL even produce a result for the 3rd, 4th and 5th queries. SELECT LEFT(‘ab,’, LEN(‘ab,’) – […]

Creating a trigger in SQL Server for the appropriate table based on the logic

I have an InventoryOnHand table with a Quantity column. When an InventoryTransaction record is INSERTED, I need to update the Quantity in the InventoryOnHand table. Can someone please explain the following: Should the trigger created in the InventoryOnHand table or the InventoryTransaction table? I would assume the InventoryTransaction table since this is the ‘INSERTED’ When […]

Convert date format doesn't take effect on self made date string in SQL Server

I have a rather strange issue here. I have a date string, which I’ve created partly by myself to incorporate a variable. The problem is, that I’m setting another language settings. In this case, I have to also convert the string to fit the language settings format. I’m using this code: cast(convert(varchar, cast(cast(getdate() as date) […]

Pivot table how to not allow nulls in multiple columns

I want to put a condition if [2015] and [2016] are NULL then hide row, but I cant pull it off. I don’t have interest in the billname if it doesn’t have data. SELECT IssuedByUserGroup, BILLNAME, SUM([2016]) AS [2016], SUM([2015])AS [2015] FROM Sum_Orders PIVOT ( SUM(Sum_SellPrice) FOR OrderperiodYear IN ([2016],[2015]) ) AS pvt WHERE ( […]

permanent damage from SELECT query?

I want to execute some SELECT statements to get some data out of an application database (production environment, not test), can I cause any permanent damage? I’ve heard its not recommended to use SELECT * because it slows things down etc. but I assume there is nothing that could be affected in the long run? […]

How can i get the days between results dates for each seperate pat_id?

I’m looking to get the days between result dates for each patient: only looking at result dates where the result value is <90.00 ;WITH patient_results AS ( SELECT * FROM (VALUES (1, ‘EA11AEE3-1D90-4602-9A37-0000007E2293’, ‘85.10’ ,’2015-12-11′), (1, ’27BCD3E4-2381-4139-B420-0000025B4113′, ‘91.50’ ,’2016-01-05′), (1, ‘D8969360-45D6-487B-AF94-0000035F78B0’, ‘81.00’ ,’2016-07-21′), (5, ‘446E6413-442A-452A-BCF4-000006AA9896’, ‘58.00’ ,’2014-07-01′), (5, ‘00305129-BC14-4A12-8368-00000AC04A9B’, ‘53.00’ ,’2014-12-13′), (5, ’96A67E53-2D6C-430B-A01F-00000AE4C37B’, ‘42.80’ ,’2015-02-01′), […]

Not firing after update trigger when updating from insert trigger

I have two triggers on one table, one fires on insert and updates the row with additional info from other tables CREATE TRIGGER [dbo].[eTteamTg] ON [dbo].[entryTable] AFTER INSERT AS BEGIN UPDATE entryTable SET shiftTeam = (SELECT TOP 1 shiftTeamMemb.teamId FROM shiftTeamMemb WHERE shiftTeamMemb.personalNumber = i.personalNumber) FROM entryTable INNER JOIN inserted i ON i.ID = entryTable.ID […]

EXEC an INSERT with a dynamic Schema TSQL SSMS

I know that’s a little bit much, but I’m searching for a solution since 4 days. I just want to EXEC a INSERT, but I always get some cryptic Error Messages in SSMS INPUT(variables are wrong so the ErrorLog should get filled): USE [Tfs_GitInterface] GO DECLARE @return_value int EXEC @return_value = [dbo].[spInsertChangeLevel2] @repositoryName = N’asdasd’, […]

How to automatically trigger a query at specific time in SQL Server

How can I run this query using auto trigger 5 minutes before the time of next prayer? SELECT DATEADD(minute, -5, (SELECT TOP 1 TIME FROM prayertimes WHERE Time > sysdatetime())) AS TIME Here are details of the prayertimes table: Id P_id TIME ——————————— 698 3 2016-10-08 15:31:00.000 699 4 2016-10-08 18:02:00.000 700 5 2016-10-08 19:32:00.000 […]

How do I sort a SQL Server 2008 pivot result columns and rows?

I’m using This to get pivot results for a table. But I want the resulting columns and rows both in a specific order. So the rows would be sorted by CDATE and the columns would be CDATE, BALANCE, then the DATE columns. CDATE | BALANCE | 04-2007 | 05-2007 | 06-2007 | TRANS TOT 2003-01-15 […]

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