Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg