Partial Number SQL

I have the below query ....

SELECT  NGPCostPosition.ProjectNo, NGPCostPosition.CostCat, 
        NGPCostPosition.DocumentNumber, NGPCostPosition.TransactionDate, 
        NGPCostPosition.UnitCost, NGPCostPosition.TotalCost, 
        NGPCostPosition.CreditorEmployeeName, NGPCostPosition.SummaryCostCat, 
        PurchaseNGP_PL.CalculatedCost,
CASE
    WHEN
        DATEPART(MONTH, NGPCostPosition.TransactionDate) = DATEPART(MONTH, GETDATE())  
        AND
        DATEPART(YEAR, NGPCostPosition.TransactionDate) = DATEPART(YEAR, GETDATE())
    THEN TotalCost
    ELSE 0
END AS CurrentMonthCost2

FROM  NGPCostPosition INNER JOIN
      PurchaseNGP_PL 
        ON  NGPCostPosition.ProjectNo = PurchaseNGP_PL.PAPROJNUMBER 
        AND NGPCostPosition.DocumentNumber = PurchaseNGP_PL.DocumentNumber 
        AND NGPCostPosition.SummaryCostCat = PurchaseNGP_PL.SummaryCostCat
WHERE NGPCostPosition.ProjectNo = @ProjectNumber 
AND   CostCat ='P070' 
OR    CostCat ='P080' 
AND   NGPCostPosition.ProjectNo = @ProjectNumber 
AND   NGPCostPosition.TotalCost = ABS(PurchaseNGP_PL.CalculatedCost) 

GROUP BY NGPCostPosition.ProjectNo, 
         NGPCostPosition.CostCat, 
         NGPCostPosition.DocumentNumber, 
         NGPCostPosition.TransactionDate,
         NGPCostPosition.UnitCost,
         NGPCostPosition.TotalCost, 
         NGPCostPosition.CreditorEmployeeName,
         NGPCostPosition.SummaryCostCat, 
         PurchaseNGP_PL.CalculatedCost

That gives me the below results ...

What I want to do is limit the column 'ProjectNo' to the first 5 numbers only. (eg. 12169)

Could someone advise if this is possible and what the best way to do this is?

Answers


You can do:

SELECT LEFT(NGPCostPosition.ProjectNo, 5) TruncatedProjectNumber, ....

Then change your grouping to use TruncatedProjectNumber


Need Your Help

Work with PSDs in PHP

php psd

I was recently asked to come up with a script that will allow the end user to upload a PSD (Photoshop) file, and split it up and create images from each of the layers.

Convert NSString of a date to an NSDate

iphone objective-c nsdate nsdateformatter

This might be a silly question, but I can't seem to find the answer on here or in the documentation.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.