T-SQL (SS2K8) :: Creating Missing Records In A Date / Time Range?
Nov 6, 2014
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.
View 6 Replies
ADVERTISEMENT
Apr 22, 2014
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.
View 9 Replies
View Related
Oct 14, 2014
I'm trying to get a count of Employed and Available contractors per time period, and I have a table of Contracts... something like:
CREATE TABLE empContract(
empContractID INT IDENTITY(10000,1) PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE,
ContractorAssigned INT,
FOREIGN KEY ContractorID REFERENCES Contractor(ContractorID)
);
I don't think this is possible without the existence of some kind of Calendar table. Given the existence of a calendar table, the query seems really simple - just something like:
SELECT cal.CalendarDate, ec.ContractID
FROM Calendar cal LEFT JOIN empContract ec ON cal.CalendarDate BETWEEN ec.StartDate AND ec.EndDate
The left join forces the existence of all dates in a range (@StartDate and @EndDate), so that when I try to create a graph with counts by day, I don't have any gaps in my time series.
View 2 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Aug 16, 2006
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!!
View 6 Replies
View Related
Apr 26, 2015
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
View 7 Replies
View Related
May 26, 2015
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
View 9 Replies
View Related
Jun 26, 2014
I would like to get the first and last day of any month in a given date range.
Ex: Display the first and last day of the months between @startDate and @EndDates.
Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'
OutPut should be:-
MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016
View 1 Replies
View Related
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
Mar 14, 2014
I'm trying to modify a date range, but it's much more complicated.
Let's say I have existing date ranges of:
BlockIDStartDateEndDateActive
182013-12-31 00:00:002013-12-31 00:00:001
192014-01-01 00:00:002014-01-23 00:00:001
202014-01-24 00:00:002014-01-25 00:00:001
212014-01-26 00:00:002014-02-04 00:00:001
222014-02-05 00:00:002014-02-13 00:00:001
232014-02-14 00:00:002014-02-15 00:00:001
I've created code that will do the following (depending on the inputs):
1) Create new start and end dates
2) Inactivate obsolete date ranges
View 8 Replies
View Related
Jun 6, 2012
I need to compare the contents of a column.
I have a table with 1 column. Below are the sample contents..
DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000
[code]....
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000
if it is not doable, we can get the nearest record of all missing records like this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000
or this:
DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000
or this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000
View 6 Replies
View Related
Aug 27, 2014
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.
View 5 Replies
View Related
Apr 1, 2008
I have a 7 million line table named SecurityID with the following data:
Date, Security, Identifier1, Identifier2, Identifier3
I am trying to reduce it to a table newSecurityID in the following form:
FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3
This new table will have the first instance for each securityId with the identifying information. New rows will be added If any of the 3 identifying information changes. This isn't as simple as querying for the maximum and minimum value given each distinct group of identifiers because identifiers can change from an initial set and then change back to the initial values.
My plan was to first select all distinct (Security, Identifier1, Identifier2, Identifier3) into a temporary table. Then query the table SecurityID for the minimum date available which matches these 4 fields and find the corresponding maximum value.
This doesn't seem to working as I had planned as I am getting one row for each date rather than when identifiers change. Plus its taking a really long time to finish.
Any help will be appreciated!
Here is my code:
select distinct SecurityId, Identifier1, Identifier2, Identifier3 into #DistinctSecurityID from SecurityID
insert into
newSecurityID (FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3 )
select
FromDate=min( a.Date),
ToDate=isnull((
select min(b.Date)
from
SecurityId b
where
b.SecurityId=a.SecurityId
and b.Date>a.Date
and (a.Identifier1!= b.Identifier1
OR a.Identifier2!=b.Identifier2
OR a.Identifier3!= b.Identifier3)
),'20991231'),
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3
from
SecurityID a,
#DistinctSecurityID
where
#DistinctSecurityID.SecurityId=a.SecurityId
and #DistinctSecurityID.Identifier1=a.Identifier1
and #DistinctSecurityID.Identifier2=a.Identifier2
and #DistinctSecurityID.Identifier3=a.Identifier3
group by
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3,
a.SecurityId,
a.Date,
a.Identifier1,
a.Identifier2,
a.Identifier3
order by
a.SecurityId,
min(a.Date)
View 1 Replies
View Related
Jun 11, 2014
I have to find the records with in a paricular dates(from date and to date). In some cases @FromDate or @ToDate could be null. in the following query, when i am passing values @FromDate and @ToDate and execute the SP i am getting records which are not in the given range.
SELECT * FROM TABLE P WHERE
CONVERT(VARCHAR(10), P.[FromDate], 101) BETWEEN
CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10),@minFromDate, 101)
ELSE CONVERT(VARCHAR(10), @FromDate, 101) END
AND CASE WHEN @ToDate IS NULL Then CONVERT(VARCHAR(10), @maxToDate, 101)
[Code] .....
View 4 Replies
View Related
Jul 31, 2014
Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:
7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
View 9 Replies
View Related
Aug 20, 2006
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies
View Related
Dec 3, 2007
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View 1 Replies
View Related
Aug 19, 2013
I want to get all records that have a valid date range for todays date(20130819).
All records have a date_f and date_t. I need to check that against todays date. The below code is my version of sql pseudo code.
SELECT DISTINCT
p.id,
p.name,
c.ip_number
FROM
tbl_Person AS p, tbl_current_conn_ipnumber AS c
[Code] .....
View 2 Replies
View Related
Oct 14, 2015
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
View 7 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
Jan 15, 2012
If you have a table that has records which are applicable over a date range, is there a preferred design for dealing with the dates?
A simple example might be an employee table, which might have multiple employees, with each employee have multiple records, each record being applicable over a particular date range.
For instance:
Code:
EmpID EmployeeName StartDate EndDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011 8/10/2011
00001 Jones, Jerry 8/11/2011 1/7/2012
00001 Jones, Jerry 1/8/2011 12/31/3000
00002 Fredricks, Fred 8/3/2011 10/15/2011
00002 Fredricks, Fred 10/16/2011 12/31/3000
One could model this table with an implied EndDate (of 12/31/3000), as in:
Code:
EmpID EmployeeName StartDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011
00001 Jones, Jerry 8/11/2011
00001 Jones, Jerry 1/8/2011
00002 Fredricks, Fred 8/3/2011
00002 Fredricks, Fred 10/16/2011
Or, one could imply the beginning date and store the first record's beginning date elsewhere, in a date hired table, or in additional field on each record. As in:
Code:
EmpID EmployeeName EndDate HireDate AdditionalFieldsOfData
00001 Jones, Jerry 8/10/2011 4/6/2011
00001 Jones, Jerry 1/7/2012 4/6/2011
00001 Jones, Jerry 12/31/3000 4/6/2011
00002 Fredricks, Fred 10/15/2011 8/3/2011
00002 Fredricks, Fred 12/31/3000 8/3/2011
View 4 Replies
View Related
Aug 21, 2014
I have two tables, customers and appointments.
I want to bulk insert records for a date range and a day of the week.
For example, John Smith has an appointment on every monday for the next three months. How do I accomplish this?
View 1 Replies
View Related
Mar 11, 2008
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:
01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>
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.
View 3 Replies
View Related
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
Nov 3, 2006
hello friends..
i aleays facing this problem while reporting....
if i want to show report for date range then i am not getting records for end date...why???
my report query was
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '10/31/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3
i am getting rows 15 here but when i fired this
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '11/01/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3
then i am getting rows 16
previous one i always missed records on 10/31/2006...is there any solution or i always add one day to end date and then get values??
please help me out
Thanks
View 4 Replies
View Related
Mar 28, 2007
I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "". But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12. If @month is empty, I want all the records to be displayed, but nothing is. If @month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not? Diane
View 5 Replies
View Related
Apr 6, 2015
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
Table B
Item ProcessStartDate ProcessEndDate
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
View 8 Replies
View Related
Jul 15, 2014
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
View 4 Replies
View Related
Dec 21, 2014
I have a standard datetime and I need to convert it to the client specification of:
YYYY-MM-DDThh:mm:ssTZD
eg: 2009-04-16T19:20:30+08:00
I am not sure of the easiest way to do this.
The test code below gets me part of the way but I am unsure on how to get the offset on the end without hardcoding to much.
DECLARE @datetime DATETIME = '2014-12-20 12:30:00'
SELECT CONVERT(VARCHAR(30),@datetime,127)
View 2 Replies
View Related
Aug 3, 2015
We have 2 columns:
StartDate - data type Date
StartTime - data type Time
I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:
convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))
Is there a better, more elegant way to do this?
View 9 Replies
View Related
Dec 3, 2001
Hi all-
I was wondering if you had created a default for data/time (I'm looking to create one that will insert the equiv. of time()). When I try to make one and set the value to time() it tells me the value cannot be anything except a function, constant, or var. What's the deal?
Thanks.
-Chris
View 1 Replies
View Related
Aug 27, 2014
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
View 1 Replies
View Related
Nov 13, 2007
Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
View 8 Replies
View Related
