Tag: sql

Sql Select top 3 per rollup group

How do I return only the top 3 values per Name-datee pair in the following? DECLARE @t TABLE(NAME NVARCHAR(MAX),datee date,val money) insert INTO @t SELECT ‘a’,’2012-01-02′,100 insert INTO @t SELECT ‘a’,’2012-01-02′,100 insert INTO @t SELECT ‘a’,’2012-01-03′,100 insert INTO @t SELECT ‘a’,’2012-01-05′,150 insert INTO @t SELECT ‘a’,’2012-01-06′,200 insert INTO @t SELECT ‘b’,’2012-01-07′,200 insert INTO @t SELECT […]

Is this a good algorithm for listing prime numbers?

DECLARE @c int = 1000; DECLARE @numbers TABLE (n int NOT NULL PRIMARY KEY); DECLARE @products TABLE (p int NOT NULL PRIMARY KEY); DECLARE @primes TABLE (p int NOT NULL PRIMARY KEY); — The ‘composite exclusion’ approach — 1. list all n = 2, 3, 4, … c WITH numbers AS ( SELECT 2 AS […]

Display SQL Column Name as Row value in SSRS matrix

I have the following table as a result of sql query: I would like to display the data at the SQL Server Report as the following : Thank You.

How can I turn/tranpose these columns into rows in SQL?

This MUST be a simple one, I’ve very reluctantly asked this question – but I can’t work it out. I have a query which (in a roundabout way) returns 2 counts: Col1 | COL2 123 456 But I need to return: 123 | COL1 456 | COL2 What I’ve Tried I’ve looked at UNPIVOT but […]

Pivot Data Using a SQL Select Query

I need to pivot some data when doing a select query. I’m using SQL Server 2014. Here is the format of the original data. StudentID | DocumentType | PersonID ———- ————- ——– 00001 DocA 2222 00001 DocB 2222 00002 DocB 2222 00002 DocA 3333 00003 DocA 4444 And I want it to display like… StudentID […]

SQL Query to get the monthly change percentage from daily quotes

I have the below table and I would like to get the Monthly change % from it I am using the below query for getting the monthly change: SELECT ct.Quote_Date, ht.Quote_Date AS htDate, ct.Quote_Price, ht.Quote_Price AS [htPrice], ((ct.Quote_Price – ht.Quote_Price) / ht.Quote_Price) * 100 AS ChangePerc FROM #TempStock ct LEFT JOIN #TempStock ht ON CONVERT(DATE, […]

Is there any pure sql way to delete data from log table in following ways

I have a two tables 1. main_table 2. log_table On main_table a trigger is defined that copies the affected rows to log_table on delete and update. In log_table the columns are same except two extra column pid(primary key int) and updateat(time stamp). Every month the cron job is deleting all the records from log_table that […]

Concatenate two columns and join by ID

and thanks in advance! I’m looking for the most efficient way to concatenate each “Secretary” where JobTitle = “Assistant” and then join to another table by “Empl_code”. This will be done in a view. declare @Atty_Sec table ( Empl_Code int, Attorney varchar(20), Secretary varchar(50), SecJobTitle varchar(50) ) insert into @Atty_Sec select 1,’John Smith’,’Mary Anne’, ‘Assistant’ […]

SQL Query to separate address into multiple columns

In SQL Server, I have address data stored in a single column like this: 123 Fake St #25 I would like to separate this data 3 columns like this: Number Street Apt ———————– 123 Fake St #25 I figured I can use a combination of trimming and search functions, but I am not sure. I […]

SQL Server Trigger: INSERTED and DELETED does not exist. What is it?

I need to debug (aka reverse engineer) a SQL Server trigger. The trigger is an AFTER INSERT, UPDATE trigger. At the beginning of the trigger, there is the following IF clause: if not exists(select 1 from inserted) and not exists(select 1 from deleted) begin print ‘something’ return end The code runs into this IF and […]

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