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


ADVERTISEMENT

Data From Previous Day Excluding Weekends

Jan 23, 2014

One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.

select distinct

clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild

from

impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1

View 5 Replies View Related

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.



Any help would be greatly appreciated.



Thanks

JP

View 6 Replies View Related

DateAdd Function - Excluding Weekends

Jan 4, 2008

Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?

Thanks in advance

Alex

View 1 Replies View Related

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

I have a table with a list of jobs along with their start and end datetime values.

I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.

Example

Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000

Total Time should be: 2 Days, 1 Hour and 0 Minutes

View 5 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??

select  building_number as SchoolID,building_name as Campus,   count( distinct( CASE  WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed   from   Sales sl join Buildings b on  sl.Building_Num =b.Building_number join students2 s on  s.Student_Number= sl.Student_Num   join Sale_Items SI on   si.UID = sl.UID   where  CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and   '2015-05-07'      and VoidReview <> 'v' and  SI.INum = '1'    group by  building_number,building_name order by building_number,Building_Name;

View 8 Replies View Related

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

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

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

T-SQL (SS2K8) :: Count Of Consecutive Years Of Participation (islands And Gaps)

Jun 29, 2015

I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below.

ID DATE
------ --------
1 2000-05-03
1 2001-06-10
1 2002-04-02
1 2005-07-29
1 2010-12-15
4 2001-05-07
4 1999-08-01
4 2000-07-05
4 2001-08-01
9 2002-05-01
9 2000-04-02

My result set needs to be something like:

ID Count of Consecutive Years
------- -----------------------------
1 2
4 2
9 0

I know this is a gaps and islands type problem but nothing I have been able to find is working once I attempt modification so that it can fit my dataset. Please note that I am going to use the data return to populate another table that is currently being populated using a cursor that utilizes an insert statement based on different codes.

View 9 Replies View Related

Substract Weekends

Oct 22, 2007



I am getting two dates each of which I calculate using a subquery (joining 2 or 3 tables)
Then I perform datediff on these two dates to get a interger values (No of Days)

I need to substract the weekends from this No of days
How do I accomplish this

Thanks
sowree

View 10 Replies View Related

Removing Weekends??

Oct 24, 2006

Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.

I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?

View 6 Replies View Related

Transact SQL :: Get Date Of All Weekends Of The Year Without CTE

Sep 1, 2015

I want go get all weekends of the year (year dynamic) with out CTE concept, because I need to implement in 2005 version.I have googled but getting only CTE examples.

Query to get the weekends in a year.

View 3 Replies View Related

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. 
Any ideas on how to accomplish this task, and leverage the below, existing code?  Thanks in advance! 
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "

View 2 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

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

Excluding Records

Apr 24, 2008

Hello,

I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!

Here's the basic idea:

SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2

I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).

I've tried:

WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))

(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)

What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.

The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.

I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.

This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.

Any suggestions?

View 1 Replies View Related

Excluding Value From Insert

Jun 17, 2014

The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14)
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID

[code]....

View 5 Replies View Related

Excluding Duplicates

Nov 20, 2007

I have to exclude some records from the data flow.

If there is more than one record with equal col1 and col2, I need to exclude both records.

I can't do this operation in the OLE DB Source because I have to run some transformations before I run the col1-col2-equality check.

ANy ideas? Is aggregation transformation way to go? How do I tell to the aggr. transf to do a having count() > 1?

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

Aggregating Consecutive Fields

Apr 4, 2015

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

Excluding Empty Parameters

Apr 17, 2005

I would like to exclude any parameter that is empty from the SELECT
command?  How do I do this?  This is part of a stored
procedure.

SELECT PersonID FROM Persons WHERE
(FirstName = @firstname) AND
(LastName = @lastname) AND
(SSN = @ssn) AND
(AddressID = @addressid) AND
(DOB = @dob) AND
(Middle = @middle)

THanks

View 2 Replies View Related

# Of Weekdays Between Dates Excluding Sat/Sun

May 14, 2002

Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday.
Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.

Any thoughts or solutions out there?

View 4 Replies View Related

Excluding Duplicate Records

Apr 18, 2008

and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?

View 5 Replies View Related

Excluding Data For Certain Criteria Only

Feb 6, 2012

I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.

Here's an example of my data:

No code has to be inserted here.
No code has to be inserted here.

No code has to be inserted here.
No code has to be inserted here.

Right now my SQL is this:

Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF

INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd

WHERE PR04PTF.EmployeeID=441

View 3 Replies View Related

Order By Excluding First X Characters

Jul 6, 2012

I have a column where records are either 6 or 7 characters long.

for example:

111.111

OR

111.01

I am trying to order by the characters after the period. In other words I would like to exclude the first 4 characters (the 111.)

View 1 Replies View Related

Duplicate Row Excluding Primary Key

Jul 6, 2007

I am trying to duplicate a row in a table and this is the solution I came up with:

CREATE PROC duplicate_row(@my_primary_key)
AS

DECLARE @sql_stmt varchar(MAX)
SET @sql_stmt = 'INSERT INTO My_Table('

SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt + 'title) SELECT '

SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key))

EXEC(@sql_stmt)

However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.
-thanks

View 5 Replies View Related







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