|
Subject:
|
SQL Calculation
|
|
Posted By:
|
mihabib
|
Post Date:
|
12/12/2005 3:38:12 PM
|
Sorry for the cross posting but I am getting desperate now.
I have a table with 11 fields as follow:
Column Name Data Type Length
strEmployeeID varchar 9
strReportTime_Mon varchar 12
strReportTime_Tue varchar 12
strReportTime_Wed varchar 12
strReportTime_Thu varchar 12
strReportTime_Fri varchar 12
strCommute_Mon varchar 2
strCommute_Tue varchar 2
strCommute_Wed varchar 2
strCommute_Thu varchar 2
strCommute_Fri varchar 2
All the strReportTime are in actually time like 06:00 AM or 06:30 AM or 06:00 PM, etc etc.
Possible data for StrCommute are A through Z and AA, BB and CC. Here is a sample data
091100867 08:00 AM 08:00 AM 08:00 AM 08:00 AM 07:00 AM A A A A A
091098216 08:00 AM 08:00 AM 08:00 AM 08:00 AM 08:00 AM Q Q Q Q Q
091456272 07:00 AM 07:30 AM 07:00 AM 07:00 AM 08:00 AM A A A A A
091748216 09:00 AM 09:00 AM 09:00 AM 09:00 AM 09:00 AM S S S S S
090054234 08:00 AM 08:00 AM 08:00 AM 08:00 AM 08:00 AM A A A A A
090027502 02:30 PM 02:30 PM 02:30 PM 02:30 PM <Null> B B B B CC
091131059 15:00 PM 08:00 AM 07:00 AM 07:00 AM 07:00 AM A A A A CC
Now goal is to find the sum of count of all the commute methods for each day and total for week as follows:
Mode Mon Tues Wed Thu Fri Total
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
BB
CC
Any help would be appreciated.
Thanks.
Habib.
|
|
Reply By:
|
pesq
|
Reply Date:
|
12/13/2005 5:28:15 AM
|
Hi.
For Mode A and column Mon you can do this:
select count(strCommute_Mon) from <table_name>
where strCommute_Mon = 'A'
You can insert result data into a table and do a while do fill all table cells.
This is one option.
|
|
Reply By:
|
Anantsharma
|
Reply Date:
|
1/6/2006 5:23:16 AM
|
See "Rotating Table in Sql Server " Topic in BOL or MSDN. There is an example like this one.
B. Anant
|
