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.
,CustIDet.SalePrice - CustIdet.Unitcost AS Margin
CustInv.CustomerID = Cust.CustomerID
CustIDet.TransactionID = CustInv.TransactionID
CustInv.TransactionDate BETWEEN @startDate AND @endDate
CustInv.LocationID = @location
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 * 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:
How can I accomplish this? Do I need 4 individual queries or is there another way to do it?