SQL Server 2014 :: DateDiff Between Two Consecutive Rows

Oct 15, 2014

How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?

I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).

WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)
SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc

View 5 Replies


ADVERTISEMENT

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 1 Replies View Related

Consecutive Rows

Jan 8, 2008

Hello,
I have a stored procedure that inserts 5 rows into a table. The execution of the SP is inside a transaction like in the code below:


Code Block
SqlConnection conn = new SqlConnection(ConnectioString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// execute stored procedure... insert 5 rows
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
conn.Close();
}






Suppose that N users are executing the code, one independent of the other, in the same time and they both commit the transaction at the same time.
Can I suppose that the rows inserted in the table by one user will be consecutive?


Thanks!

View 5 Replies View Related

Grouping Consecutive Rows In A Table

Feb 21, 2013

SQL Server 2008.

From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.

Part of the table:

logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413

[Code] ....

So as long the field LocationID is the same in the next row, it needs to be grouped.

I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:

,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp

By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.

View 12 Replies View Related

Compare Values In Consecutive Rows

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 2 Replies View Related

T-SQL (SS2K8) :: Subtraction Of Values From Consecutive Rows

May 31, 2015

I've;

Id.........|......type....|.....Value
2001................1...............20
2001................2...............32
2002................1...............19
2002................2...............21
2003................1............... 3
2003................2...............30

I want;

Id........|.......Value
2001.................12
2002..................2
2003.................27

View 7 Replies View Related

SQL 2012 :: Calculate Time Difference For Consecutive Rows?

Jul 2, 2015

I have a table like this.

CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),

[code]....

Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:

- S_ACTV_CODE_PREV means the previous active records.

- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts

- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE

- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL

- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL

- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL

So the result should be as below:

S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33

[code]....

View 9 Replies View Related

Comparing Data In Two Consecutive Rows From A Single Table

Jul 20, 2005

I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.

View 22 Replies View Related

SQL Server 2014 :: Counting Corresponding Rows In Table

Mar 21, 2015

Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.

SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

View 3 Replies View Related

SQL Server 2014 :: Replace Rows With Other In Same Table

Jul 5, 2015

I have the below table :

IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0

Then i need to replace second row with value with first row with null and so on :

IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL

View 3 Replies View Related

SQL Server 2014 :: Comparing Two Rows Values?

Oct 30, 2015

I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)

Input Table Data:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2008
10104210001/1/200912/31/2009
10104220001/1/20081/15/2008
10104220001/16/200812/31/2008
10104220001/1/200912/31/2009
10104230001/1/200812/31/2008
10104230001/1/200912/31/2009
10104230001/5/201012/31/9999
101042DDPP5/16/200712/31/2007
101042DDPP2/16/20075/15/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

Output should be like this:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2009
10104220001/1/200812/31/2009
10104230001/1/200812/31/2009
10104230001/5/201012/31/9999
101042DDPP2/16/200712/31/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

View 3 Replies View Related

SQL Server 2014 :: How To Append Rows Horizontally

Nov 4, 2015

I'm trying to append rows horizontally - I'm using the "xml path" approach

SELECT
E.[USER_NAME] As 'User Name',
(
SELECT ',' + C.[PERMISSION_NAME]
FOR XML PATH('')
) As [Associated Groups]
FROM TABLEA As A
JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF
JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF
JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID
JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF
WHERE A.[OBJECT_NAME] = 'MyObjectName'

It's not working. I'm getting:

User nameAssociated Groups
A. SmithG1
A. SmithG2
A. SmithG3
etc...

What I'm looking for is:

User NameAssociated Groups
A. SmithG1, G2, G3
etc...

View 3 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: BCP Reports No Errors But Inserting No Rows

Feb 15, 2015

I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.

My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.

Here the Cursor:

create table #filelist(filename nvarchar(100))
insert into #filelist exec xp_cmdshell 'dir C:BCP_Test /A-D /B'
declare @filename nvarchar(100)
declare @path nvarchar(100)
declare @bcp_open nvarchar(100)

[Code] ....

The code seems to work but although there are around 50000 lines in the .csv file, None of the lines are inserted.

View 6 Replies View Related

SQL Server 2014 :: Check If Two Rows Have A Different Value In A Specific Column

Sep 9, 2015

I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.

Like this:

ID IS NULL
1 1
2 1
3 0
1 0

The expected ID i get as a result of my query should be 1 in this case.

View 6 Replies View Related

SQL Server 2014 :: Concatenating Rows In A Select Horizontally

Oct 8, 2015

I'm using the SQL indicated below:

SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A

[code]...

However I'm having difficulties concatenating the user field in the horizontal form indicated.

View 3 Replies View Related

SQL Server Admin 2014 :: Certain Rows Not Replicated Yet Replication Was Not Broken

Oct 24, 2013

We had an issue recently where a (transactional) replicated table was replicating data as expected.

Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.

We have pretty much confirmed that users did not delete those rows.

Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.

We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.

View 1 Replies View Related

SQL Server 2014 :: Insert 500 Million Rows Into In-memory Table

Jul 29, 2014

I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.

I need a sample script to insert 500 million records into a table ....

View 9 Replies View Related

SQL Server 2014 :: Find Duplicate Rows Like Same Entries More Than One Time?

Sep 11, 2014

i have a table like below

create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)

values like

1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22

I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?

View 3 Replies View Related

SQL Server 2014 :: Display Column Headers And Rows From Different Tables?

Dec 18, 2014

I am looking for SQL query which uses 2 tables CASH and BALANCE.

eg: Need Tablename, ColumsList and data in the results set.

eg: 10 rows shown below and ordered based on Acct_number

Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE

Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY

Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row6,BALANCE,10,500,750,DR,12/12/2014,USD

Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row10,BALANCE,20,500,750,DR,12/12/2014,USD

View 7 Replies View Related

SQL Server 2014 :: Find Equal And Opposite Rows In A Table

May 25, 2015

How to find the equal and opposite rows in a table.

E.g.:

book position
A 500
B -500
C -500

The output should return the rows having equal and opposite positions.

O/P:
A 500
B -500

I have tried self join on a table but in vain.Do we need to cursors for these row level handling?

View 6 Replies View Related

SQL Server 2014 :: Huge Number Of Rows In Table Spool

Jul 6, 2015

I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.

Note that the "actual number of rows" is some 351M...how is this possible?

the query:

declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID

it's like there's some massively recursive process going on?

View 5 Replies View Related

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

View 4 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

SQL Server 2014 :: Separate Words To Rows (Convert Comma Separated)

May 3, 2015

I have a table Sample with data stored like below

ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL

I need the output like..

-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------

How can I do the same with a select query in SQL Server?

View 4 Replies View Related

SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables

Jun 8, 2015

We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.

SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,

[Code] ....

View 9 Replies View Related

SQL Server 2014 :: Select Statement Returning Too Many Rows For Backup Start Date

Jun 3, 2015

Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.

select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'

[Code] .....

View 8 Replies View Related

SQL Server 2012 :: How To Get Consecutive Count Based On First Value

Jan 13, 2015

We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.

declare @YourTable table (
CustomerID int,
ReadDate datetime,
usage int
)

insert into @YourTable select 1,' 1 mar 2014',0
insert into @YourTable select 1,' 1 feb 2014',0

[Code] ....

This should return

1,3
2,1

This is what I am currently using but it isn't working right

WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt,CustomerID) pt FROM
(
SELECT tt.*

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Create Row Number For A Consecutive Action

Nov 26, 2013

How to create a row number for a consecutive action. Example: I have a listing of people who have either completed a goal or not. I need to count by person the number of consecutively missed goals.

My sql table is this:
PersonId, GoalDate, GoalStatus (holds completed or missed)

My first thought was to use the rownumber function however that doesn’t work because someone could complete a goal, miss a goal, then complete one and when they complete a goal after a missed goal the count has to start over.

View 9 Replies View Related

SQL Server 2012 :: Assigning A (Group ID) Based On Consecutive Values?

Jul 31, 2014

I have a data set that looks something like like this:

Row# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A

I need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:

Row# Data GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5

You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.

View 2 Replies View Related

SQL Server 2012 :: Find Most Recent Record Of Consecutive Dates

Feb 23, 2015

I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123

From this table, I would like a query that would give me this result:

01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123

There will likely be more than just 2 consecutive records per contract number.

View 4 Replies View Related

SQL Server 2008 :: Assign Consecutive Numbers To A Block Of Data

Mar 17, 2015

I want to assign consecutive numbers to a block of data where block of data is based on days consecutive to each other i.e., one day apart.

Date format is: YYYY-MM-DD

Data:

TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
1 2011-07-31 00:00:00.000

[Code] ....

My Attempt:

WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER
(
PARTITION BYTestId

[Code] .....

Expected Output:

TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
1 2011-07-30 00:00:00.000 3

[Code] ....

The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved