[dba-VB] [dba-SQLServer] 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,
                                                                        9372
08/03/04
 
15/03/04
 
22/03/04
 
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 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-VB mailing list