MS-Access：选择每个会计月的开始日期和日期

SQL newbie here. So I have a table that reads

SQL新手在这里。所以我有一张表可读

start_date  |  end_date   |   fiscal_month  | fiscal_year
------------| ------------|-----------------|-------------
1/1/2017    |  1/1/2017   |        1        |   2017
1/2/2017    |  1/8/2017   |        1        |   2017
1/9/2017    |  1/15/2017  |        1        |   2017
1/16/2017   |  1/22/2017  |        1        |   2017
1/23/2017   |  1/29/2017  |        1        |   2017
1/30/2017   |  2/5/2017   |        2        |   2017
2/6/2017    |  2/12/2017  |        2        |   2017
2/13/2017   |  2/19/2017  |        2        |   2017
2/20/2017   |  2/26/2017  |        2        |   2017
2/27/2017   |  3/5/2017   |        3        |   2017

...And so on for the year of 2017

......等2017年

I want to extract just the start and end dates for each fiscal month

我想提取每个会计月的开始和结束日期

I'm trying to write a query that'll output

我正在尝试编写一个输出的查询

start_date  |  end_date   
------------| ---------   
1/1/2017    |  1/29/2017  
1/30/2017   |  2/26/2017  
2/27/2017   |  3/26/2017

...And so on for the year.

......等一年。

I could grab the start and end dates for each month individually fairly easily with

我可以相当容易地抓住每个月的开始和结束日期

SELECT First(start_date), Last(end_date)
FROM fiscal_calendar
WHERE year=2017 AND fiscal_month=1

But I don't know how to get all of the months in a single query. I'm sure it's a simple solution for a SQL veteran, but I'm racking my brain. From my understanding, MS Access doesn't really have loops in their SQL language, right?

但我不知道如何在一个查询中获得所有这些月份。我确信这对于一个SQL退伍军人来说是一个简单的解决方案，但我正在绞尽脑汁。根据我的理解，MS Access在他们的SQL语言中并没有真正的循环，对吧？

1 个解决方案

#1

You should use min and max

你应该使用min和max

  SELECT min(start_date), max(end_date)
  FROM fiscal_calendar
  WHERE year=2017 AND fiscal_month=1

and for all the months

并且所有的月份

  SELECT fiscal_month, min(start_date), max(end_date)
  FROM fiscal_calendar
  WHERE year=2017 
  group by fiscal_month

and for a version with desired formatting!

以及具有所需格式的版本！

SELECT  min(start_date) as start_date, max(end_date) as end_date
  FROM fiscal_calendar 
  WHERE year=2017 
  GROUP BY fiscal_month

#1