SQL server 2008 query with grouping

Or this one, which gives you the NumNameCode as well as a new field GroupNumNameCode that you can use for grouping

SELECT
  CustInv.TransactionDate
  ,CustInv.UserTranNumber
  ,Cust.CompanyName
  ,CustIDet.AircraftNumber
  ,CustIDet.NumNameCode
  ,replace(CustIDet.NumNameCode, ' w/Prist' ,'') GroupNumNameCode
  ,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

Select allOpen in new window

Thanks for the great fast responses.  All of them have been helpful.  As I dug into the data the formating is slightly different but it is working for the most part.  It appears that the descriptions that contain the * in them are not working properly. If I run the query and look at the data that is being returned it all looks correct. When I go to do the grouping in the sql statement it tells me that the GroupNumNameCode is not a valid column. When I use the grouping in the report builder it works for about half of the items.  

>> It appears that the descriptions that contain the * in them are not working properly.
Can you post some sample data like this?
Make sure to desensitize it but still show the pattern.

If you intend to use GROUP by in sql server, then use this expression

GROUP BY replace(CustIDet.NumNameCode, ' w/Prist' ,'') -- and other columns

and remove "  ,CustIDet.NumNameCode" from the select list.
I thought you were doing the grouping at the reporting front end like Crystal Reports.

SELECT
  CustInv.TransactionDate
  ,CustInv.UserTranNumber
  ,Cust.CompanyName
  ,CustIDet.AircraftNumber
  --,CustIDet.NumNameCode
  ,replace(CustIDet.NumNameCode, ' w/Prist' ,'') GroupNumNameCode
  ,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%'
GROUP BY replace(CustIDet.NumNameCode, ' w/Prist' ,'')
ORDER by CustInv.TransactionDate ASC

Select allOpen in new window

@cyberkiwi,

Wont that cause issues with the other columns that do not contain an aggregate function ? e.g.

"Column 'Cust.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

So, would need to include all those selected columns either in the Group By, or, have some kind of aggregation applied (like sum(), count() etc)

@zachms

If doing a group by in the T-SQL select statement, then easiest way is to copy and paste the "select" part with all the columns and change that (pasted bit) to a "group by" removing those columns that you will aggregate like quantity, margin etc.

But it sounds like there is more to this. If doing it in the report, then would imagine if you are using the new column for the group band, then it should be working OK.

When you say "it is not working" or "working for about half" what do you mean ? It is missing some entries - which ones ? do any of the reformatted groupnumnamecode make it into the report ? is it still showing the old names ? have you done a refresh on the datasource ?

@Mark re: 33158654

The code box was my attempt at a solution, then I thought better given the ambiguous requirement and posed a question and a generalized comment about GROUP BY on an expression.
I did not realize the code was still sitting in the code box.

Yes, all columns in the select list must either be a GROUP BY column/expression or an aggregate.

I have attached my current code that I am using.  When I run the query in the report builder to test the query the data results look like it should work.  The column of data for GroupNumNameCode is the truncated names and the original data column is left alone.  However, when I run the report on the server it is not grouping the values that have an asterick in them.

here is a list of the possible values for NumNameCode:

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

When I run the query and preview the data it is taking the w prist portion off properly,  however the report output that has the grouping in it is it not grouping the Jet-A*Base Tenant items or the contract of fractional.  I don't know if having the * is the problem or not.  I have set the report builder grouping to group on GroupNumNameCode.

SELECT
  CustInv.TransactionDate
  ,CustInv.UserTranNumber
  ,Cust.CompanyName
  ,CustIDet.AircraftNumber
  ,CustIDet.NumNameCode
  ,replace(CustIDet.NumNameCode,' w Prist','')GroupNumNameCode
  ,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 >= DATEDIFF(d,0,getdate()) -1
AND
  CustInv.TransactionDate < DATEDIFF(d,0,getdate())
AND
  CustInv.LocationID = 1
AND
  CustIDet.NumNameCode LIKE 'JET-A%'
ORDER by CustInv.TransactionDate ASC

Select allOpen in new window

Strange...  Did you refresh the datasource in the report and then remove the group and add it back in using the groupnumnamecode ?

Two things that might be happening. The replace of multi characters with a single character puts a few DEL characters in there, admittedly, you dont normally see it in this type of situation.

Second, maybe it is the * although cannot understand why that would be.

The "test" is if everything is reporting properly in the first group.

What we can also do is use a substring (or take the left most characters before the ' w prist') and also replace the * with a space.... Gotta say it does sound a bit extreme, but lets try anyway.... Also, we might want to cast as a set length...

replace(left(CustIDet.NumNameCode,charindex(' w Prist',CustIDet.NumNameCode + ' w prist')),'*',' ') as GroupNumNameCode

Thanks for the response.  When I try it using the replace string you have listed it does not group any of them but the raw data appears to be correct if I look at the query results before running the report.  Not sure why the report when ran would not function properly when the data is being created properly.

So, what happens to them ? Are they entirely missing ? Or just appear haphazardly ?

The other way is to create a view of the data, then in your report, use the view as the datasource so it is being generated on the server rather than the report generating the query (assume that is what you are doing). Are you in a position of being able to create views in the database ?

Have you got the RDL ? (sorry, what report engine are you using ?)

All of the data is there, they are just grouped by the original values. I have deleted the grouping several times to make sure it is using the GroupNumNameCode for the grouping and sorting.

I have the ability to make views in the database as I manage the database server as well. I am a one man IT shop.  I am using the SQL Server 2008 reporting services to do this.

>> I am a one man IT shop

Well, that does have its advantages - in some regards :)

SSRS does get itself confused sometimes when you are changing the datasource. Sounds like this is one of those times....

Is it a complex report ? Are the "where" options supplied by parameter or specified as part of the query ?

OK, let's try this quick quiz.

1) create a view - in SSMS open a new query window, select your database...

CREATE VIEW vw_customer_transactions AS
SELECT
  CustInv.TransactionDate
  ,CustInv.UserTranNumber
  ,Cust.CompanyName
  ,CustIDet.AircraftNumber
  ,CustIDet.NumNameCode
  ,replace(CustIDet.NumNameCode,' w Prist','') as GroupNumNameCode
  ,CustIDet.Quantity
  ,CustIDet.SalePrice
  ,CustIDet.UnitCost
  ,CustIDet.SalePrice - CustIdet.Unitcost AS Margin
FROM CustInv
INNER JOIN Cust ON CustInv.CustomerID = Cust.CustomerID
INNER JOIN CustIDet ON CustIDet.TransactionID = CustInv.TransactionID

GO

2) create a new report (yep just a quicky)

3) set the view as your datasource, add in what ever WHERE clause you want (if not included in the above)

4) create a simple report with the groupnumnamecode as a grouping

Both methods provided the desired results.