September 15, 2006 at 1:16 am
hi all, i am wanting to create a flat file through DTS with a header that has all these zeros, with the last line being the number of records it will process. thereafter each record must have a few constant numbers before each record like 31,01 and 00(these are codes that tell my program which table it will import into). after which it will be followed by the record itself(employee number, date, time) and then 2 more constants (999, 99).
can it be done, or am i taking a ridiculous leap into the unknown.
00
0000000000
0000000000
0000000000
0000000000
0000000000
0000000000
0000000001 (number of records)
31
01
00
0000581139 (employee number)
041805 (Date - mmddyy)
1122 (time - hhnn)
999
99
September 18, 2006 at 8:00 am
This was removed by the editor as SPAM
September 18, 2006 at 1:42 pm
Have u tried to create a view? If u can create a view similar to the one below:
Select '00'
Union all
Select '0000000000'
Union all
Select '0000000000'
Union all
Select '0000000000'
Union all
Select '0000000000'
Union all
Select '0000000000'
Union all
Select '0000000000'
Union all
Select Right('0000000000' + Cast(Count(*) as varchar(10)),10) from tableName
Union all
Select...(your select Statement)
U are working with timeclock system aren't u (ADP Etime??) Just curious.
Thanks
Sreejith
September 20, 2006 at 7:18 am
yeah, it's a Kronos labour management system.
that first part rocks, Sreejith you the man!
now comes the trick, here's my query:
SELECT ssMainData.dbo.ssTagHolders.EmployeeNumber, LEFT(dbo.ssAccessLog.DateTime, CHARINDEX(' ', dbo.ssAccessLog.DateTime, 10)) AS Expr1,
RIGHT(dbo.ssAccessLog.DateTime, LEN(dbo.ssAccessLog.DateTime) - CHARINDEX(' ', dbo.ssAccessLog.DateTime, 11)) AS [Time]
FROM ssMainData.dbo.ssTagHolders CROSS JOIN
dbo.ssAccessLog
which gives me this:
EMPLOYEE NO. DATE TIME
0000003021Sep 13 2006 9:10AM
0000003021Sep 13 2006 9:10AM
0000003021Sep 13 2006 9:10AM
0000003021Sep 13 2006 9:13AM
0000003021Sep 13 2006 9:13AM
0000003021Sep 13 2006 9:14AM
0000003021Sep 13 2006 9:15AM
0000003021Sep 13 2006 9:15AM
0000003021Sep 13 2006 9:16AM
0000003021Sep 13 2006 11:59AM
0000003021Sep 13 2006 12:00PM
0000003021Sep 13 2006 12:02PM
0000003021Sep 13 2006 12:02PM
0000003021Sep 13 2006 12:03PM
0000003021Sep 13 2006 12:04PM
0000003021Sep 13 2006 12:19PM
0000003021Sep 13 2006 1:21PM
0000003021Sep 13 2006 1:22PM
0000003021Sep 13 2006 1:23PM
0000003021Sep 13 2006 1:52PM
0000003021Sep 13 2006 1:55PM
0000003021Sep 13 2006 1:56PM
What i did was split the date and time in 2 as they were in the same column. but what i want it to do is this:
0000003021
Sep 13 2006
9:10AM
0000003021
Sep 13 2006
10:11AM
0000003021
Sep 13 2006
11:10AM etc... put the records one after the other..anymore ideas? many thanks in advance...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply