Tag: sql

SQL-Server: How do I insert multiple rows with data from a temp table + store the ID of the inserted rows in the temp table

I am trying insert 1 row into an audit table for every row in a temporary table and store the ID of each audit table row in the temporary table. The columns inserted into the audit table will come from the rows in the temporary table and I will need to refer back to the […]

How to get date time 01/03/2017 16:06:21 AM format in SQL Server

I am working on a query in SQL server 2012 where I need to get datetime format as 01/03/2017 16:06:21 AM. How do I do that?

How get sum of count when using group by

SELECT [ReferredDoctor_ID] , [count] , SUM([count]) [returns] FROM (SELECT O.[ReferredDoctor_ID], COUNT(1) [count] FROM [dbo].[Order] O WITH (NOLOCK) WHERE O.agency_id = @agency_id AND O.Trash = 0 AND O.DateCreatedByServer BETWEEN @datefrom AND @dateto GROUP BY O.[ReferredDoctor_ID] ) dd GROUP BY [ReferredDoctor_ID] , [count] ORDER BY [count] DESC

Updating records depending on the intersection of dates

I have a table that looks like this: ID | Start | End | Flag1 | Flag2 ——————————————– 1 | 1900-01-01 | 1900-01-01 | 0 | 1 1 | 1900-01-01 | 2000-01-01 | 1 | 0 2 | 2010-01-01 | 2020-01-01 | 0 | 0 For any date range of an ID, flags 1 and […]

I want a query for multiple windowed results (SQL Server 2012+)

Date (A) B C D E F G H I 27-Feb-15 -100000.00 2,000 2,000 50 -2000 2000 -100,000.00 02-Mar-15 -80000.00 1,000 3,000 40 -2000 4000 -80,000.00 29-May-15 30000.00 -600 2,400 30 800 3200 24,000.00 09-Jul-15 500000.00 -1,200 1,200 20 1600 1600 32,000.00 26-Oct-15 -2000 800 2,000 40 -50 1650 -2,000.00 20-Nov-15 1500000 -2,000 0 30 […]

Selecting top row in a many to many relationship with custom join table

I have three tables in a M-M relationship: I’m having a lot of trouble trying to figure out how I can retrieve all Bundles with only the first related inventory item ordered by Inventory.Stock ASC but also return the Bundles_Inventory.InventoryRequired field. I’ve seen other responses to similar questions but none of them take into account […]

Execution details for UNION vs FULL OUTER JOIN

I’m dealing with two SQL tables that contains 120,000,000 records each. Few records (approx 60,000) are duplicated across the two tables. Structure of both tables are the same. There are 40 columns in each table. I need to union the records into one of the table. I know of two ways to do it (both […]

Get the groups items after a GROUP BY in SQL Server

I have a table that has these columns: UserID1, UserID2, ProductID, PurchaseDate The following query run in a purchases table and return couple of users that have more than a number of interactions between them, regardless the order in the last 31 days: DECLARE @threshold AS INT DECLARE @days AS INT SET @threshold = 10 […]

SQL, from , seperated rows to colums

Create table test123 ( SeqId int, string1 varchar(100), string2 varchar(100) ) insert into test123(SeqId,string1,string2)values (10,’a,b,c’,’x,y,z’), (20,’d,e,f’,’p,q,r’) output SeqId RowNum String1 String2 10 1 a x 10 2 b y 10 3 c z 20 1 d p 20 2 e q 20 3 f r

SSRS Matrix cells formatting

I have a data that looks like similar to below. Data: Type Color Pen Black Pen Blue Pen Red Pencil Black Pencil Red I am trying to use it to show the desired result in ssrs which is shown below. Desired Result: Pen Pencil Black Black Blue Red Red Current Issue: Pen Pencil Black Black […]

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