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?
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.
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
;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
;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
- Proposed as answer by Naomi NModerator 5 hours 15 minutes ago
Thank You Jingyang
appreciate that. it's working
Thnak you again
Malav Patel