CODEWITHSUNDEEP
sqlsql-servert-sqlselectwhere-clause
dmbb
dmbb

Reputation: 55

Is it possible to add second where condition to select this same data but from other date range?

I have two tables in SQL Server.

enter image description here

I want to select DeptCode, DeptName, YearToDate, PeriodToDate (2 months for example) and group it by DeptCode. There is a result which I want to get:

enter image description here

I created a piece of code which shows me correct YTD cost but I don't know how I can add next one for getting total cost for other date range. Is it possible to do this?

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD
FROM [Departments] AS d
INNER JOIN Shipments AS s
ON d.DeptCode= s.DeptCode
WHERE s.ShipmentDate BETWEEN DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()), 0) 
AND GETDATE()
GROUP BY d.DeptCode, d.DeptName

Upvotes: 1

Views: 84

Answers (3)

Mohamed Azizi
Mohamed Azizi

Reputation: 162

Try this one :

nbr_last2month_ AS
(
SELECT DISTINCT 
   Sum(s.[TotalCost]) AS 'PTD', 
   s.DeptCode, 
   s.DeptName
 FROM    [Shipements] s
         LEFT JOIN [Departements] d ON d.[DeptCode] = s.[DeptCode] 
 WHERE   Year(date_) LIKE Year(GETDATE()) 
         AND MONTH(ShipementDate) LIKE Month(Getdate()) - 2
 Group by DeptCode
),

nbr_YTD_ AS
(
SELECT DISTINCT 
   Sum(s.[TotalCost]) AS 'YTD', 
   s.DeptCode, 
   s.DeptName
 FROM    [Shipements] s
         LEFT JOIN [Departements] d ON d.[DeptCode] = s.[DeptCode]
 WHERE    Year(ShipementDate) LIKE Year(GETDATE()) 
 Group by DeptCode
),

SELECT 
   A.DeptCode,
   A.DeptName,
   YTD,
   PTD
FROM   nbr_YTD_ A
       LEFT JOIN nbr_last2month_ B on B.DeptCode = A.DeptCode
ORDER BY DeptCode

Upvotes: 0

steve v
steve v

Reputation: 3540

Your expected output doesn't match 2 months, but here's the code to accomplish what you want. You just have to add a SUM(CASE...) on the 2nd condition.

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD,
    SUM(CASE WHEN s.ShipmentDate >= DATEADD(month, -2, GETDATE()) then s.TotalCost else 0 END) as PTD

FROM [Departments] AS d
INNER JOIN Shipments AS s
ON d.DeptCode= s.DeptCode
WHERE Year(s.ShipmentDate) = Year(GETDATE())
GROUP BY d.DeptCode, d.DeptName

Upvotes: 1

John Wu
John Wu

Reputation: 52270

Just add one more column that returns 0 when not in the two-month range, e.g. SUM(CASE WHEN (date check) THEN (amount) ELSE 0 END). Check out the fifth line:

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD,
    SUM(CASE WHEN DateDiff(MONTH, s.ShipmentDate, GetDate()) < 2 THEN s.TotalCost ELSE 0 END) PTD,
FROM [Departments] AS d
INNER JOIN Shipments AS s
ON d.DeptCode= s.DeptCode
WHERE s.ShipmentDate BETWEEN DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()), 0) 
AND GETDATE()
GROUP BY d.DeptCode, d.DeptName

Upvotes: 1

Related Questions