SQLTeam.com | Weblogs | Forums

Need SQL help


#1

I have the following table. I need to get a count of days, but I need to count id 1-7 as 2 days. So like if the person selected Sunday, Monday and Tueday, The count would be 6. The rest only count as 1, so if the person selected Sunday, Monday and Tueday, and Sunday - Week 1. The count would be 7

id name
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
8 Sunday - Week 1
15 Sunday - Week 2
9 Monday - Week 1
16 Monday - Week 2
10 Tuesday - Week 1
17 Tuesday - Week 2
11 Wednesday - Week 1
18 Wednesday - Week 2
12 Thursday - Week 1
19 Thursday - Week 2
13 Friday - Week 1
20 Friday - Week 2
14 Saturday - Week 1
21 Saturday - Week 2

#2

Please give example of input (user selected)


#3

Roughly along the lines of:

SELECT person_id, SUM(CASE WHEN id BETWEEN 1 AND 7 THEN 2 ELSE 1 END) AS day_count
FROM dbo.table_name
GROUP BY person_id