Janis said:
tblRouter:RouterName
tblUtilization:routerName, weekending date, utilization %
What I want to do is output the routername, and weekending date columns to
Excel. I just want to create a table so i can export it to Excel.
like this:
RouterName, 8/25, 9/1, 9/7
UBRa 50%, 60%, 75%
UBRb 25%, 30%, 35%
UBRc 89%, 90%, 95%
I don't know how to write the query from the two tables above so I can get
exactly this output? I only need to export the last 6 weeks. The dates
aren't always exactly 7 days apart.
Hi Janis,
Looks like a perfect candidate for a crosstab query.
If you are not familiar with them, they are just
"glorified" group by queries, that can add extra
columns to a group by.
So, let's start with a simple group by query.
SELECT
U.RouterName
FROM
tblUtilization AS U
GROUP BY
U.RouterName;
result:
RouterName
UBRa
UBRb
UBRc
That was easy enough...
It is not clear to me what function the other
table is playing. Are there many router names
in tblUtilization, but you only care about the
ones in tblRouter?
If so, then our simple group by would include a
join to this table to get only those names.
SELECT
U.RouterName
FROM
tblUtilization AS U
INNER JOIN
tblRouter AS R
ON
U.RouterName = R.RouterName
GROUP BY
U.RouterName;
Okay...now the crosstab magic...
For every routername group, we would
like to "pivot out" some extra columns
for each date in the group, then under those
columns "transform" some aggregate value
for each of those date columns.
In this case, we don't want to sum or count
or find max/min or avg... we just want the
[utilization %] value for that router/date...
so we will use the aggregate "First"
TRANSFORM First(U.[utilization %])
SELECT
U.RouterName
FROM
tblUtilization AS U
INNER JOIN
tblRouter AS R
ON
U.RouterName = R.RouterName
GROUP BY
U.RouterName
PIVOT U.[weekending date];
Pretty slick huh?
All that is left is to restrict to last 6 weeks
(last 6 x 7 = 42 days).
This may the hardest part because it is not
apparent to me whether your [weekending date]
is type Date/Time (since you do not show a year
in your example).
If they were type Date/Time, all that is left is to
add a WHERE clause to your internal group by.
TRANSFORM First(U.[utilization %])
SELECT
U.RouterName
FROM
tblUtilization AS U
INNER JOIN
tblRouter AS R
ON
U.RouterName = R.RouterName
WHERE
U.[weekending date] >= Date() -42
AND
U.[weekending date] < Date() + 1
GROUP BY
U.RouterName
PIVOT U.[weekending date];
So...what happens if [weekending date] is text?
Text does not sort like dates.
The "order" of string dates will confound you...
1/25
10/25
11/25
12/25
2/25
3/25
4/25
5/25
6/25
7/25
8/25
9/25
The WHERE clause above will not work.
The DateSerial function is efficient but what is the year?
Will the year always be the current year?
What about when you do this sometime in January
and then some of the years will be the previous year?
If these are string dates, I might suggest first seeing if
you cannot get them in full mm/dd/yyyy.
Else, add another column (type Date/Time) to your table
and figure out how to update the string date to a real date
and store it in this new column.
Then use that new column in the crosstab query.
good luck,
gary
