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
In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.
I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.
I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.
I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.
For example, on the table,
TimeoffID employeename startdate enddate timeofftype 1 Andy 11/02/2006 11/03/2006 Sick Day 2 Bill 11/03/2006 11/03/2006 Sick Day
Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.
For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.
1234510.1 --> Record 1 1235010.1 1235510.1 1236010.1 1236510.1 1237010.5 --> Record 2 1237510.5 1238010.5 1238510.5 123909.7 --> Record 3
Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.
why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'myDateRangePF') ORDER BY [ID] ASC
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.
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.
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)
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment) ,( ((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
Guys Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number of periods betwen the startdate and enddate so for example
-- Initial data insert @Periods ( StartDate , Enddate , NumberOfMonthsInPeriod , TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below )
select '30-Sep-2005', '10-Apr-2009', 1 -- 1 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 3 -- 3 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 6 -- 6 month period 0
The following rules regarding periods apply Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie 01-Jan - 31 Jan 01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...) and so on to December
Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year 01-Jan - 31-Mar 01-Apr - 30-Jun 01-Jul - 30-Sep 01-Oct - 31-Dec
Each 6 (NumberOfMonthsInPeriod) month period is one of the following 01-Jan - 30-Jun 01-Jul - 31-Dec
I need to derive TotPeriods as follows:
For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44
For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16
For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1 PoNo :- Po No 1 ShipDate :- Oct 5, 2007
OrderNo : 1 PoNo :- Po No 2 ShipDate :- Dec 5, 2007
OrderNo : 1 PoNo :- Po No 3 ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1 PoQty :- 2000
PoNo :- Po No 2 PoQty :- 3000
PoNo :- Po No 3 PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number 2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3 DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null) CustomerID (FK, int, null) PaymentDate (datetime, null) UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X: Resource: 100 Starting date: 2012-03-01 (from this date the schedule will be effective) Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30 Record 2: same but daynumber is 2 Record 3: same but daynumber is 3 etc. ... Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00 Record 9: same as record 2 but daynumber is 9. etc. ... Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber) 2014-05-02 = day 5 2014-05-03 = day 6 ... 2014-05-10 = day 13 2014-05-11 = day 14 2014-05-12 = day 1 2014-05-13 = day 2 2014-05-14 = day 3 ... 2014-05-24 = day 13 2014-05-25 = day 14 2014-05-26 = day 1 2014-05-27 = day 2 ... 2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008' @EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
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.
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01 e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:
And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.
But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:
But can't seem to get this using a self join query like this:
select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID from arrivals a INNER JOIN arrivals b ON b.personID = a.personID WHERE a.arrivalid != b.arrivalid and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7 and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT non empty [Measures].[TRANSACTIONS Count] on 0, non empty ([TRANSACTIONS].[Days].[Days], [TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER [Measures].[Cumm Account Count] AS ( AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count]) )
SELECT non empty [Measures].[Cumm Account Count] on 0, non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
