February 17, 2012 at 8:52 am
Hi Guys
if this topic has already a solution I would be glad if you could point me to a solution (didn't know what to search for and the script-collection is rather huge...)
ok to my problem: I'm importing data from a .txt file provided by another programm which shows me de vacation days of employees, but rather then giving me every single vacation day it givs me only start and end date.
(If the vacation is only one day theres no end date provided) -> IsNull Test in script
What I need now is a table that gives me for every employee the dates where he's on vacation.
let me show you the table structure:
Source table: (columns seperated by ";"
tblsSource:
ID;Name;StartDate;EndDate;Length;Type
1;Employee A;02.01.2012;;1,00;Vacation.
1;Employee A;18.06.2012;21.06.2012;4,00;Vacation.
1;Employee A;01.01.2012;;1,00;Holiday.
2;Employee B;03.10.2012;;1,00;Holiday.
2;Employee B;13.12.2012;14.12.2012;2,00;Vacation.
2;Employee B;31.12.2012;01.01.2013;2,00;Holiday.
What I need as Target is:
tblTarget:
ID;Name;absenceDate
1;Employee A;02.01.2012
1;Employee A;18.06.2012
1;Employee A;19.06.2012
1;Employee A;20.06.2012
1;Employee A;21.06.2012
1;Employee A;01.01.2012
2;Employee B;03.10.2012
2;Employee B;13.12.2012
2;Employee B;14.12.2012
2;Employee B;31.12.2012
2;Employee B;01.01.2013
Is this possible with SQL (if yes then my working knowledge of SQL is to limited) or (what I think) does it need a vbs script to add the lines
It would propably look something like: (just conceptional because I only programm in vba)
Declare and Set tblSource
Declare and Set tblTaget
Dim StartDate as Date
Dim EndDate as Date
------------
for each line in tblSource do:
If IsNull(tblSource.EndDate) then
add row to tblTarget 'only one vacation day which is added to tblTarget
Else
StartDate = tblSource.StartDate
While StartDate <= EndDate Do
Add Row to tblTarget Set ID = tblSource.ID, Name = tblSource.Name, absenceDate = Startdate
StartDate = StartDate + 1
Wend
endif
Next
I mostly program in VBA (Access), so if somebody could translate this routine in a VBS or SQL script, I would be very very happy!!!
Thanks in advanced for reading!
Cheers
Steffen
February 17, 2012 at 9:38 am
Use the import wizard to build an SSIS package. Only map the columns you need to the target.
February 17, 2012 at 9:41 am
I'll give it a try and will report back... haven't tested the wizard so far
Thanks
February 17, 2012 at 9:50 am
Ok Importing is no Problem
but I need to do the calculations as specified at the top
the import gives me only the start and end date of vacations, but I also need the days inbetween
and as far that I see the Wizard doesn't give me any options... I want to perform an automatic task on the table that runs every hour with an SQL or VBS script
Cheers
February 17, 2012 at 10:10 am
OK let's close this one here... just figured it's the Server 2008 section... running 2005 at the moment ^^
well if some VB-Enthusiast is klicking by I'd be grateful for help 🙂
Cheers
February 17, 2012 at 10:47 am
Wondering if you have considered writing a query that will put the data in the format that you are looking for?..? That will allow you to load the data as you get it in the text file and have a query that will pull it in the format you desire. Is that what you are looking for long term?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2012 at 10:56 am
well the SourceTXT is coming as an export from an propritary programm so I can't change anything there.
The way I thought it would work:
1. Import the txt File into a tbl in my Database (got that)
2. Run a script that fills and adds the inbetween Dates into another table on my Database (e.g. tblTarget)
Point 2 is where I'm stuck
Does that answer your question?
February 17, 2012 at 11:01 am
Yes. I'm thinking that you don't need the second table but could rather just do that with a query and be able to get the data in the format you want anytime you run the query. If you send some create table scripts for the base table that you are loading the data into and some sample inserts to get a test set of data there I can try to work on something later this afternoon - or someone else can probably get you some suggestions sooner.
Check out this article [/url]on how to post scripts / data in a way that will make it easy for people to help you out. It really goes a long way. 🙂
Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2012 at 11:05 am
I'll put a CREATE skript together 😉
Well yes...reading Forum etiquette first would help *darn 😉
I'll be back in a bit
February 17, 2012 at 11:08 am
SteffenW (2/17/2012)
I'll put a CREATE skript together 😉Well yes...reading Forum etiquette first would help *darn 😉
I'll be back in a bit
Sounds great. Thanks for checking out the article as well. It really does help and you will get a bunch of responses that way.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2012 at 11:48 am
stupid... was confused because we're already on page 2
February 17, 2012 at 12:03 pm
Ok as David suggested I've put together a create skript to give you the sample Data
This is a Table with employee vacation Dates where the start and End-Date are specified
(EndDate = Null when its only one day vacation)
--===== If the test table already exists, drop it
IF OBJECT_ID('PRODA..tblAbsence','U') IS NOT NULL
DROP TABLE tblAbsence
--===== Create table Absence
CREATE TABLE tblAbsence
(
ID INT IDENTITY(1,1),
Name varchar(40),
Type nchar(10),
StartDate DATETIME,
EndDate DATETIME,
Length SMALLMONEY,
Comment nchar(20)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT tblAbsence ON
--===== Insert the test data into the test table
INSERT INTO tblAbsence
(ID, Name, Type, StartDate, EndDate, Length, Comment)
SELECT '1','EmpA','F','04.06.2012 00:00:00','06.06.2012 00:00:00',3.00,'Vacation' UNION ALL
SELECT '1','EmpA','F','20.07.2012 00:00:00',NULL,1.00,'Vacation' UNION ALL
SELECT '1','EmpA','U','23.10.2012 00:00:00','24.10.2012 00:00:00',2.00,'Holiday' UNION ALL
SELECT '2','EmpB','F','03.02.2012 00:00:00','04.02.2012 00:00:00',2.00,'Vacation' UNION ALL
SELECT '2','EmpB','U','10.04.2012 00:00:00','20.04.2012 00:00:00',11.00,'Holiday' UNION ALL
SELECT '2','EmpB','F','02.04.2012 00:00:00','05.04.2012 00:00:00',4.00,'Vacation' UNION ALL
SELECT '3','EmpC','F','01.05.2012 00:00:00',NULL,1.00,'Vacation' UNION ALL
SELECT '3','EmpC','F','06.10.2012 00:00:00','12.10.2012 00:00:00',7.00,'Vacation'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT tblAbsence OFF
as written above I somehow need an automated process that calculates me the days of absence into either a new table or inserts the rows in the one above.
What I need is a table that gives me the Employees ID and the Date he is absence (one row for every Day he's not at work)
so the table above would give out a total of 29 rows
I thought of an VB-Script run as a Job something like this (I've no Idear of VBS, I only programm with VBA (Access))
something like this:
Declare and Set tblSource
Declare and Set tblTaget
Dim StartDate as Date
Dim EndDate as Date
------------
for each line in tblSource do:
If IsNull(tblSource.EndDate) then
add row to tblTarget 'only one vacation day with is added to tblTarget
Else
StartDate = tblSource.StartDate
While StartDate <= EndDate Do
Add Row to tblTarget Set ID = tblSource.ID, Name = tblSource.Name, absenceDate = Startdate
StartDate = StartDate + 1
Wend
endif
Next
Thanks for your help!
Steffen
February 17, 2012 at 1:18 pm
I appreciate that you're new to SQL Server and please don't take what I have to say as anything other than me trying to help you learn some things. There are a couple of problems with your approach.
#1) You have to understand the difference between procedural logic and set based logic. Any form of VB is by nature procedural wheras the strenght of SQL is set based manipulation. Some tasks cannot be done well through t-sql (such as string parsing) but other things can be done much better than through a procedural process. If I understand your goal here, you should be asking how to accomplish what you want to do through a set based solution, not through procedural step-by-step code.
#2) I'm guessing that you're storing all this data you're getting from your flat file, but I would suggest limiting your import and cleaning up your data during the import process. You have both a code for the time off type and the description, which is redundant. You also have the number of days and the end date, which can be calculated from the number of days, so this is also redundant. Lastly, you need to be very careful with date data types. Be aware that what you're using is a non-standard localized format that can potentially be misinterpreted. DD.MM.YYYY can be understood to be MM.DD.YYYY based on the localization settings of the current user. Lastly, I'm not sure why you used a currency data type for the length of time off. My 'solution' just uses an int and thus doesn't allow partial days (which seems consistent with what you're requesting) so your actual needs may require a tweak.
Here is the sort of approach I would point you towards ...
create table dbo.TimeOff
( TimeOffID int identity(1,1) primary key clustered
, EmpName varchar(40) not null
, TimeOffType char(1) not null
, TimeOffStartDate smalldatetime not null
, TimeOffLength int not null );
create table dbo.TimeOffType
( TimeOffType char(1) primary key clustered
, TimeOffTypeDescription varchar(50) not null )
insert into dbo.TimeOffType (TimeOffType, TimeOffTypeDescription)
values
('F', 'Vacation'),
('U', 'Holiday');
--data taken from your script but limited and cleaned ... this would be the staging data
insert into dbo.TimeOff (EmpName, TimeOffType, TimeOffStartDate, TimeOffLength)
values
('EmpA','F','20120604', 3),
('EmpA','F','20120720', 1),
('EmpA','U','20121023', 2),
('EmpB','F','20120203', 2),
('EmpB','U','20120410', 11),
('EmpB','F','20120402', 4),
('EmpC','F','20120501', 1),
('EmpC','F','20121006', 7);
My query below depends on a 'numbers' or 'tally' table. It's just a simple table in the master db that has a single int column N with numbers from 0 to a million and it has a lot of uses such as this. For this, we'll only use a small portion of those so a smaller numbers table would work fine.
select t.EmpName,
DayOff = dateadd(dd, n.N, t.TimeOffStartDate),
t.TimeOffType
from dbo.TimeOff as t
join master.dbo.Nums as n
on n.N < t.TimeOffLength
order by t.EmpName, t.TimeOffStartDate, n.N;
This simple query will give you an individual record for each day that anyone is off. I am not accounting for weekend days and I don't know what your business rules are for that and I don't want to try to guess. This is meant to get you going in the right direction and certainly should not be take as a final solution.
Good luck.
February 17, 2012 at 1:41 pm
ok I haven't checked your complete code yet, especially the point with the extra table in the db-master...
I know of the reluctant data but well the output gives me no less and yes I would definitly clean this up
the start/end date and length are not completly reluctant because there might be weekends inbetween that are not included in the length sry haven't check on that on the input -.-
so I specifily need a calculation out of start and end-date
an well I only need [ID, StartDate, EndDate] as input no more
I haven't bothered yet with cleaning reluctant data because the table will never exceed 20000 records (I was to lazy just yed 🙂 )
Thanks alot bteraberry!! but I think I need calculations from start to end
February 17, 2012 at 1:41 pm
I'll add a small change to the version the previous poster put up using a CTE numbers table. This result will get all the days and I have spot checked that. The CTE is assuming a max number of days off being 30 as I would be surprised if a company would let someone take more than that off... I would however, recommend the physical tally table if you are allowed to create it as there are many other uses. You should check out THIS ARTICLE[/url].
Solution that works with your present table.
;WITHNumbers(Number) AS (
SELECT 0 AS Number
UNION ALL
SELECTNumber + 1
FROMNumbers
WHERENumber < 30
)
SELECTt.Name,
Type,
CASE
WHEN n.Number = 0 THEN StartDate
ELSE DATEADD(DD, n.Number, StartDate)
END AS DayOffDate
FROMtblAbsence t INNER JOIN Numbers n
ON n.Number < t.Length
ORDER BY t.Name, t.StartDate, n.Number
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply