Transact SQL :: Difference Between Data With Group By
Jul 28, 2015
I have a SQL table like this
Events time endTime
Tram 2014-11-28 12:35:50.390 2014-11-28 12:43:19.120
Re-Entry 2014-11-28 12:43:19.120 2014-11-28 12:56:07.040
Tram 2014-11-28 12:56:07.040 2014-11-28 13:15:25.060 // EndDate Before dump
Dump 2014-11-28 13:15:25.060 2014-11-28 13:50:07.233
Tram 2014-11-28 13:50:07.233 2014-11-28 13:55:17.473
Load 2014-11-28 13:55:17.473 2014-11-28 14:06:55.063
Tram 2014-11-28 14:06:55.063 2014-11-28 14:37:12.100
Dump 2014-11-28 14:37:12.100 2014-11-28 14:37:12.100
I want to calculate the Difference between 2 dates like endtime before Dump -time..I am expecting output like this
ROW1 ROW2
1 00:39:34
2 23:12:55
You can find the details in SQL Fiddle here.
View 4 Replies
ADVERTISEMENT
Aug 24, 2015
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where carid not
in(selectRentalOrders.CarRef
from
RentalOrders)
when I wrote this above-query for sofiacarrental_v2.2 it shows 30 in the result but when I changed it this query to that:
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where not exists
(select
RentalOrders.CarRef
from
RentalOrders)
I replaced not in with not exists it showed 0 in the result.there is any point in term of using them or I made a mistake in the second query?
View 2 Replies
View Related
Jun 15, 2015
Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.
Sample report:
View 4 Replies
View Related
Jun 18, 2015
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID Branch Group Date Value
1 A C 2015-06-01 10
2 A C 2015-06-02 15
3 A C 2015-06-03 25
4 A C 2015-06-04 20
5 B D 2015-06-01 20
6 B D 2015-06-02 25
7 B D 2015-06-03 10
8 B D 2015-06-04 20
I want the Output like below with a Running value difference in comparison to previous day.
ID Branch Group Date Value Running Value
1 A C 2015-06-01 10 10
2 A C 2015-06-02 15 05
3 A C 2015-06-03 25 10
4 A C 2015-06-04 20 -5
5 B D 2015-06-01 20 20
6 B D 2015-06-02 25 05
7 B D 2015-06-03 10 -15
8 B D 2015-06-04 20 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
View 6 Replies
View Related
Aug 10, 2015
What is the difference between the Index and the Primary Key?
View 14 Replies
View Related
May 28, 2015
What is the difference between Heaps and Balanced Trees? Is it true that if a table has no clustered index it is a heap?
If so , if the table has only Non clustered index, is it still a Heap?
View 4 Replies
View Related
Sep 25, 2015
I want a difference in days
Select datediff(dd,Target_Date,Achv_Date)
Now , checks are
1] when target date greater than achv_Date the difference should be greater than 0
means for FileID 77608
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%
View 6 Replies
View Related
Jun 19, 2015
What is the difference between Batch and Stored Procedure?
View 5 Replies
View Related
Jul 10, 2015
Currently my script is using the below mentioned query to find the time difference.
DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))
If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?
View 14 Replies
View Related
May 8, 2015
how to measure a change in inventory over various stores. My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. When the new stock arrives it is added to the "OnHand" count. I want to measure the delta change per day,per store. I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
TimeStamp Store
OnHand OnOrder
2015/04/22 18 1 - Concord
12
0
2015/04/23 11 1 - Concord
11
[code]....
View 17 Replies
View Related
May 23, 2015
I have the below query
DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')
[code]....
the result is like below
i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).
i am using SQLServer 2008 R2
View 8 Replies
View Related
Jun 21, 2015
I have a table data like below
id type timestamp
1001 start1 10:34:23:545
1001 start2 10:34:24:545
1001 end2 10:34:24:845
1001 end1 10:34:25:545
1002 start1 10:34:25:645
1002 start2 10:34:25:745
1002 end2 10:34:25:945
1002 end1 10:34:25:965
I need the result as follows
id millisecond diff start1end1 millisecond diff start2end2
1001 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
1002 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
SQL Server 2008 R2
View 5 Replies
View Related
Apr 16, 2015
I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
View 12 Replies
View Related
Jul 15, 2015
just see same kind of two sql statement
BEGIN TRAN
SELECT *
FROM authors AU
WITH (HOLDLOCK, ROWLOCK)
WHERE AU.au_id = '274-80-9391'
COMMIT TRAN
BEGIN TRANSELECT *
FROM authors AU
WITH (UPDLOCK, ROWLOCK)
WHERE AU.au_id = '274-80-9391'COMMIT TRAN
i like to know what is the difference between HOLDLOCK and UPDLOCK in sql server. explain with example to better understand.
View 4 Replies
View Related
Sep 22, 2015
Here is the table:
empid lastname firstname title titleofcourtesy ModifiedOROriginal
1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original
We need an output like this:
empid
1
1
lastname
Davis1
Davis
firstname
Sara
Sara
title
CEO
CEO
titleofcourtesy
Ms.
Ms.
ModifiedOROriginal
Modified
Original
View 2 Replies
View Related
Nov 16, 2015
Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.
Type Sn Timestamp Name Value
A 1 2015-08-15 12:34 Snick 5
A 1 2015-08-15 12:34 Mars 10
A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1
B 2 2015-08-15 15:31 Mars 9
B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11
A 1 2015-08-21 09:12 Mars 18
[code]...
So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.
View 8 Replies
View Related
May 21, 2015
declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'
i want output like this 2.18 means 2day 18hours difference how using query
View 6 Replies
View Related
Sep 23, 2015
In sql we have a built in function
DATEDIFF(second, startdate, enddate)
How can we achieve the same using Log parser query for date time in format (2015-09-22 10:25:30.5220)
Example:
startdate = 2015-09-22 10:25:30.5220.
Enddate = 2015-09-22 10:26:30.5220
After DateDiff I need to get 6 seconds.
View 5 Replies
View Related
Jun 22, 2015
I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.
This is the selcet statement:
SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')
View 17 Replies
View Related
Oct 14, 2015
Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.
View 10 Replies
View Related
Aug 5, 2015
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...
View 2 Replies
View Related
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
Dec 5, 2006
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
View 3 Replies
View Related
May 20, 2015
I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate
My source date
ID WorkId HireDate TerminateDate
1 123 2006-01-05 2013-01-13
1 456 2006-02-23 2013-04-13
1 789 2005-12-12 2013-06-13
2 913 2004-12-15 2012-01-13
2 406 2006-04-13 2014-04-13
3 113 2009-11-15 2011-01-13
3 106 2010-04-13
4 513 2002-11-15
4 168 2011-10-23
5 342 2007-01-05 2009-01-13
5 416 2008-02-23
5 159 2008-12-12 2010-06-13
6 542 2005-01-03 2007-01-13
Rule -1: set flag = yes
-Whenever terminatedate is not null
-Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No
- with in the ID group Whenever terminatedate is null
My output should look like this..
ID WorkId HireDate TerminateDate Flag
1 123 2006-01-05 2013-01-13 yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null)
1 456 2006-02-23 2013-04-13 yes
1 789 2005-12-12 2013-06-13 yes
2 913 2004-12-15 2012-01-13 yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
[Code] .....
View 3 Replies
View Related
May 14, 2015
I have 3 columns of data CustomerNum, Newsletter, and NumSent.
I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.
For the data below, my result set will be:
0000000000000000101 Healthcare
0000000000000000102 Construction-Environ Svcs
How can I do this easily with GROUP BY , Max or subselect?
Sample Data:
0000000000000000101 Healthcare 19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing 8
0000000000000000101 Homecare 5
0000000000000000101 Daycare 4
0000000000000000102 Healthcare 9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing 5
0000000000000000102 Homecare 11
0000000000000000102 Daycare 1
View 6 Replies
View Related
Sep 8, 2015
If I have the first two columns from a SQL query and would like to create the third (row):
personid new_commsstream row
1 0 1
1 0 1
1 0 1
1 1 2
1 0 2
2 0 1
3 0 1
4 0 1
5 0 1
5 1 2
5 1 3
Is this possible? I have tried using row over partition but I'm not sure how group it correctly, so basically every time there is a new 1 in new_commsstream within a personid the row number goes up by one.
View 3 Replies
View Related
Nov 3, 2015
I have a table with "Project", "Demo" and "Value". Here is a sample. Is there a way to show the "Value" in just one Demo after a I use group by?
Project Demo Value
Project1 Adult 100
Project1 Kids 100
Project1 Adult 100
Project2 Adult 200
Project2 Adult 200
Project2 Kids 200
Project2 Kids 200
That is after I ran the SQL: I will get only: (see Project1's Kids's value is null now.
Project Demo Value
Project1 Adult 100
Project1 Kids
Project2 Adult 200
Project2 Kids 200
View 12 Replies
View Related
Sep 3, 2015
I have 2 table (Employee T0 & Employee Absense T1), T0 & T1 use Employee Code to connect.
In T0, I have EmployeeCode, Location Code, Shift Code & Hour Rate....,
T1 has EmployeeCode, Date, Quantity ...,
How to write a query to get summary of
T1.[Quantity] * T0.[Hour Rate] as AbsHR group by T0.[Location], T0.[Shift] ?
so the result has [Location], [Shift], [Date], [AbsHR] 4 column.
View 2 Replies
View Related
Feb 13, 2001
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
View 1 Replies
View Related
Jul 20, 2005
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
View 4 Replies
View Related
May 6, 2015
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
View 5 Replies
View Related
Aug 5, 2015
How can I aggregate this result into 1 row? (I got it from a UNION ALL)
Article Assort1 Assort2
50095811 K1 NULL
50095811 NULL K3
I would like to have
Article Assort1 Assort2
50095811 K1 K3
View 3 Replies
View Related
Aug 13, 2015
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5
2 1 01/01/2011 Accept 5
3 2 02/01/2010 Accept 10
4 2 02/01/2010 Deny 20
5 3 02/01/2012 Accept 15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
5 3 02/01/2012 Accept 15 (show Accept row=5)
6 4 05/05/2012 Accept 25 (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7 4 07/08/2012 Accept 20
11 6 01/01/2011 Accept 5 (show Accept rows=11,12 because Qty is different)
12 6 01/01/2011 Accept 15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5),
(3,2,'02/01/2010','Accept',10),
(4,2,'02/01/2010','Deny',20),
(5,3,'02/01/2012','Accept',15),
(6,4,'05/05/2012','Accept',25),
(7,4,'07/08/2012','Accept',20),
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),
(12,6,'01/01/2011','Accept',15)
View 4 Replies
View Related
