SQL query to find business days of the month

[Homerboy]

I need to run a query against the ACTIVE table to gather data group by each business day of the month.

So if there are 21 business days in the month, I will have 21 rows of data.
If there are 19 business days I will have 19 rows of data.

I will be doing a COUNT of data from the ACTIVE table and returning that count in each of the rows which is a business day.

Does that make sense? These things are so hard to describe in text...

 

[Homerboy]

Actually I think I figured out a way I can "cheat" at this.
Since entries to ACTIVE are only going to be entered on business days already, I think I can get away with just grouping by ACTIVE. TRANS_DATE

GROUP BY ACTIVE.TRANS_DATE
ORDER BY ACTIVE.TRANS_DATE

For the month of October, it seems to be just fine:

TRANS_DATE
2011-10-03
2011-10-04
2011-10-05
2011-10-06
2011-10-07
2011-10-10
2011-10-11
2011-10-12

 

[WannaFly]

That will work unless you have a business day where there are no rows, then it will not return a row, where you probably want to to return 0.

 

[Homerboy]

Correct, but if there was a business day that didn't have a row, we'd have some major issues around here 🙂

I did just realize a major problem though. Technically people work on Saturdays and those numbers are to be lumped into Friday's together (though we do not count Saturday as a "work day" so there is only 21 work days in October, not 26)

Long story short, I can't figure out a way to automatically add Friday and Saturday together since I'm just grouping by DATE.

 

[WannaFly]

adding
WHERE
DATENAME(dw, ACTIVE.TRANS_DATE) <> 'Saturday' should work

 

[Jeraden]

Override the trans_date on the saturday row, to make it look like its friday. Not sure of non-oracle syntax, but if I was doing it in Oracle..

instead of:
group by active.trans_date

I'd do:
group by decode(to_char(active.trans_date,'Day'),'Saturday',active.trans_date - 1,active.trans_date)
so basically on saturday, use the day before, if not saturday, just use the normal date

and use that in the order by and select clause as well instead of trans_date directly. I'm sure there is similar command for other databases.