CODEWITHSUNDEEP
sqldb2
goldenbutter
goldenbutter

Reputation: 589

How to get 1st Business day in DB2

I have below requirements like, below query should run on 3 conditions, daily, weekly and monthly.

daily rows should give output everyday, weekly should give output on only Saturday. And Monthly should give output on 1st Business day but if 1st is Saturday then output will come 3rd day, if 1st is Sunday then output should come on 2nd day.

select * from Tablename is giving me below 4 rows

Status  ID   COL1  COL2  
= = = = = = = = = = = = 
Daily   12    100  150.2
Daily   17     90  120.6
Weekly  24    180  50.6
Monthly 25    150  155.2

Now I want to show this data as per above requirements

I got my exact output for daily and Monthly by below SQL but need help on Monthly

Select * from Tablename
where
ID in (12,17) OR --daily
(DAYNAME(CURRENT DATE) = 'Saturday' AND ID = 24) --weekly
(TO_CHAR(DATE(CURRENT DATE)) = '01' AND ID = 25 AND DAYNAME(CURRENT DATE) NOT IN ('Saturday','Sunday')) --monthly

But my Monthly logic not giving me expected output.

Upvotes: 0

Views: 327

Answers (1)

Ian
Ian

Reputation: 114

The first thing I noticed is that there is a missing OR at the end of the Weekly line - I assume that's just a typo.

I think the 'monthly' line needs a bit more complexity though. Try something like:-

Select * from Tablename
where
ID in (12,17) OR --daily
(DAYNAME(CURRENT DATE) = 'Saturday' AND ID = 24) OR --weekly
( ID = 25 AND 
    (
        (   TO_CHAR(DATE(CURRENT DATE)) = '01' AND 
            (DAYNAME(CURRENT DATE) NOT IN ('Saturday','Sunday'))
        )
        OR( TO_CHAR(DATE(CURRENT DATE)) IN ( '02', '03') AND 
            (DAYNAME(CURRENT DATE) = 'Monday'))
    )
) --monthly

Upvotes: 1

Related Questions