I have a question about an SQL query.

I have a table with these column names:
date time route_id name

(and other columns not included here):

I have made the following query:

Select
date,
LEFT(route_id_intern,4) as route_id,
CASE 
       WHEN time < '12:00:00' THEN 'Morning' 
       ELSE 'Free' END as 'Morning',
CASE 
       WHEN time > '12:00:00' THEN 'Afternoon' 
       ELSE 'Free' END as 'Afternoon',
Name,
FROM [DW2].[dbo].[FCT_RITTEN]
where year(date) = 2022 and month(date)=02 and day(date) = 04 
and LEFT(route_id_intern,4) = 3209

My query gives the following result:

The data stays separated in two lines but I would like to have following result:

I have tried several methods but I keep getting these separated lines.

Please note the used data is anonymized for the data in the above samples but the problem stays the same.

Update:

After the reply of @HoneyBadger, I have amended my query:

Select
date,
LEFT(route_id_intern,3) as route_id,
MAX(CASE 
       WHEN time <= '12:00:00' THEN '1' 
       ELSE '0' END) as 'Morning',
MAX(CASE 
       WHEN time > '12:00:00' THEN '1' 
       ELSE '0' END) as 'Afternoon'
FROM [DW2].[dbo].[FCT_RITTEN]
where date = '2022-02-04'
and LEFT(route_id_intern,4) = 320
group by date, route_id_intern

Unfortunately, the result is still not as needed:

CodePudding user response：

One the challenges here is that your sample data and query don't line up. There are columns referenced in your query not in your sample data. I think this is likely because you obfuscated the real information so much that some details got lost. I made some best guesses and I think that what you need is conditional aggregation. At the very least this will return the desired output you wanted based (mostly) on the sample provided.

create table FCT_RITTEN
(
    MyDate date
    , route_id int
    , MyTime time
    , Name varchar(10)
)

insert FCT_RITTEN
select '20220204', 320, '11:40:00', 'Taxi' union all
select '20220204', 320, '14:35:00', 'Taxi'

Select
    MyDate
    , route_id
    , Morning = max(CASE WHEN MyTime < '12:00:00' THEN 'Morning' END)
    , Afternoon = max(CASE WHEN MyTime > '12:00:00' THEN 'Afternoon' END)
    , Name
FROM [dbo].[FCT_RITTEN]
where MyDate = '20220204'
    and route_id like '320%' --Why are you doing a string comparison on an integer column?
group by MyDate
    , route_id
    , Name

CodePudding user response：

You are almost there I guess. Removing the group by and putting your query inside a cte and then playing with the time column a bit as below will give you the desired result.

;with cte as(
Select
date,
LEFT(route_id_intern,4) as route_id,
CASE 
   WHEN max(time)over(partition by date,name order by date) > '12:00:00' 
   and count(1)over(partition by date,name order by date) > 1 
   THEN 'Morning-Afternoon'
   WHEN max(time)over(partition by date,name order by date) = '12:00:00' 
   and count(1)over(partition by date,name order by date) > 1 
   THEN 'Morning-Lunch'
   when max(time)over(partition by date,name order by date) > '12:00:00' 
   and count(1)over(partition by date,name order by date) = 1
   then 'Free-Afternoon'
   when max(time)over(partition by date,name order by date) < '12:00:00' 
   and count(1)over(partition by date,name order by date) = 1
   then 'Morning-Free'
   END as 'day',
Name,
FROM [DW2].[dbo].[FCT_RITTEN]
where year(date) = 2022 and month(date)=02 and day(date) = 04 
and LEFT(route_id_intern,4) = 3209
)
select distinct Date,route_id
,SUBSTRING([day],1,charindex('-',[day])-1) as [Morning]
,SUBSTRING([day],charindex('-',[day]) 1,len([day])) as [Afternoon]
,name
 from cte