I’m looking for a query where I need to show all the Suppliers from the Suppliers table that doesn’t have products from category 1 (Products.CategoryID = 1). Whenever I run it it always gives an error. Side question: How do I get these results but with suppliers that has products from cat. 6 ? (So…
Tag: tsql
SQL. Join with filter and offset
I need to create a SQL query that will return images with tags. The result should be ordered by some column and filtered by UserID and tags. The result should be paginated. There is a SQL statement This request is not working as expected. I expect to get 50 images, but they will be combined with tags and the …
Count unique values in a table with group by, with and without where condition
I have a table: FUND DATE ID POST ACAT Friday, January 1, 2021 10058 5056 ACAT Friday, January 1, 2021 10058 5056 BCAT Friday, January 1, 2021 32598 5004 ACAT Monday, February 1, 2021 10058 5056 MISS Monday, February 1, 2021 10058 5056 CCAT Monday, February 1, 2021 32598 5004 DCAT Monday, March 1, 2021 10058 …
Query to display Employee and Manager
I have to write a SQL Server query to display the employee last name and his respective manager’s name, as given below. These are the schema for the two tables. Expected Output : I tried this code but its not right Detailed Schema: Schema Answer I guess something as simple as this should do it How it works: T…
(SQL) Create Table with Distances from Separate Table with XY Coordinates
I am trying to create a table that will have unique distances between slot machines based on the following dataset. I know I first need to calculate my distances and the only way I currently know how would be to use the following code: But this obviously only returns the distance between the first two machine…
SQL – % Breakout by Hour
I’m trying to write a query that will break out what percentage of time is utilized within a hour given a time range. Sample data: declare @date table(id int, Start_time time, End_time time) …
T-SQL – subqueries on rows grouped by ID to create a summary table
I have a table “MyTable” with an id and two int fields “A” and “B”: MyTable +——-+—–+—–+ | ID | A | B | +——-+—–+—–+ | 99 | 0 | 1 | | …
Split records into buckets based on counts in reference table
This is a simplified version, stripped down to my core problem. I have a ContactData table with millions of rows of data, with each contact record broken up into categories with a ReferenceID. I now have to assign a new UpdatedValue to each contact record, based on counts from the separate NewValues table als…
Dynamically fill an SQL table by detecting a name?
I want to dynamically fill a SQL table by detecting duplicate names. So, when I use an insert statement on this table – if the name already exists it adds an entry with the same ID. What I mean by this, for example I’ve got this empty table: Table Domain: DomainID Name URL StatusID ErrorID Date So…
Transpose row to column in SQL Server
I have a table like below: The first row is the header. Now, I would like to transpose table into this I have tried unpivot but the result is not desired table. Thanks in advance, Answer I recommend using cross apply to unpivot and then aggregation: I much, much prefer this over pivot/unpivot. Why? APPLY impl…