Tag: sql

SQL Combine duplicate columns to 1

I have this query select im_Call,IM_DR from cadw.tiburon.inmast m where IM_DATE < Convert(date,GETDATE()) AND IM_DATE >= DATEADD(DD, – 4,Convert(date,GETDATE())) which returns results similar to this I need to take the records that are highlited in yellow so I get results that would return 1 row with the im_Call number with the IM_DR column showing both […]

SQL Query using inner join

CategoryTable Code Name 1 Food 2 Non-Food Existing Table Consists list of category, as for example, I have two only Food and Non-Food As challenge, I am assigning tenants with category or categories (multiple assignment, as there are tenants which are categorized as food and non-food). I i used to insert Tenant and Code to […]

Splitting a Column Using a Separator Character in SQL Server

Here is an example row of data stored in this column ‘88922774 8785075 9417025’ I am trying to return all 3 separate numbers with the ‘ ‘ separator. It’s also possible for there to be a 4th column in some cases. Usually for splitting strings I would have used the following PARSENAME(REPLACE([ColumnName], ‘ ‘, ‘.’), […]

SQL Server query with update error from select query

update ItinventoryR1 set iin_userid = (select emp.empid from employee emp, itinventoryr1 it where ltrim(rtrim(it.iin_username)) in (emp.empname)) Error is : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Query Performance with and without join

Suppose We have two Tables Customers and Orders Customers Table:- custid custname ——- ——— 1 aaa 2 bbb 3 ccc Orders Table :- orderid custid date ——– —— —– 101 1 2016-03-01 102 1 2016-03-03 103 2 2016-03-01 Now , we have show customers who have placed no orders We can do it several ways:- […]

C# refund SQL syntax

I wrote an inventory software; I have 3 forms 1st one had items to inventory 2nd one sell items from inventory and the last one we can refund the items we sold. I have problem with the refund command, here is the code: try { mycon.Open(); int y = 0; for (int i = 0; […]

Alphabetic ranking a list with set of 'WITH TIES – ORDER BY (multiple fields)

A regular usage of “WITH TIES” & the result of the query : Demand: The best of 10 movies as Oscar Wins SELECT TOP 10 WITH TIES F.FilmName AS TITLE, F.FilmOscarWins AS [OSCAR WINS] FROM DBO.tblFilm F WHERE FilmOscarWins IS NOT NULL ORDER BY [OSCAR WINS] DESC; Resullt of the first query When I add […]

Error: Ambiguous column name

Why am I getting this error? Msg 209, Level 16, State 1, Line 94 Ambiguous column name ‘New Value’. Query: SELECT aho2.[Control Number] AS [Control Number], STUFF((SELECT ‘; ‘ + [Old Value] as [text()] FROM #AuditHistoryOutput aho1 WHERE [aho1].[Control Number] = [aho2].[Control Number] FOR XML PATH(”)), 1, 1, ”) [Unset Choice Value], STUFF((SELECT ‘; ‘ […]

Subtracting Two Case Statements

Database: SQL Server Hopefully this is really simple. I have a purchase order query and I am trying to subtract two case statements but I am having issues getting it to work. I am using temp tables to pull the purchase order data in. See query below: –temp table for PO Inventory lines select prchseordr_id, […]

Optimizing rank() query

I’m using the following query, within a stored procedure, to get user’s ranking. While trying to optimize the database, I’m trying to determine if this query can be optimize as it is the most used query in my database. id column is indexed. SELECT @p_rank = all_time_rank FROM (SELECT user_rankings.*, RANK() OVER (ORDER BY score […]

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