Get multiple columns with multiple in between statements in the where clause in SQL

Question

I want to get a column for every WHERE statement, now I have to do it manually for every statement to get the result, but how can i do it at once?

SELECT 
    COUNT([ParcelEventID]) as WEEK
FROM
    [Data].[CDB].[ParcelEvent] 
WHERE
    [EventDate] BETWEEN 20180820 AND 20180826 
    --[EventDate] BETWEEN 20180827 AND 20180902
    --[EventDate] BETWEEN 20180902 AND 20180909
    --[EventDate] BETWEEN 20180909 AND 20180916
    --[EventDate] BETWEEN 20180916 AND 20180923
    --[EventDate] BETWEEN 20180923 AND 20180930
    --[EventDate] BETWEEN 20181001 AND 20181007
    --[EventDate] BETWEEN 20181008 AND 20181014 
    --[EventDate] BETWEEN 20181014 AND 20181021 
    --[EventDate] BETWEEN 20181022 AND 20181028 )
    AND [Depot] LIKE '05[12]%'
    AND [ParcelEventTypeID] = 09

EDIT: Thanks for the answers, they work. However, the query runs very slow. Is it somehow possible to query something as the following as outcome?

Eventdate ParcelEventTypeID   Count     Week 
01/12            6              10       50
01/12            8              100      50
02/12            6              25       50
02/12            8              15       50

With something like this:

SELECT week
SUM(Count)
[ParcelEventTypeID]
,datepart(weekday, [EventDateTime]) As [WeekDay]
FROM [Data].[CDB].[ParcelEvent]
GROUP BY [week], [ParcelEventTypeID]

Then with the result something like this:

Week   ParcelEventTypeID      Count
1             6                 35
1             8                 115

Your end and start dates overlap, `20180827 AND 20180902` and `20180902 AND 20180909`, is this correct? Do you want one row with those counts as columns or one row peer week? — dnoeth, Jan 04 '19 at 14:13
Thanks for the comment, The overlapping doesnt really matter for now. Im trying to get a row for every week. — SaschaS, Jan 04 '19 at 14:19
If you want 1 row per week, the overlapping is quite important. Without it you simply group by the week number (or similar). — dnoeth, Jan 04 '19 at 16:32

score 0 · Answer 1 · answered Jan 04 '19 at 14:16

I want to get a column for every WHERE statement

You seem to be looking for conditional aggregation :

SELECT 
    SUM(CASE WHEN [EventDate] BETWEEN 20180820 AND 20180826 THEN 1 ELSE O END) AS 20180820_TO_20180826
    SUM(CASE WHEN [EventDate] BETWEEN 20180827 AND 20180902 THEN 1 ELSE 0 END) AS 20180827_TO_20180902
    ...
FROM
    [Data].[CDB].[ParcelEvent] 
WHERE
    [Depot] LIKE '05[12]%'
    AND [ParcelEventTypeID] = 09

score 0 · Accepted Answer · answered Jan 04 '19 at 14:16

You probably want conditional aggregation

SELECT 
    Count(CASE WHEN [EventDate] BETWEEN 20180820 AND 20180826 THEN [ParcelEventID] end) AS WEEK1
   ,Count(CASE WHEN [EventDate] BETWEEN 20180827 AND 20180902 THEN [ParcelEventID] end) AS WEEK2
   ,Count(CASE WHEN [EventDate] BETWEEN 20180902 AND 20180909 THEN [ParcelEventID] end) AS WEEK3
   ,...
FROM
    [DATA].[CDB].[ParcelEvent] 
WHERE [EventDate] BETWEEN 20180820 AND 20181028 
  AND [Depot] LIKE '05[12]%'
  AND [ParcelEventTypeID] = 09

What can I do to make it faster? – SaschaS Jan 07 '19 at 11:29 — SaschaS, Jan 07 '19 at 11:29

score 0 · Answer 3 · edited Jan 04 '19 at 15:28

SELECT 
case 
     when [EventDate] BETWEEN 20180820 AND 20180826 then count(ParcelEventID)
     else 0 
end as 20180820_20180826,
case
     when [EventDate] BETWEEN 20180827 AND 20180902 then count(ParcelEventID) 
     else 0 
end as 20180827_20180902,
case 
     when [EventDate] BETWEEN 20180902 AND 20180909 then count(ParcelEventID) 
     else 0
end as 20180827_20180902
FROM [Data].[CDB].[ParcelEvent]
WHERE [Depot] LIKE '05[12]%' AND [ParcelEventTypeID] = 09

SaschaS · Answer 4 · 2019-01-07T10:46:57.380

Thanks for the answers, they work. However, the query runs very slow. Is it somehow possible to query something as the following as outcome?

Eventdate ParcelEventTypeID   Count     Week 
01/12            6              10       50
01/12            8              100      50
02/12            6              25       50
02/12            8              15       50

With something like this:

SELECT week
SUM(Count)
[ParcelEventTypeID]
,datepart(weekday, [EventDateTime]) As [WeekDay]
FROM [Data].[CDB].[ParcelEvent]
GROUP BY [week], [ParcelEventTypeID]

Then with the result something like this:

Week   ParcelEventTypeID      Count
1             6                 35
1             8                 115

You can calulate the first day of a week, e.g. see https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server You can also try to improve performance using a two-step aggregation: first get the count per date in a CTE and calculate the week and the sum of those counts on top — dnoeth, Jan 07 '19 at 22:17

Get multiple columns with multiple in between statements in the where clause in SQL

4 Answers4