I have a table with multiple mission with a pick and drop information the time and the name.

I have this data:

What I looking to do is to create a table to group by flow, in our example our flow are:

• Flow 1: all pick from A and D
• Flow 2: all pick from B and C

All of this grouped in 3 column using date. using our example it will look like this:

Here is what I looking:

1st select: first row of associated pick A and B

select * from table mission 
where pick = A or pick = D 
as the flow1 

select count(*) from flow1 
where date.hour < 11:01 this information as to be stored on the column morning 

Their is a way to get this result using SQL Server?

Thanks for your help

CodePudding user response：

You can use a SELECT CASE([PICK] = 'A' OR [PICK] = 'C', "FLOW 1", "FLOW 2") AS FLOW and then group by FLOW

CodePudding user response：

The following "sort of" gives your desired results - I'm not clear on your time classifications but you can amend these as appropriate.

Using Apply to calculate the intermediate required results (to save repeating conditions or requiring a sub-query/CTE):

select Flow, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, Count(*) Total
from t
cross apply (values (Iif(pick in ('A','D'),'Flow 1','Flow 2')))f(Flow)
cross apply (values ( Convert(time, [DATE] )))v(T)
cross apply (
    select
        case when T >='06:00:00' and T < '11:00:00' then 1 else 0 end Morning,
        case when T >='11:00:00' and T < '22:00:00' then 1 else 0 end PM,
        case when T >='22:00:00' and T < '06:00:00' then 1 else 0 end Night
)c
group by Flow

See Demo Fiddle