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
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
I have a table as follows : A| B| C| D| 1 3 1 25/2/2016 2 4 2 25/4/2016 2 5 1 22/2/2016 3 5 1 23/2/2016 3 5 1 23/6/2016 Now I want the result set with following condition : All rows with value of B as 3 or 4. Include rows with value of B as 5 only wh
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,
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
Here is an example of my current table: ProjectID Level Cost --------- --------- --------- 101 LVL_1 1000.00 101 LVL_2 2500.00 102 LVL_1 1500.00 102 LVL_2 3000.00 103 LVL_2 1200.00 When I run the following query: SELECT ProjectID, CASE WHEN Level = '
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
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
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
I have to get/create date from the user input of week of month (week number in that month - 1st,2nd,3rd,4th and last) and day of week (sunday,monday..) in SQL server. Examples: 4th Sunday of every month, Last Friday of every month, First Monday etc.
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
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
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
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
I have a slight twist on the other "in one table but not another" threads I've seen. I have 3 tables: ProductFeed ProductFeedID INT ProductName NVARCHAR(30) Product ProductID INT ProductFeedID INT StorefrontID INT Storefront StorefrontID INT Sto
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
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
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
Ok I have searched SO and Google but haven't really found a definitive answer so throwing it out there for the SO community. Basically I have a table of longitudes and latitudes for specific points of interest, the sql query will know which location
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 .
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
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
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
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
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
I have a DateTime column. I want to extract all records, lets say, from 8:30 to 16:15 within a certain date range. My problem is that I need to compare hour and minute as a single time value. I can test the DATEPART for Greater or Less than some hour
How do I check if a column is empty or null using a SQL select statement? For instance, if I want to check: select * from UserProfile WHERE PropertydefinitionID in (40, 53) and PropertyValue is null or empty Does this do what you want? SELECT * FROM
I need to create trigger in SQL Server 2008 that gone insert all values from one row in which some value was changed into Log table! For example if i have table Employees that have column id, name , password, and i update this table and insert new va
Suppose that I have a source table like this: Source SourceKey (PK) Data1 Data2 Amount And I am loading data from 'Source' into two related tables that look like this: Summary SummaryKey (PK) Data1 Data2 Amount Detail DetailKey (PK) SummaryKey (FK) S
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
