select one or more rows for each group

I have a set of supply operation organised like this: op_type | time_stamp | product | in | out ---------------------------------------------------- 01 | 08:00:00 | p1 | 50 | 0 02 | 08:01:00 | p1 | 0 | 10 02 | 08:02:00 | p1 | 0 | 35 03 | 08:03:00 | p

T-SQL XML update with XML fragment of another linked table

I am looking for a way to update (.modify('insert into..') to be specific) an XML column in a table with xml fragments from another table linked by a foreign key. For example, my table structure looks like below (simplified): Fields 1 to 5 in each ta

Applying the window function in a query without CTE

I have been searched in the net and trying to create a query that contains a window function without a CTE but I couldn't get the result and I need some help this my query that I create with a CTE what I need is doing that in one query ;with cte(Gid,

Variables explained to a beginner

I am having some trouble grasping the concept of variables in TSQL and SQL Server Here is an example from a stored procedure: ALTER PROCEDURE schema.procedure @InvoiceID varchar(28) = '' @InvoiceGuID varchar(28) = '' AS BEGIN DECLARE @CustomerGuID un

Use if according

I use this query, but it does not work: create FUNCTION getUserCreditPayment ( @resellerCode int, @userCode int, @startDate datetime, @endDate datetime ) RETURNS TABLE AS RETURN( WITH Directories AS ( CASE WHEN (@userCode != 0) THEN (SELECT * FROM Us

Need the last value of the previous year

I am trying to create a query of patients' last blood pressure values of 2015. When I use max(date) it includes values from 2016, but when I apply where date < '2016-01-01' it does not give me the values for those patients in 2015, but rather just de

How is the scale defined when decimal and bigint are divided?

I have value A of type DECIMAL(19,8) - the scale is 8, so the number of decimal digits that will be stored to the right of the decimal point is 8. Now, I am dividing A on B, where B is BIGINT. For, example: SELECT CAST(3 AS DECIMAL(19, 8)) / CAST(27

SQL Server: order of linked lines (routes)

I have a table with routes and I need to give them actual orders. The data looks as follows: route | rstart | rfinish | rfrom | rto | rorder -------------------------------------------------- route1| A | E | A | B | 0 route1| A | E | B | E | 0 route2

SELECT specific date in SQL query?

I have 3 tables that are joined together with this query. One of them brings me people names , another one brings me their points and the last one brings me date time. I select the total people score. Also, there is a column in the 3th tables that br

Priority of SQL parentheses

I'm using SQL Server 2008 R2 and trying to do a query like this: SELECT * FROM finance WHERE paid > 1000 and not (city <> 'RJ' and (flag2 is null or flag2 = '0')) But i'm not getting the desire solution. My idea is to search all payments bigger t

Simplify the dynamic table of SQL PivotTable

I have written a Dynamic Pivot Table Query based on the following. Here is a SQL FIDDLE for reference. CREATE TABLE TestTable1 ([idnumber] INT, [DataTypeId] INT) GO INSERT INTO TestTable1 VALUES (1, 108), (1, 108), (1, 108), (2, 108), (2, 108), (3, 1

TSql: Problem with join

I am trying to write a join query but can't seem to filter it out. I'm selecting an invoice table and and applied payments table. I want to get the balance of all invoice totals amount - applied amounts. I can get the applied amounts fine using sum b

How can I find disks in the mess - SQL?

Let's say I have a table with an ID Identity column, some data, and a datestamp. Like this: 1 data 5/1/2013 12:30 2 data 5/2/2013 15:32 3 data 5/2/2013 16:45 4 data 5/3/2013 9:32 5 data 5/5/2013 8:21 6 data 5/4/2013 9:36 7 data 5/6/2013 11:42 How do

how to convert the date to a format `mm / dd / yyyy '

I'm having a sql table with date column named CREATED_TS which holds the dates in different format eg. as shown below Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Nov 16 2011

Loop with internal and split buckle

I have records like this in a table called "Entry": TABLE: Entry ID Tags --- ------------------------------------------------------ 1 Coffee, Tea, Cake, BBQ 2 Soda, Lemonade ...etc. TABLE: Tags ID TagName ---- ----------- 1 Coffee 2 Tea 3 Soda .

Interview the last event each person attended

I have a table called CampRegistration with the following structure: [EID] INT, [CampName] VARCHAR(100), [StartDt] DATETIME EID is the identifier of the person who attended the camp. I wish to select the last camp each person in the CampRegistration

Update the manager table tree by recurrence

I have a table (see image) Employees (manager is manager of another and so on) with id, parentid, salary, totalsalary. The last one needs to be updated so that every employee had Sum of its Descendants salary. I have already written script, which get

Suppression with GROUP BY

How do I change this SELECT so it is the criteria used for deleting? SELECT COUNT(empid), empid, status, deptid from tableA GROUP BY empid, status, deptid HAVING status is null and deptid = 5 What do you want to delete? If simply records with NULL st

SQL Server returns the unexpected week number

I have some orders in a table and the last order date of 2011 is 20th Dec. I'm using a sql command to calculate the number of orders in a given week: SELECT CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) AS week, COUNT(1) as orde

Null substitution in SQLite:

In Sybase and MSSqlServer TransactSQL, we have a function IsNull(columnName,valueForNull) to return a typed default value when a column value is null. How can I replicate this functionality in SQLite? Example in TransactSQL: select IsNull(MyColumn,-1

SQL Batch Delete

I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of these rows. If I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to ma