SQL QUERY FOR MAX SALES AND MIN SALES FOR EVERY YEAR WITH DATE
IN MS Sql server 2008
My Table Contains these Data

SalesDate    TaxableSales    NonTaxableSales    Total Sales
            
01/01/2012    1211.67             139.68            1351.35
02/01/2012    1630.16             87.3                1717.46
30/06/2012    2831.48             156.07            2987.55
01/07/2012    2336.63             122.82            2459.45
30/11/2012    2342.72             271.22            2613.94
            
01/01/2013    1172.53             65.19              1237.72
02/01/2013    1669.83             145.73            1815.56
27/02/2013    2071.84             182.94            2254.78
01/03/2013    2928.38             282.76            3211.14
            
06/06/2014    4578.8             262.56              4841.36
09/06/2014    2033.98             215.99            2249.97
25/12/2014    1094.1             115.12              1209.22
26/12/2014    2222.49             129.51            2352.00

I need Following Result

Year    Max Sales    Max Sale Date    Min Sales    Min Sale Date

2014    4841.36            06/06/2014    1209.22            25/12/2014
2013    3211.14            01/03/2013    1237.72            01/01/2013
2012    2987.55            30/06/2012    1351.35            01/01/2012

I tried this one:

Select   DATEPART(yy,salesdate)AS Years, max(Taxablesales + nontaxablesales ) as MaxSale,
Min(Taxablesales + nontaxablesales ) as MinSale,
(Select Top(1) SalesDate from Sales where (TaxableSales+ NonTaxableSales) = (Select Max(TaxableSales + Nontaxablesales) from Sales where DATEPART(yy, salesdate) = 2013)) AS Max_Date,
(Select Top(1) SalesDate from Sales where (TaxableSales+ NonTaxableSales) = (Select Min(TaxableSales + Nontaxablesales) from Sales where DATEPART(yy, salesdate) = 2013)) AS MIn_Date


from Sales group by DATEPART(yy, salesdate) order by Years desc

But it does not work. can anybody help me please?
September 11th, 2015 3:49am

Hi MALAV,

To get the expected output, please see below sample.

CREATE TABLE Sales
( SalesDate DATE,
  TaxableSales MONEY,
  NonTaxableSales MONEY,
  [Total Sales] MONEY
  );


SET DATEFORMAT DMY
INSERT INTO Sales
VALUES
('01/01/2012',    1211.67,  139.68 ,  1351.35),
('02/01/2012',    1630.16 , 87.3   ,  1717.46),
('30/06/2012',    2831.48 , 156.07 ,  2987.55),
('01/07/2012',    2336.63 , 122.82 ,  2459.45),
('30/11/2012',    2342.72 , 271.22 ,  2613.94), 
('01/01/2013',    1172.53 , 65.19  ,  1237.72),
('02/01/2013',    1669.83 , 145.73 ,  1815.56),
('27/02/2013',    2071.84 , 182.94 ,  2254.78),
('01/03/2013',    2928.38 , 282.76 ,  3211.14),  
('06/06/2014',    4578.8  , 262.56  , 4841.36),
('09/06/2014',    2033.98 , 215.99 ,  2249.97),
('25/12/2014',    1094.1  , 115.12  , 1209.22),
('26/12/2014',   2222.49  , 129.51  , 2352.00);
 
;WITH Cte AS
(
SELECT YEAR(SalesDate) [YEAR],*,
	  ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY [Total Sales] ASC) RN1,
	  ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY [Total Sales] DESC) RN2
	  FROM Sales
)
SELECT [Year],
	   MAX(CASE WHEN RN2=1 THEN [Total Sales] END) MaxTotalSale,
	   MAX(CASE WHEN RN2=1 THEN SalesDate END) MaxSalesDate,
	   MAX(CASE WHEN RN1=1 THEN [Total Sales] END) MinTotalSale,
	   MAX(CASE WHEN RN1=1 THEN SalesDate END) MinSalesDate
 FROM CTE WHERE RN1=1 OR RN2=1
GROUP BY [YEAR] ORDER BY [YEAR] DESC 

If you have any question, feel free to let me know.

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 4:50am

Hey Eric

Thank you very much for reply back, appreciate that

 Here I have a small problem. in database I don't have [Total Sales] Column. that i just showed only for reference.  in fact I Have only three columns called Salesdate, TaxalebleSales and NonTaxableSales.

 I tried your query and made a bit changes there

;WITH Cte AS
(
SELECT YEAR(SalesDate) [YEAR],*,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY [TAXABLESALES + NONTAXABLESALES] ASC) RN1,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY [TAXABLESALES + NONTAXABLESALES] DESC) RN2
      FROM Sales
)
SELECT [Year],
       MAX(CASE WHEN RN2=1 THEN [TAXABLESALES + NONTAXABLESALES]END) MaxTotalSale,
       MAX(CASE WHEN RN2=1 THEN SalesDate END) MaxSalesDate,
       MAX(CASE WHEN RN1=1 THEN [TAXABLESALES + NONTAXABLESALES]END) MinTotalSale,
       MAX(CASE WHEN RN1=1 THEN SalesDate END) MinSalesDate
 FROM CTE WHERE RN1=1 OR RN2=1
GROUP BY [YEAR] ORDER BY [YEAR] DESC

I make a small changes, wrote Taxablesales + nontaxablesales instaed of [total sales] but it says me invalid column name Taxablesales + nontaxablesales

Can you help me out please?

Thank You

Malav Patel

September 12th, 2015 1:34am

;WITH Cte AS
(
SELECT YEAR(SalesDate) [YEAR],*,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY TAXABLESALES + NONTAXABLESALES ASC) RN1,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY TAXABLESALES + NONTAXABLESALES DESC) RN2
      FROM Sales
)
SELECT [Year],
       MAX(CASE WHEN RN2=1 THEN TAXABLESALES + NONTAXABLESALES END) MaxTotalSale,
       MAX(CASE WHEN RN2=1 THEN SalesDate END) MaxSalesDate,
       MAX(CASE WHEN RN1=1 THEN TAXABLESALES + NONTAXABLESALES END) MinTotalSale,
       MAX(CASE WHEN RN1=1 THEN SalesDate END) MinSalesDate
 FROM CTE WHERE RN1=1 OR RN2=1
GROUP BY [YEAR] ORDER BY [YEAR] DESC

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 2:37am

;WITH Cte AS
(
SELECT YEAR(SalesDate) [YEAR],*,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY TAXABLESALES + NONTAXABLESALES ASC) RN1,
      ROW_NUMBER() OVER(PARTITION BY YEAR(SalesDate) ORDER BY TAXABLESALES + NONTAXABLESALES DESC) RN2
      FROM Sales
)
SELECT [Year],
       MAX(CASE WHEN RN2=1 THEN TAXABLESALES + NONTAXABLESALES END) MaxTotalSale,
       MAX(CASE WHEN RN2=1 THEN SalesDate END) MaxSalesDate,
       MAX(CASE WHEN RN1=1 THEN TAXABLESALES + NONTAXABLESALES END) MinTotalSale,
       MAX(CASE WHEN RN1=1 THEN SalesDate END) MinSalesDate
 FROM CTE WHERE RN1=1 OR RN2=1
GROUP BY [YEAR] ORDER BY [YEAR] DESC

September 12th, 2015 6:36am

Thank You Jingyang

appreciate that. it's working

Thnak you again

Malav Patel

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 9:42am

Your problem was in the wrong usage of []. You should use [] for individual column names and you don't have to use them at all if the column names don't contain spaces or special characters. That's why when Jingyang removed them your code worked.
September 12th, 2015 10:28pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics