CODEWITHSUNDEEP
sqlsql-servergroup-bydifference
chrisdeb
chrisdeb

Reputation: 47

SQL statement to get the difference between two dates

I have a query which basically is grouping the total sum by day 

SELECT CountDate, SUM(Max_Count) as MaximumCount, SUM(Min_Count) as MinimumCount
FROM countTable
WHERE countId IN ('48', '34', '65', '63', '31', '64', '86') 
AND CountDate BETWEEN '2014-08-14' AND '2014-08-16' 
GROUP BY CountDate 
ORDER BY CountDate

The output result will be

 Date       | Maximum   |  Minimum
------------|-----------|----------------------
2014-08-14  |  3018234  |  3014212
2014-08-15  |  3023049  |  3018510
2014-08-16  |  3026813  |  3023244

I want the query to get the difference between the MaximumCount of the last day and the MinimumCount of the first day.

The result of the query should be the maximum of the last day i.e. 2014-08-16 : 3026813 minus (-) the minimum of the first day i.e. 2014-08-14 | 3014212. Therefore 3026813 - 3014212

Any help how I could achieve this will be much appreciated.

Upvotes: 0

Views: 920

Answers (3)

chrisdeb
chrisdeb

Reputation: 47

With reference to Jithin Shaji answer, I've got the result by this query

DECLARE @STARTDATE DATE = '2014-08-14'
DECLARE @ENDDATE DATE = '2014-08-16'
DECLARE @NOOFDAYS INT = datediff(day, @STARTDATE, @ENDDATE)

 SELECT  A.CountDate,
    A.MaximumCount - B.MinimumCount AS CountSum
 FROM    (
 SELECT  CountDate, 
    SUM(Max_Count) AS MaximumCount, 
    SUM(Min_Count) AS MinimumCount
 FROM    countTable
 WHERE   countId IN ('48','34','65','63','31','64','86') 
 AND     CountDate BETWEEN @STARTDATE AND  @ENDDATE
 GROUP   BY CountDate) A
 LEFT JOIN (
 SELECT  DATEADD(DAY, @NOOFDAYS, CountDate) AS CountDate,
    SUM(Max_Count) AS MaximumCount, 
    SUM(Min_Count) AS MinimumCount
 FROM    countTable
 WHERE   countId IN ('48','34','65','63','31','64','86') 
 AND     CountDate BETWEEN @STARTDATE AND @ENDDATE 
 GROUP   BY DATEADD(DAY, @NOOFDAYS, CountDate)) B

 ON      A.CountDate = B.CountDate

Upvotes: 0

Jithin Shaji
Jithin Shaji

Reputation: 6073

I think this will be correct.

DECLARE @STARTDATE DATE = '2014-08-14'
DECLARE @ENDDATE DATE = '2014-08-16'

SELECT  A.CountDate,
        B.MaximumCount - A.MinimumCount
FROM    (
SELECT  CountDate, 
        SUM(Max_Count) AS MaximumCount, 
        SUM(Min_Count) AS MinimumCount,
FROM    countTable
WHERE   countId IN ('48','34','65','63','31','64','86') 
AND     CountDate BETWEEN @STARTDATE AND  @ENDDATE
GROUP   BY CountDate) A
LEFT JOIN (
SELECT  DATEADD(DAY,CountDate,1) CountDate
        SUM(Max_Count) AS MaximumCount, 
        SUM(Min_Count) AS MinimumCount,
FROM    countTable
WHERE   countId IN ('48','34','65','63','31','64','86') 
AND     CountDate BETWEEN BETWEEN @STARTDATE AND  @ENDDATE 
GROUP   BY DATEADD(DAY,CountDate,1)) B

ON      A.CountDate = B.CountDate

Upvotes: 0

PhillipH
PhillipH

Reputation: 6222

SELECT 
 (SELECT [Maximum] FROM TABLE WHERE Date = (SELECT MAX(Date) FROM TABLE)) -
 (SELECT [Minimum] FROM TABLE WHERE Date = (SELECT MIN(Date) FROM TABLE))
FROM TABLE

Upvotes: 1

Related Questions