[dba-SQLServer] [dba-VB] Is This Possible ?

paul.hartland at fsmail.net paul.hartland at fsmail.net
Tue Feb 17 03:34:39 CST 2004

To all,
Firstly can I say a GREAT big thank you to those of you that helped me yesterday on concatenating fields in SQL Server 7.0.  Your answers worked like a charm.
Now this is where it gets complicated.  Each member of our staff can be available for a full day, or half day (am/pm), so in our employee availability table we have three records like below (Type 0 = Full Day, 1=AM, 2 = PM):
PayrollNo           JobDate             JobNo                Type     AvailCode
9999999            01/03/04           8726                 0          A
9999999            01/03/04                                   1          A
9999999            01/03/04                                   2          A
9999999            02/03/04                                   0          A
9999999            02/03/04           7465                 1          A
9999999            02/03/04           9372                 2          A
I need to somehow turn the above into the example below, If I need to use temporary tables etc I will but if the main calculations etc can be done by Stored Procedures then even better.
Week Commencing         PayrollNo           Mon      Tue       Wed      Thur     Fri        Sat       Sun
01/03/04                       9999999            8726     7465,
I don’t really require the payrollno in the above example asa it will be shown elsewhere along with the Employees name.  The main requirement of the above is that I will need to be able to edit the data.  
Anyone ever done anything like this before ?, all help greatly appreciated.  Anything to get me pointing in the right direction
Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
dba-VB mailing list
dba-VB at databaseadvisors.com

More information about the dba-SQLServer mailing list