BAQ CTE Incrementing Question

Kinetic ERP
1

Hello, I am trying to convert a sql query into a BAQ. In this SQL query there are calculating invoice totals by week, but the weeks are not the standard 7 day week, its based on 8 days. So, starting at 1, if the day of the month is 8, 16, 24, or the last day of the month the week count should go up by one.

I was think I could use a CTE to achieve this, but I’m having some issues, here is what I tried.

I created a CTE query with InvcHed and my calculated field. From InvcHed I just brought in Invoice Number, Invoice Date, and my calculated field. My calculated field is “WeekNum” and it starts with a static value of 1.

Then I created a UnionAll query. Brought in InvcHed (alias InvcHed1) and my CTE query, linking them by Invoice number. Put the Invoice Number, Invoice Date, and a new calculate field. My calculated field is referencing my calculated field from the CTE then a + 1.

Create a TopLevel query and bring in the CTE and show all the fields and my week number never increments. It just says 1 on every row. Figured I should get this before trying the week calculation with it.

Clearly I’m missing something and any advice would be appreciated. Thanks!

2

Wouldn’t it just be easier to divide the day by 8 then round up in a calculated field?

image
image709×190 10.6 KB

So your calculated field would look like this.

(CEILING(CONVERT(DECIMAL,DATEPART(DAY, @mydate))/8))
3

Let me try that, I got stuck on the conversion aspect and didn’t think of this approach. Thanks!

4

Rounding up won’t increment even multiples of 8.

cast(datepart(day, <date>) / 8 as int) + 1

What’s happening there - Casting a decimal to integer truncates the decimal.
So if it’s the 7th, dividing by 8 produces 0.875, casting that to int truncates it to 0, add 1 and you’ve got your week (where week count starts at one).

5

Seems to work when I test it.

image
image729×258 10.9 KB

image

6

The part I’m having trouble with is going across months. I’m doing this for an entire year, so your example is only going to group stuff into either week 1, 2, 3, or 4. I need to increment through out the entire year. So I’m thinking some date diff calc is probably where I would need to go, but then how does that handle short months like Feb?

7

So you want to count up weeks, but only have 4 weeks per month, regardless of how many days are in the month?

I guess I would just continue with the math. If every month has 4 “weeks”, then just add (month number-1)*4 to the week number that you get.

((DatePart(month,@myDate) -1)*4)+
(CEILING(CONVERT(DECIMAL,DATEPART(DAY, @mydate))/8))

image
image955×218 9.16 KB

or are you just looking for the weeknumber by year, split up into 8 day segments?

If that’s the case, you can use the dy to get the day number of the year, and do the same with ceiling and divide by 8.
image

8

I think the part I’m struggling with is that the last week of the month, which would be the week between the 24th and the last day of the month isn’t always 8 days. The logic in the SQL query I’m tasked with creating in a BAQ uses logic that increments the week number based on the day of the month, which is in increments of 8, except for the last week of the month which can be less 24th to the last day of month).

for example this January
1st - 8th week one (eight days)
9th - 16th week two (eight days)
17th - 24th week three (eight days)
25th - 31st week four (seven days)

since the last day of the month is the 31st it triggers the week number to increment 1. Then this logic is repeated throughout the year. the next month would start with week 5 going form the Feb 1st to the 8th. For the last “week” in this month is the 25th to the 28th a four day week.

my thought with the CTE was that I could have the results in order of invoice date and increment through an IF statement.

9

So what is Feb 1st? Week 5?

why doesn’t this work?

((DatePart(month,@myDate) -1)*4)+(CEILING(CONVERT(DECIMAL,DATEPART(DAY, @mydate))/8))

Why do you think you need a CTE? CTEs are necessary when a record DEPENDS on the preceding record. Like in a BOM, I know it’s level 3 BECAUSE the previous one was level 2. Without the previous record, I don’t know what level this record is.

In your case though, it’s purely a single record, and the week number based on a calendar. If I have no records in week 6, there can still be records in week 7. It has nothing to do with the previous records, so why would you need a recursive query?

This part gets you the previous number of weeks in months before (assuming each month is 4 weeks)

((DatePart(month,@myDate) -1)*4)

And this part gets you what week it is in the current month.

(CEILING(CONVERT(DECIMAL,DATEPART(DAY, @mydate))/8))

So you add the 2 together, and you get the current week.

date 4-10-2025
Month part
month = 4
4-1 = 3
3*4 = 12

So you have 12 weeks for Jan, Feb, and March. Regardless of how many days are in the last week.

day part
day = 10
10/8 = 1.25
ceiling = 2

so this is week 2 in month 4

2+12 = 14.

That date is in week 14.

What isn’t right there?

10

I tested it today and it looks like that does work. I was just mentioning the CTE because that is what I was handed, so it was my jumping off point. my response was more in answer to your question about if I wanted evenly split up 8 day weeks. I wasn’t trying to dispute your answer.

Thanks for the help its appreciated.

1 Like
11

No worries. Lots of nuance is lost in text communication.

Out of morbid curiosity, I wouldn’t mind see the original SQL that you were basing this off of.

1 Like