Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

SQL server 2008 query with grouping

Avatar of zachms
zachmsFlag for United States of America asked on
Microsoft SQL Server 2008
14 Comments1 Solution485 ViewsLast Modified:
I am working on a report in SQL server 2008 using the report desinger and need to do some unique grouping.  

Here is my current select statement that works great except for the grouping.

SELECT
  CustInv.TransactionDate
  ,CustInv.UserTranNumber
  ,Cust.CompanyName
  ,CustIDet.AircraftNumber
  ,CustIDet.NumNameCode
  ,CustIDet.Quantity
  ,CustIDet.SalePrice
  ,CustIDet.UnitCost
  ,CustIDet.SalePrice - CustIdet.Unitcost AS Margin
FROM
  CustInv
  JOIN Cust
    ON
       CustInv.CustomerID = Cust.CustomerID
  JOIN CustIDet
    ON
       CustIDet.TransactionID = CustInv.TransactionID
WHERE
  CustInv.TransactionDate BETWEEN @startDate AND @endDate
AND
  CustInv.LocationID = @location
AND
  CustIDet.NumNameCode LIKE 'JET-A%'
ORDER by CustInv.TransactionDate ASC

The results are currently being grouped in the report designer on the field NumNameCode. The values in this field are one of the following:

Jet-A
Jet-A w/Prist
Jet-A * Base
Jet-A * Base w/Prist
Jet-A * Contract
Jet-A * Contract w/Prist
Jet-A * Fractional
Jet-A * Fractional w/Prist

The current grouping keeps them all separated which I know is correct.  What I am trying to do is group them into the 4 types regardless if it is w/prist or not.  My groups should be:
Jet-A
Jet-A*Base
Jet-A*Contract
Jet-A*Fractional

How can I accomplish this?  Do I need 4 individual queries or is there another way to do it?