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


ADVERTISEMENT

Transact SQL :: Finding Unique Counts Based On Consecutive Days?

Jul 9, 2015

I have a scenario here where the data looks like -

ID        Date
100      07/01
100     07/02
100    07/03
100   08/01
100   08/02
100   08/15

Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?

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

Number Of Days

Apr 16, 2008

How would I do a date range from the first day of this year to the getdate(), and so it will change once the new year hits as well?

View 6 Replies View Related

Finding Number Of Days...

Oct 30, 2006

i have id, start_date, end_date

View 2 Replies View Related

Number Of Business Days

Mar 22, 2001

Is there any easy way to calulate number of business days between the 2 dates ?
I want to exclude all sat and sun between the 2 dates.

View 1 Replies View Related

Calculate Number Of Days

Aug 28, 2004

I need to add days to a date field, my date field is as varchar(20041030 for example) and I need to add 4 days to it, my result should be 20041103, result field is also in varchar,how would I do that, can anyone pls help?

thx in advance!!

View 2 Replies View Related

Cal Number Of Days In Between Dates

Feb 27, 2005

select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'

Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria.
I can't seem to be able to get the datediff function right in this particular example

View 3 Replies View Related

Calculation Of Number Of Days

Jan 30, 2008

I have a Hospital Utilization Report and my Client wants me to calculate the number of days.

Example

Date of Services from 1/1/08 - Date of Service Thru 01-05-2008. Which the total will be 5 days.

How would I calculate that?

View 13 Replies View Related

Get The Number Of Days It Has Been Since A Record Was Inserted

May 22, 2007

Hiwhen inserting records into a table one of the fields is a date field.  I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!

View 7 Replies View Related

How To Get Average Number Of Days Between Orders

Nov 5, 2013

SQL 2012

I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.

SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders)
Group BY custId

View 5 Replies View Related

How To Calculate Average Number Of Days Taken

Jan 16, 2014

How to calculate the overall average number of days taken to complete something.

The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).

Each enquiry has a enquiry_number

Sample data typically looks like:

Enquiry number - enquiry_time - complete date
1 - 01/01/2014 - 12/01/2014
2 - 01/01/2014 - 11/01/2014
3 - 01/01/2014 - 10/01/2014
4 - 01/01/2014 - 07/01/2014
5 - 01/01/2014 - 12/01/2014
6 - 01/01/2014 - 04/01/2014

etc.

What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?

View 2 Replies View Related

Number Of Days Between Two Dates In Same Column

Apr 17, 2007

I have a table like this (a small section of the table)

Cu_id |Tr_id |Date
1234 |1 |12/3/2006
1234 |2 |12/18/2006
1234 |3 |1/5/2007
1234 |4 |1/9/2007
1234 |5 |2/21/2007
9999 |91 | 1/3/2006
9999 |81 |1/10/2006
9999 |71 |1/18/2007
9999 |61 |2/1/2007

I have to find the number of days between the dates for the same cu_id and add the number as a new column. The new table should look like this.

Cu_id |Tr_id |Date| Days_between
1234 |1 |12/3/2006 |0
1234 |2 |12/18/2006 |15
1234 |3 |1/5/2007 |18
1234 |4 |1/9/2007 |4
1234 |5 |2/21/2007 | 43
9999 |91 |1/3/2006 |0
9999 |81 |1/10/2006 |7
9999 |71 |1/18/2007 |8
9999 |61 |2/1/2007 |14

Please let me know how I can find the number of days between two dates in the same column for the same cu_id (customer_id).

Thanks

View 14 Replies View Related

Count Number Of Pay Days In A Month

Apr 11, 2008

I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.

So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.


As always, your help is very much appreciated!

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

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

How To Find 'x' Number Of Days From 'yyyymmdd' Format?

Nov 15, 2004

I need to find out the count of number of records older than 100 days from a table having 'order_date' as yyyymmdd format eg. 20041115. Thanks in advance.

View 9 Replies View Related

SQL 2012 :: Calculate (Number Of Days To Pay) Logic

Jun 24, 2014

How to write a query that calculate the number of days (approx) that it takes to a customer to pay an invoice.

Document Types

1 Payment
2 Invoice
3 Credit Memo
4 Finance Charge Memo
5 Reminder
6 Refund

There are three methods to consider when calculating the days to pay logic.

Method 1 - Simple : Look for Document Type 2 (Invoice), if "closed at date" > "posting date" then number of days = ("closed at date" - "posting date")

Method 2 - A Document Type 1 (payment) closes a Document Type 2 (Invoice)
For this method the formula would be: Payment Record (1) "posting date" - Invoice Record (2) "posting date"

Method 3 - An Invoice closes the payment.

On a payment entry “closed by entry no.” refers to an Invoice entry.

a. In our code we are not on the payment looking for the invoice, we are on the invoice.
i. Because of this we need to find the entry that our current invoiced has closed.

I am taking this from a page that has the pascal code that I need to translate to SQL.

IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
NOT CustLedgEntry2.Open
THEN
IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")

[Code] ....

I am also including create table and insert data scripts ...

View 9 Replies View Related

T-SQL (SS2K8) :: Hot To Get Number Of Days For Given Date Range

Sep 15, 2014

--From the rows I want to know how many number of days a person was active for the given date range.

create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values

[Code] ....

--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days

2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days

3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days

4) FromDt = '2013-01-01' ToDt ='2014-12-31'

KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/

View 9 Replies View Related

T-SQL (SS2K8) :: Average Number Of Days Between Orders

Apr 7, 2015

I need to calculate the average number of days between customer orders.

My table has customer number and order number and the order date.

I would like to end with customer number last, order date and the average number of days between orders for each customer.

cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Calculate Number Of Days

Oct 8, 2015

I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .

CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016

for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013

then AVG them.

View 3 Replies View Related

Convert DateTime To Int - Display Number Of Days

Sep 1, 2014

I'm stuck on converting a datetime field to Int. Basically, one of the fields in the select returns the datetime of a journey. However I need to be able to add up the number of days for a specific person who done that journey, e.g. it will return 21/08/2014, 22/08/2014 etc...

I then need to total up the days as a new field to display the number of days.

Can you do this by converting a datetime to int then use COUNT on the int?

View 1 Replies View Related

Calculating Number Of Working Days In A Month

Sep 4, 2007

Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.

can anyone say the solution?

thanks.

View 2 Replies View Related

Transact SQL :: Number Of Days Between A Date And Its Month End

Sep 23, 2015

I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
 
Something like below,
 
DateTD       Days left
2009-01-05  14
2009-01-06      13
2009-01-07      12
2009-01-08      11
2009-01-09      10
2009-01-12       9
2009-01-13       8
2009-01-14       7
2009-01-15       6
2009-01-16       5
2009-01-19       4
2009-01-20       3
2009-01-21       2
2009-01-22       1
2009-01-23       0
2009-02-02       /
2009-02-03       /

View 28 Replies View Related

Transact SQL :: User Defined Number Of Days

May 8, 2015

I am new to sql server query. I am trying to write a query for an application and I want user to enter number of over due days for payment.Below is my query and I am getting an error: An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.

SELECT (P.FirstName+', '+P.LastName) As PA, Px.PRespDate,Px.DateFrom
 WHERE Px.Hide = 0 AND Px.Total <> Px.Payments AND Px.PRespDate IS NOT NULL AND PE.Hide = 0 AND PE.BillReady = 1 
AND DATEDIFF(DAY, Px.DateFrom, PRespDate)  LIKE 'Enter # of days |%% '
Group By (P.FirstName+', '+P.LastName), Px.PRespDate,Px.DateFrom

View 2 Replies View Related

Return Number Of Days By Year From Date Fields

Dec 3, 2013

I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.

View 2 Replies View Related

DATEDIFF Case Statement - Calculate Number Of Days

Feb 14, 2014

Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]

This is my query so far

SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Which produces the following

CreatedDate2012-12-17
StageNameLive Transactions
CloseDate2012-12-31
Age424

When in fact the age should be 14days

View 7 Replies View Related

Simple Way To Determine The Number Of Days In A Month For Any Given Date

Jul 20, 2005

DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth

View 1 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Transact SQL :: Using Number Of Days As Stored Procedure Parameter

Aug 7, 2015

We have a stored procedure which currently accepts the following parameter: exec dbo.PurgeOldData <'yyyy-mm-dd'...We want to schedule this sp to run once a (first day of the month) going back to say today's date (or the date when this sp is called to run) minus 365 or purging 12 months data. Basically, the function should convert the today's date minus xxx number of days to yyyy-mm-dd so that the sp accept it as the correct parameter and purges the data successfully. So, if the sp is run on 2015-08-06, it will purge data going back to 2014-08-06 or earlier with the following parameter:

exec dbo.PurgeOldData <'2014-08-06'>

which means, all the data prior to the above date will be purged. Even the sp was run on 2015-08-06, the converted value of 2014-08-06' is entered as the sp parameter.

View 3 Replies View Related

Calculate Number Of Working Days Back From A Date

May 6, 2008

Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime

SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)

-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

View 3 Replies View Related







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