CODEWITHSUNDEEP
sqlsql-server-2005
user1086159
user1086159

Reputation: 1055

How to add a column for just current month data to a multimonth query?

I have a simple query:-

SELECT *
FROM dbo.NGPCostPosition

That returns the below data:-

enter image description here

I want to try and display a total cost for just the current month while still having access to all other data so maybe a new column called current month that would only be populated by items that fall into that category?

What is the best way to do this?

All advice welcome and appreciated.

Upvotes: 0

Views: 153

Answers (3)

LukeGeneva
LukeGeneva

Reputation: 584

This should add an extra column to the query just showing current month total costs.

SELECT
    *,
    CASE
        WHEN
            DATEPART(MOTNH, TranDate) = DATEPART(MONTH, GETDATE()) AND
            DATEPART(YEAR, TranDate) = DATEPART(YEAR, GETDATE())
        THEN TotalCost
        ELSE 0
    END CurrentMonthCost
FROM dbo.NGPCostPosition

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

try this:

 SELECT doctype, projectNo, CostCat, [all other columns etc.],
   (Select Sum(Quantity * UnitCost) From NGPCostPosition 
    Where transDate >= DateAdd(month, 
                        datediff(month,0,p.Transdate), 0)
       And transDate < DateAdd(month, 
                        datediff(month,0, p.Transdate), 31)) MonthlyTotal    
 FROM NGPCostPosition p

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

You could add a column to your query like so:

        Select *, 
CASE WHEN 
    datepart(mm, getdate()) == datepart(mm, TranDate) 
    and datepart(yy, getdate()) == datepart(yy, tranDate) 
    then TotalCost 
else 0 
end as CurrentMonthTotalCost

And then sum that up somewhere in your sheet. You could also use date formatting (I don't prefer these as they're a bit of a cipher as to what you're comparing):

      Select *, 
    CASE 
WHEN CONVERT(VARCHAR(7), GETDATE(), 111) == CONVERT(VARCHAR(7), TRANDATE, 111) 
THEN TotalCost 
ELSE 0 
End as CurrentMonthTotalCost

Upvotes: 0

Related Questions