MS SQL Server :: Aggregating Consecutive Fields

Aggregating Member Properties Or Not Aggregating Distinct Data

Feb 23, 2005

I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?

If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).

If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.

View 4 Replies View Related

Aggregating Conditionally

Mar 7, 2008

I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!

Sample dataset (select * from cs_view):

Gender | Program | Student | Class_Section | Heads | Credits
------ | ------- | ------- | ------------- | ----- | -------
Female | English | Elena | Phys 101-b | 1 | 4
Female | English | Elena | Hist 101-c | 1 | 4
Female | English | Elena | Engl 101-a | 1 | 4
Female | English | Elena | Engl 105-b | 1 | 4
Male | History | Rich | Phys 105-a | 1 | 4
Male | History | Rich | Engl 101-c | 1 | 4
Male | History | Rich | Hist 101-b | 1 | 4
Male | History | Jacob | Phys 101-a | 1 | 4
Male | History | Jacob | Hist 101-b | 1 | 4
Male | History | Jacob | Engl 101-c | 1 | 4
Male | History | Jacob | Phys L-101-a | 1 | 0

Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.

Desired table:
Gender Program Heads Credits
------ ------- ----- -------
Female English 1 16
Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.

Thanks for taking the time...
tom

View 1 Replies View Related

Using Different Scopes For Aggregating Values

Mar 5, 2007

Hello everybody,

I'm facing a big problem in my actual report development task... however, what i need to do is (in my opinion) so basic that there has to be a way to do this...

What does it all about:

My customer needs a report in which different measures are shown in rows in a matrix table. columns are reserved for the months, simulating a sort of calendar aspect.

However, the matrix has one additional group in it's rows and the whole matrix is contained within a list.

A possible situation could be following:

Coca Cola

Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
TOT %

PM BRU 619 53% 122 23 34 24 0 67 72 78 56 90 78 65
NET 572 58% 87 12 30 13 0 54 65 50 43 85 73 60
IN BRU 554 47% 45 54 8 23 32 45 6 76 34 87 87 65
NET 421 42% 30 43 5 20 12 32 3 60 32 80 65 39

TOT Jan Feb Mar ...

BRU 1173
NET 993

Pepsi Cola

...

The problem are those percentages in red. They are calculated on total amount of BRU/NET without distinction of the first group in the table (PM or IN). If I set the value for the textbox to the following expression:

=(sum(Fields!value.Value)/sum(Fields!value.Value,"Level2"))*100 Where Level2 is the Details specified in the list (in our case Coca Cola or Pepsi Cola) Reporting services makes the sum of the value field without making the distinction of which type of value it is dealing with...

My question is now rather obvious: Is this calculation possible and if yes how?? I've thought to write a custom method in Reporting Services (no assembly) but I've no Idea how i can 1) access the dataset. 2) I need to be able to specifiy the name of the measure for which the calculation needs to be done and 3rd I also need to pass the current detail of the list item to be able to apply the necessary filters on the dataset to perform the aggregation....

Any idea's how to perform this??

Thanks in advance

Alain

View 1 Replies View Related

RSsk5 Aggregating Groups In Matrix

Aug 3, 2007

Hi all.

I have what I thought would be a simple problem, maybe my approach is all wrong!

Say you have customers, registered in a year/month group. They can be active in another year/month group.

So we get a simple table with registration year/month down the side and activity along the top.

This shows "of all the people p who register in month m, how many are active in month a?"

So assuming that all people registered in month m are also active in month m, the max(activity) for that slice (ie the max value for any active month for the registered month) should be the value if we collapse the activemonth group to activeyear.

Let me try to draw it :D

Registered/Active
2006 2007 ...
Jan Feb Mar ... Jan Feb Mar ... ...
2 Jan 20 10 10 20 . 5 3 1 5 20
0 Feb 0 25 15 25 . 8 5 5 8 25
0 Mar 0 0 40 40 . 11 1 6 11 40
6 ... 20 35 65 65 . 24 9 12 24 85
2 Jan 0 0 0 0 . 50 34 44 34 50
0 Feb 0 0 0 0 . 0 45 40 41 45
7 ... 20 35 65 65 . 50 79 44 75 95
..... 20 35 65 65 . 74 88 56 99 180

I think. The ...'s represent the grouping band total.

View 1 Replies View Related

How To Stop Cube From Aggregating Product Prize

Oct 30, 2015

I've built a cube which has all the measures and dimensions I want, unfortunately the numbers are off: the product prize is aggregated then rather simply being fetched from the underlying oledb destination file in the data warehouse. So if product x costs 10 USD and has been purchased 3 times in a certain month by a customer, the cube shows a product prize of 30 USD. I've switched the measure's usage from SUM to no aggregations but I still don't get the simple value list without aggregation. Why is this happening?

View 4 Replies View Related

Aggregating Values That Match Aggregate Result

Sep 23, 2015

The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.

-- greatly simplified schema that makes as much sense as the real schema
CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10));
CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue));
CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));

[Code] ....

Desired results:

SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment
UNION ALL
SELECT 2, 'second row', '2015-10-11', 150
UNION ALL
SELECT 3, 'third row', NULL, NULL

I know I'm doing something wrong in the last query and I believe another sub-query is needed?

Let me answer a few questions in advance:

Q: This schema looks horrible!
A: You don't know the half of it. I cleaned it up considerably for this question.

Q: Why is this schema designed like this?
A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.

Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical.
A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).

View 2 Replies View Related

Aggregating Financial Data To 1 Minute Intervals

Aug 19, 2006

I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.

I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.

SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice,
DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp
FROM MasterData
GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)

Any ideas?

thanks in advance.

View 1 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

Consecutive Users

Nov 22, 1999

Does anyone know how many consecutive users can be logged into a MSSQL database? I have a database online and need to know how many users can be logged on at a time. someone told me 150 users and others say 200 and stillothers say it's unlimited based on licensing. I tried Microsoft's homepage but got nothing so please don't suggest it. (I spent two hours there)

Thank You in advance
Joey*

View 2 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

Consecutive Numbering WITHOUT Identity

Nov 23, 2006

Hi,I'm
trying to insert records from one table into another table. The
destination table has a ROWID field which cannot be an identity key,
but needs to 'act like' an identity key and have its value populated
with (Max(ROWID) + 1) for each row added to the table.To my
thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not
work as it will only be evaluated once so if I am adding 1000 records
and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of
1235.Is there a way to accomplish this?Thanks

View 2 Replies View Related

Find Consecutive Occurrences

Jul 25, 2006

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
Dinesh

View 2 Replies View Related

Measuring Consecutive Years

Oct 24, 2006

Hi there.

I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.

I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.

CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))

INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00

For the above dataset, I am trying to create the following output

Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100

Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?

Thanks in advance.

View 9 Replies View Related

Select With Consecutive Dates

Jul 30, 2012

I have a table with

EmpNum, Date, Abstype

What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.

eg Table

EmpNum, Date, Abstype

001 07/23/2012 VAC *Monday
001 07/24/2012 VAC
001 07/25/2012 VAC
001 07/26/2012 VAC
001 07/27/2012 VAC
003 07/23/2012 VAC * Monday

[code]...

As these are the 2 that run from monday-friday

View 2 Replies View Related

Getting Consecutive Numbers From Address

Oct 22, 2013

I have a table with the following :

Cust id Address
1234 25 main street apt 78961
5678 25 ohio dr apt 567891
7890 25 lee lane apt 6789

I want to pull only the 6 consecutive numbers from my address , so only cust id 5678 should be displayed.

View 11 Replies View Related

Count Consecutive Numbers

Jul 23, 2005

I'm trying extract a count of consecutive numbers, or "unbroken" years inthis case, at any particular given time.For example (simplified):CREATE TABLE #Customers(CustNo INT,YearNo INT,IsCust CHAR(1))INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')SELECT * FROM #CustomersCustNo YearNo IsCust----------- ----------- ------999 2006 Y999 2005 Y999 2004 Y999 2003 N999 2002 N999 2001 Y999 2000 YIn 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for2, etc. Ideally I'd feed it a single year to lookupI'm resisting the urge to create cursor here -- anyone have any hints?....Chris.

View 10 Replies View Related

Query Help Please - Consecutive Dates

Jul 23, 2005

Hello,Can someone please help me with a query?The table looks like this:BookedRooms===========CustomerID RoomID BookDateID1 1 200507011 1 200507021 1 200507031 1 200507091 1 200507101 1 200507111 1 20050712Desired result:CUSTOMER STAYS==============CustomerID RoomID ArriveDateID DepartDateID1 1 20050701 200507031 1 20050709 20050712Basically, this is for a hotel reservation system. Charges varynightly, and customer changes (shortening/extending stay, changingrooms, etc) happen quite often. Therefore, the entire stay is bookedas a series of nights.The length of the stay is never known, so it needs to be derived viathe Arrive and Depart Dates, based on the entries in the table.Notice, customers often stay in the same room, but with gaps between,so a simple MIN and MAX doesn't work. The output needs to showconsecutive nights grouped together, only.I've researched this quite a bit, but I just can't seem to make itwork.Any help would greatly be appreciated.Thanks!

View 15 Replies View Related

Power Pivot :: Aggregating Time Periods In Cube-member / Cube-value Formulas?

Aug 23, 2015

I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.

Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))

I haven't tested this but have assumed it works but a bit long and clumsy.

View 5 Replies View Related

SQL Help - How To Figure Out Consecutive Workdays With Holidays

Jul 18, 2006

Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance, Jessica

View 7 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

SQL 2012 :: Group By On Consecutive Records?

Apr 4, 2015

Problem 1: I have the following table which shows the location of a person at 1 hour intervals

IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006

The delta increments by +1 every time the location changes.

I would like to return an aggregate grouped by delta as per example below.

EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200

I am using the following query (which works fine):

select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid

[code]....

However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.

Problem 2:I have the following table which shows the location of different people at 1 hour intervals

IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003

I would like to return an aggregate grouped by person and location.

For example

EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200

What modifications do I need to the above query (Problem 1)?

View 0 Replies View Related

Get Number Of Consecutive Days In Table

Apr 17, 2008

Hello,

I have a table with 3 columns: Item# | Date | ItemAmount.
Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).

What I want to do is to :
retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).

Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.

Does anyone have an idea.
thanks in advance,

View 7 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

Deleting 'consecutive' Duplicate Records Alone

Aug 9, 2006

Rajarajan writes "Kindly don't ignore this as regular case.
This is peculiar.
I need to delete one of duplicate records only if they occurs consecutively.
eg.

1. 232
2. 232
3. 345
4. 567
5. 232

Here only the first record has to be delete. Kindly help me out.

Thank you.

Regards,
R.Rajarajan"

View 1 Replies View Related

Grouping Data In Consecutive Values

Oct 12, 2007

Hi Fellows
I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.

geomseq polygon xc yc x1 y2

0 17 21 22.5 0 0
3 17 21 22.5 40 40
2 17 21 22.5 0 20
4 17 21 22.5 20 0
1 17 21 22.5 0 10
5 17 21 22.5 10 10
1 18 40 40.5 0 20
4 18 40 40.5 20 30
0 18 40 40.5 0 0
3 18 40 40.5 10 20
2 18 40 40.5 5 15
5 18 40 40.5 30 35
6 18 40 40.5 40 40
9 18 40 40.5 80 80
7 18 40 40.5 45 45
8 18 40 40.5 50 60

I want something like this
geomseq polygon xc yc x1 y2

0 17 21 22.5 0 0
1 17 21 22.5 0 10
2 17 21 22.5 0 20
3 17 21 22.5 40 40
4 17 21 22.5 20 0
5 17 21 22.5 10 10
0 18 40 40.5 0 0
1 18 40 40.5 0 20
2 18 40 40.5 5 15
3 18 40 40.5 10 20
4 18 40 40.5 20 30
5 18 40 40.5 30 35
6 18 40 40.5 40 40
7 18 40 40.5 45 45
8 18 40 40.5 50 60
9 18 40 40.5 80 80

regards and thanks in advance
Edwin

View 3 Replies View Related

T-SQL (SS2K8) :: Consecutive Streak Excluding Weekends

Aug 8, 2014

I'm trying to write an algorithm that returns the most recent and longest consecutive streak of positive or negative price changes in a given stock. The streak can extend over null weekends, but not over null weekdays (presumably trading days).

For example, lets say Google had end of day positive returns on (any given) Tuesday, Monday, and previous Friday, but then Thursday, it had negative returns. That would be a 3 day streak between Friday and Tuesday. Also, if a date has a null value on a date that is NOT a weekend, the streak ends.

In the following code sample, you can get a simplified idea of what the raw data will look like and what the output should look like.

set nocount on
set datefirst 7
go
if object_id('tempdb.dbo.#raw') is not null drop table #raw
create table #raw

[Code] .....

I should also mention that this has to be done over about half a million symbols so something RBAR is especially unappealing.

View 5 Replies View Related

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 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

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

Speed Up 6 Consecutive Left Outer Joins

Apr 28, 2008

Hi,

The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.

declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on

SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'

The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.

View 19 Replies View Related

Checking Records From Database For Consecutive Year

Apr 23, 2014

I am fairly new to SQl and PHP and have a question. I have a database that we enter in when a person buys a season ticket for our baseball team. I want to check and see who bought in 2013 but has not yet bought in 2014. How do I do that?

THID is the ticket holder ID and there is a year. I can do the select * from Passes where Year = '2014' and get everyone who has bought in 2014. How do I compare that with the 2013 and show people who have not bought yet in 2014?

Example entry:

THID PassType Year
15 2 2013
21 1 2013
10 1 2013
21 1 2014

With this - Ticket holder 21 has bought in both years so I wouldn't want his name to come up. I would want to see that ticket holders 15 and 10 haven't yet bought in 2014.

View 1 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