Tag: sql

SQL to convert column data to rows and increment column name

I’ve been using SQL for a while, but I’m still a noob so I am coming here for help. I searched around the site already but didn’t quite find what I’m looking to accomplish. I want to use SQL to take Two Columns that look like this: VisitNumber ID 39332 759666 39332 769445 39332 775795 […]

insert SET STATISTICS TIME into a table

Let’s say I have the following statement: SET STATISTICS TIME ON; Select * from myTable SET STATISTICS TIME OFF; Which produces the following results in the messages tab in ssms: SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. How Can I read Elapsed time value into a table/temp table/ […]

compare comma separated values in sql

I am trying to write a function to compare comma separated values in SQL I’ve taken some code from Internet : SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Split(@v1) WHERE ‘, ‘ + LTRIM(@v2) + ‘,’ LIKE ‘%, ‘ + LTRIM(Item) + ‘,%’ ) THEN 1 ELSE 0 END; Then I make a function […]

Copy identity and put it to another table

Good day! I’m having trouble with my stored procedure, here is my code: INSERT INTO dbo.CS_RefQuestionChoice(ChoiceDescription, QuestionID, RatingID, SetID) SELECT @choice, @@IDENTITY,@ratingid,1 FROM CS_RefQuestions The code is running smoothly, but I’m having trouble because it saves 75 records on my database, it should save only one, all I want this to execute is when I […]

Nhibernate won't delete rows while table has trigger

I have the following SQL Server table: CREATE TABLE [dbo].[Logins]( [LoginID] [int] IDENTITY(1,1) NOT NULL, [AbonnementID] [int] NOT NULL, [Zeitpunkt] [datetime] NULL, [IPAdresse] [nvarchar](255) NULL, [IstLogout] [bit] NULL, CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED ( [LoginID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) […]

Creating a new column based on data from an existing column

Consider a system to track repairs. I have a table with customer data and a common id key. I have a second table with a column to show which type of repair part was used on each id key and how many were used. Their definitions are below: order_information order_id | cust_last_name 465 Smith 899 […]

Trigger and stored procedure security in SQL Server

I have a database (SQL Server) that is being used by 20 users, all members of the same security role. The role enables them to insert, delete and update to Table1, but they have no permissions for Table2. Table1 has a trigger that fires a stored procedure, Table2_Refresh, that truncates Table2 and rebuilds it from […]

Listing New Customers Per Month

This is kind of a part two to a previous question I asked and already got an answer for. There I had wanted a count of all new customers per month, per year. Now I want to actually see a list of who is new, by email address (but I don’t really understand the code […]

SQL filter result to current month and previous two months

Here is my sql code: SELECT Month = datename(month,dateadd(mm,datediff(mm,0,StartTime),0)), Cast ((ROUND(((CONVERT(DECIMAL(10,2),SUM(Duration)/60)/60)),2)*55) AS numeric(36,2)) FROM VExecutionGlobalHistory Where Tester <> ‘dit2988’ AND TestTypeID = 1 group by dateadd(mm,datediff(mm,0,StartTime),0), year(dateadd(mm,datediff(mm,0,StartTime),0)) order by dateadd(mm,datediff(mm,0,StartTime),0), year(dateadd(mm,datediff(mm,0,StartTime),0)) This query returns: April|123 May|3456 June|856 July|345 I want it to return the following: May|3456 June|856 July|345 Does anyone have any ideas? Im using […]

SQL Server DW – Query data using Date dimension and Time Dimension

Before in the source systems I could do something like below SELECT COUNT(*) FROM FlightDeparture FD WHERE FD.[Response_ts] BETWEEN ‘2013-01-01 45:01:38.000’ AND ‘2013-01-02 15:04:20.000’ NOW we have a DW where there is a separate date dimension (Dim_Date) and time dimension (Dim_time), I tried below query but I am not getting same counts SELECT COUNT(*) FROM […]

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