Tag: tsql

Why i get error in sql server ROW_NUMBER()?

I’m new to SQL Server and write this query: SELECT ROW_NUMBER() over (ORDER BY TelNo ) as RowNum, Telno FROM [ClubEatc].[dbo].[GetOnlineBills] where RowNum=1 When I run that query, I get this error: Msg 207, Level 16, State 1, Line 11 Invalid column name ‘RowNum’. How can i solve that? thanks all.

sql scalar-valued functions incredibly slow

SQL Server 2012 – I have a view (complicated) and one of the columns needs to have anything non-numeric stripped out. The following works to a point; STUFF(dbo.campaign_tracking_clicks.tt_cpn, 1, PATINDEX(‘%[0-9]%’, dbo.campaign_tracking_clicks.tt_cpn) – 1, ”) AS emailerid I get an error if anything but numbers are at the end of the value. I have a scalar-valued […]

Select most frequent values alongside with sum

I want to find most frequent product each Employee has sold alongside with product with most income. I wrote some example to be more clearfull. DECLARE @Products TABLE(ID INT, ProductName NVARCHAR(50), Price DECIMAL) DECLARE @Employees TABLE(ID INT, EmployeeName NVARCHAR(50)) DECLARE @Sales TABLE(ID INT, EmployeeID INT, ProductID INT, Quantity INT) INSERT INTO @Products VALUES (1, N’Product1′, […]

Convert Decimal to String on Pivot

example Code Declare @table1 VARCHAR(MAX) Set @table1 = ‘Select * from @tempTbl’ Declare @List VARCHAR(MAX), @Pivot VARCHAR(MAX) Select @List = ISNULL(@List + ‘,’, ”) + TrxCd From TransacMaster Where Module = ‘CB’ Set @Pivot = ‘ SELECT ROW_NUMBER() OVER (ORDER BY DebtorCd ASC) As RowIndex, * FROM ( Select Distinct c.UserId, d.Name, b.TrxCd SUM(b.Amount) As […]

TSQL merge fails for for identity column even if code is not run through

I’ve written an application which creates T-SQL code to merge data from one database to another. The structure in of both databases are equal. My generated code fails if the table I want insert to has an autoincrement column. I solved it temporarily like this: if the table contains no autoincrement column, I just merge […]

MSSQL query optimization (Sub Query, Correlated sub query)

Is there anyway I can optimize this query? Currently it takes me half an hour to run the query below. I have tried it as a subquery and tried adding the Outer Apply instead as well, but I see no improvement in performance. How would I go about improving this performance? Here is the query: […]

FUnctions on Datekey

I want MonthName and MonthNumber from Datekey variable EX:20141217. I need output as Month Name: December MonthNumber: 12 I tried to convert Datekey into DateTime variable using expression convert(datetime,convert(varchar(10),InvoiceCreateDateKey,120)) and top of it I used Month function to get MonthNumber. MONTH(convert(datetime,convert(varchar(10),InvoiceCreateDateKey,120))) and this following expression for MonthName DATENAME(m,convert(datetime,convert(varchar(10),InvoiceCreateDateKey,120))) I want to know are there any […]

is there a way to write async sql queries in sql server

I’m working on a sproc that needs to run several independent select statements. So let’s say I have 3 select statements like this: select * from x into #x select * from y into #y select * from z into #z Let’s say each select statement takes 2s to complete. If I run the sproc […]

Pivot function running long

The query is long running without producing any results. Is there anything missing on the above code?Please advise. IF OBJECT_ID(‘tempdb..#temp1’) IS NOT NULL DROP TABLE #temp1 GO CREATE TABLE #TEMP1 ( CounterSeq nvarchar(200),COUNTER INT ) INSERT INTO #TEMP1 VALUES (‘Counter1′,’1’) INSERT INTO #TEMP1 VALUES (‘Counter2′,’2’) INSERT INTO #TEMP1 VALUES (‘Counter3′,’3’) INSERT INTO #TEMP1 VALUES (‘Counter4′,’4’) […]

SQL Server : case when in where clause

Can you teach me what am I doing wrong in my case? 🙂 I am selecting invoices and repayments. Error: Msg 4145, Level 15, State 1, Line 10 An expression of non-boolean type specified in a context where a condition is expected, near ‘end’. Code: select I.subject1, R.subject2 from dbo.invoice I left join dbo.repayments R […]

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