Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If Day of the Week='Y' (i.e. Mon='Y') and between a Start Date and End Date then Count #of Days 1

Status
Not open for further replies.

kernal

Technical User
Feb 27, 2001
415
US
I'm using Oracle 11. I have thousands of records so this is just a quick example:

Table

Nbr Mon Tues Wed Thurs Fri Sat Sun Start_Date End_Date
1 Y N Y N Y N Y 12/2/2019 12/14/2019 (Mon, Wed, Fri and Sun have 'Y')
2 N N Y Y N N N 12/4/2019 12/5/2019 (Wed and Thurs have 'Y')

Result needed:
Nbr Nbr_of_days
1 7
2 2

Help is very appreciated! Thank you!
 
Code:
SELECT Nbr,
           CASE WHEN Mon = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Tue = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Wed = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Thurs = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Fri = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Sat = 'Y' THEN 1 ELSE 0 END
         + CASE WHEN Sun = 'Y' THEN 1 ELSE 0 END    Nbr_of_days
    FROM My_table
ORDER BY Nbr;

Bill
Lead Application Developer
New York State, USA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top