Hi All,
 
I have a table "SalesDetails" as follows:
 
dtDate Date Not Null,
SalesPersonCode Varchar(15) Not Null,
BranchCode Varchar(15),
Primary Key(dtDate, SalesPersonCode);

This table will contain the details of each sales person for each date under which brach he/she worked. There are fixed 5 branches. Please have a look into sample data:

dtDate        SalesPersonCode BranchCode
01/01/2010  ABC                    Brn1
01/01/2010  LMN                    Brn5
01/01/2010  PQR                    Brn2
02/01/2010  ABC                    Brn4
02/01/2010  LMN                    Brn5
02/01/2010  PQR                    Brn2
 
 
1) I need to display the follwing desired output into the report, for this i need to get column wise each date details for each employee for give date period i.e. specified Start date to Specified End date. If i fail to express, please look into the following required query output for the report.
 
SalesPersoncode Date1 Date2 Date3 Date4 ..... Date30
ABC                    Brn1  Brn4  Brn4  Brn4  ..... Brn1
LMN                    Brn5  Brn5  Brn5  Brn2  ..... Brn3
PQR                    Brn2  Brn2  Brn2  Brn2  ..... Brn2
 
 
2) With this i need to get under each brench for given date period how many time SalesPerson has worked in tabular format:
 
SalesPerson Brn1 Brn2 Brn3 Brn4 Brn5
ABC             4    0    4    2    20
LMN             4    0    2    4    20
PQR            20   0    4    6    0

I need to achieve above results using sql statement(s) only.
 
And is it possible to get No.1 and No.2 output in using single sql, if not then what could be the best way to achieve this output.

Thanks in advance.
 
Vishal

 



[Non-text portions of this message have been removed]