SQL Server SQL create substring to display the min and max date as a date range separated by a delimiter

cwdobuhd 于 2024-01-05 发布在其他

关注(0)|答案(4)|浏览(96)

I have got this query (simplified from a detailed query) to return dates as a substring. I want to return a substring which has just the minimum and maximum of the date if more than 1 date else return just the one date.

This is my SQL query:

SELECT 
    SUBSTRING((SELECT distinct ','+ r.ENTRY  AS [text()] 
               FROM RESULT r 
               WHERE r.ENTRY IN ('19-09-2023', '20-09-2023', '21-09-2023', '22-09-2023') 
               AND r.sample_number = 74406
               FOR XML PATH (''), TYPE ).value('text()[1]', 'nvarchar(max)'), 2, 1000) [Test Date]

This query returns:

19-09-2023,20-09-2023,21-09-2023,22-09-2023

However, I want just 19/09-22/09/2023 to be returned i.e. min to max date in the above format. If there is only one date then I just want that date returned.

How do I amend the query to get this? I do need it as a substring since this is just one of many other columns returned.

I tried to add a CASE expression to get the min and max when there are more than 1 r.ENTRY . That didn't work. I do not have any more ideas.

Thanks

sql-server

来源：https://stackoverflow.com/questions/77676882/sql-create-substring-to-display-the-min-and-max-date-as-a-date-range-separated-b

4条答案

按热度按时间

2w2cym1i1#

Since you are only interested in the minimum and maximum Date, you can use MIN() and MAX() together with CASE expression. No string aggregation is required

select case when min(ENTRY) <> max(ENTRY)
            then convert(varchar(10), min(ENTRY), 105)
            +    '-'
            +    convert(varchar(10), max(ENTRY), 105)
            else convert(varchar(10), min(ENTRY), 105)
            end
from   RESULT r

CONVERT() with style 105 will return string in dd-mm-yyyy format.

赞(0）回复(0）举报 2024-01-05

igsr9ssn2#

Are you looking for consecutive values of the entry column for a specific value of the sample_number column?

select @@version;
With
  result as (
    select *
    from (
      values
        (cast('20230919' as date), 74406),
        ('20230920', 74406),
        ('20230921', 74406),
        ('20230922', 74406),
        ('20230923', 74407),
        ('20230925', 74408),
        ('20230929', 74408)
    ) as t(entry, sample_number)
  ),
  t1 as (
    select
      *,
      dateadd(day,
              -dense_rank() over(partition by sample_number
                                 order by entry),
              entry) as gr
    from result
  )
select
  case
    when min(entry) = max(entry) then convert(nvarchar(10), min(entry), 103)
    else substring(convert(nvarchar(10), min(entry), 103), 1, 5) +
           '-' + convert(nvarchar(10), max(entry), 103)
  end as "Test Date"
  -- , sample_number
from t1
where
  sample_number = 74406 -- or 1 = 1
group by gr -- , sample_number;

Results:

+------------------+
|    Test Date     |
+------------------+
| 19/09-22/09/2023 |
+------------------+

DB Fiddle.

Results for all rows of test data:

+------------------+---------------+
|    Test Date     | sample_number |
+------------------+---------------+
| 19/09-22/09/2023 |         74406 |
| 23/09/2023       |         74407 |
| 25/09/2023       |         74408 |
| 29/09/2023       |         74408 |
+------------------+---------------+

DB Fiddle.

展开查看全部

赞(0）回复(0）举报 2024-01-05

brvekthn3#

A slightly more concise version of @Squirrel's excellent answer. You can use CONCAT_WS , which concatenates with a separator, and ignores nulls.

select
  concat_ws('-',
    min(r.ENTRY),
    case when min(r.ENTRY) <> max(r.ENTRY) then convert(varchar(10), max(r.ENTRY), 105) end
  ) as [Test Date]
from RESULT r;

赞(0）回复(0）举报 2024-01-05

hmtdttj44#

I have edited the suggested code to work for me. My field is not a date field so I had to use the replace and convert function to convert it to date and then the minimum and maximum works correctly. My working code as below:

`SELECT 
    SUBSTRING((SELECT distinct 
CASE when min(CONVERT(NVARCHAR(10),REPLACE(r.ENTRY,'-','/'),103)) = max(CONVERT(NVARCHAR(10),REPLACE(r.ENTRY,'-','/'),103)) THEN '-' + min(CONVERT(NVARCHAR(10),REPLACE(r.ENTRY,'-','/'),103)) ELSE
'-' + min(CONVERT(NVARCHAR(10),REPLACE(r.ENTRY,'-','/'),103)) + ' - ' + max(CONVERT(NVARCHAR(10),REPLACE(r.ENTRY,'-','/'),103)) END AS [text()] FROM RESULT r
               WHERE r.ENTRY IN ('19-09-2023', '20-09-2023', '21-09-2023', '22-09-2023') 
               AND r.sample_number = 74406
               FOR XML PATH (''), TYPE ).value('text()[1]', 'nvarchar(max)'), 2, 1000) [Test Date]`

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server SQL create substring to display the min and max date as a date range separated by a delimiter

4条答案

相关问题

热门标签

最新问答