Tag: sql

SQL Pivot Table Grouping

I have a table as follows: Date Ticket Question Response 2016-10-01 1 Score? 10 2016-10-01 1 Reason? Awesome 2016-10-02 2 Score? 9 2016-10-02 2 Reason? Good 2016-10-03 3 Score? 8 2016-10-03 3 Reason? Okay I want to Pivot it in SQL as: Date Ticket Score? Reason? 2016-10-01 1 10 Awesome 2016-10-02 2 9 Good 2016-10-03 […]

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′, […]

Updating Table with Joining

In my scenario I have two temp tables: Table1: id | UserName | CalendarDate —-+———-+————– 1 | Null | 2016-09-07 2 | Null | 2016-09-07 Table2: id | Emp_Name | Date —+———–+————- 1 | Max | 2016-09-07 2 | Jupiter | 2016-09-07 Here is my query which I am using to join two tables. update […]

Combine Query with % in SQL

I have been trying to get the percentage of category(concerns) from each test however I am having trouble combining the two queries. Where I am having trouble is the divide by and getting the correct query. I used How to calculate percentage with a SQL statement for reference. I am looking to get an exam […]

Can't multiple insert using openrowset

I already searched a couple of times. Some post are related but still can’t help me on this problem. Here are the sample items of my items.txt. I checked the .txt file and there is absolutely no white space, etc. 0000100000 7005432111 4545213695 4545213612 0000100001 0000100002 So here’s my code so far: INSERT INTO items(id, […]

Is this the right way to partition data at the application level?

Currently I’m working on a legacy system that does partitioning at the application level. A line item table with a million rows (for example) have be split into two tables so that each table gets 500,000 thousand rows. I don’t have experience with data partitioning so I’m not sure if this is the right way […]

Clustered index, order on single single, without order by clause

I have a transaction table (single primary key) with millions of records and everyday 100s of records are getting added. This table is then further used in Reconciliation and Settlement which happens on newly inserted records only, without any order. Although, I have created a clustered index on the ID column but not sure whether […]

Executing a function. Unable to print the output

I recently came across the following FUNCTION in stackoverflow: CREATE FUNCTION dbo.UrlDecode(@url varchar(3072)) RETURNS varchar(3072) AS BEGIN DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072) SET @count = Len(@url) SET @i = 1 SET @urlReturn = ” WHILE (@i <= @count) BEGIN SET @c = substring(@url, @i, 1) IF @c LIKE ‘[!%]’ […]

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 […]

Interpolate every value up to 5 years – SQL Server 2008

I have below table with Swedish swaprates. I am making a forward-looking PnL-report and therefore want to know what my basis rate would be on maturity rate for a bond so I can evaluate whether we should reinvest immediately on maturity date or not. Lets say bond X matures at 2017-03-01, I would then like […]

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