Geek asks and answers

Sale on top and bottom of each month

If I have data such as:

sale_id     sale_date
      1      1/5/2010
      2      1/8/2010
      3      1/16/2010
      4      1/28/2010
      5      2/2/2010
      6      2/21/2010
      7      2/29/2010
      8      3/3/2010

I want the first and last month (2 or less entries per month):

desired result:

sale_id     sale_date
      1      1/5/2010
      4      1/28/2010
      5      2/2/2010
      7      2/29/2010
      8      3/3/2010

One way that I think is slow is to:

select * from table o
where sale_date in (select max(sale_date) from table where datepart(month+year of sale_date) = datepart(month+year of o.sale_date), select min(sale_date) from table where ... )
+3
source share
5 answers
WITH T
     AS (SELECT sale_id,
                sale_date,
                ROW_NUMBER() OVER (PARTITION BY Year(sale_date), MONTH(sale_date)
                ORDER BY sale_date ASC)  RN,
                ROW_NUMBER() OVER (PARTITION BY Year(sale_date), MONTH(sale_date)
                ORDER BY sale_date DESC) RN2
         FROM   YourTable)
SELECT sale_id,
       sale_date
FROM   T
WHERE  RN = 1
        OR RN2 = 1
+6
source

which will run on SQl Server 2000 and higher

CREATE TABLE #temp(sale_id INT,    sale_date datetime)

INSERT #temp VALUES(      1 ,     '1/5/2010')
INSERT #temp VALUES(      2 ,     '1/8/2010')
INSERT #temp VALUES(      3 ,     '1/16/2010')
INSERT #temp VALUES(      4 ,     '1/28/2010')
INSERT #temp VALUES(      5 ,     '2/2/2010')
INSERT #temp VALUES(      6 ,     '2/21/2010')
 INSERT #temp VALUES(     7 ,     '2/28/2010')
 INSERT #temp VALUES(     8  ,    '3/3/2010')


 SELECT t.* FROM #temp t
 JOIN(
 SELECT MIN(sale_date) AS MinDAte, MAX(sale_date) AS MaxDate
FROM #temp
GROUP BY YEAR(sale_date), MONTH(sale_date)) x ON t.sale_date = x.MaxDate
OR t.sale_date = x.MinDAte

for 2005 and ... a small modification according to Martin's code (see where clause)

WITH T
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Year(sale_date), MONTH(sale_date)
                ORDER BY sale_date)  RN,
                ROW_NUMBER() OVER (PARTITION BY Year(sale_date), MONTH(sale_date)
                ORDER BY sale_date DESC) RN2
         FROM   #temp)
SELECT sale_id,
       sale_date
FROM   T
WHERE  1 IN(RN ,RN2)
+3
source 

select sale_ID, sale_date
from table inner join (
    select 
        datepart(yyyy,sale_Date) as yr,
        datepart(mm,sale_Date) as month, 
        min(sale_date) as min_date, 
        max(sale_date) as max_date
    from table
    group by
        datepart(yyyy,sale_Date),
        datepart(mm,saleDate)
) mx 
where sale_date = min_date or sale_date = max_date
+2

( SQL Server), , - :

select min(_date) AS minDate, max(_date) as maxDate, _month, _year FROM (
  select 
  datepart(date,sale_date) AS _date,
  datepart(month,sale_date) AS _month,
  datepart(year,sale_date) AS _year,
  FROM sales -- or whatever you have named your table
)
GROUP BY _month, _year
ORDER BY _month,_year
0

"" " " Script, .

Union , .

SELECT *
FROM Sales
WHERE SalesID IN 
(
SELECT TOP 1 SalesID
FROM Sales
ORDER BY SaleslID
)
OR
SalesID IN 
(
SELECT TOP 1 SalesID
FROM Sales
ORDER BY SalesID DESC
)GO
0

More articles:


All Articles