This content has been marked as final. Show 11 replies
We have a table called period. The primary key is date (just a date, not a datetime) and other fields are fiscal year, fiscal quarter, fiscal period, etc. We simply join to this table to get that info.
alternatively, you can determine the quarter with sql by using the keyword "case".
Hi Dan, thanks so much for your reply, it is so much clearer now, but I was wondering if you had a sample of your sql query to get that info? If a user clicks on Quarter2, would the primary key date joined to this? Sorry for my confusion.
Yes the primary key of our period table is also a foreign key in many other tables. Here is a sample:
from visit_fact join period on registration_date = date
where datepart(year, registration_date) = datepart(year, current_date)
and fiscal_quarter = 'FQ1';
In MS Sql, you can use the function DatePart(Q, myDateField) to determine the quarter:
SELECT user_id, update_date, DatePart(Q, update_date) as 'Quarter'
Hi Thank you Dan, for your comment, I am going to work extensively on this.
Hi Thank you Michael, I will try to integrate the DatePart function into my query. Thanks so much, and will reply when I have a solution.
Hi, I'm going to have a whole table of users and the latest date they updated their data. Then the audit team to have a page with quarterly-time buttons Q1,Q2,Q3,Q4. So for example, if audit clicks Q3, it will see all users who have not updated. I plan to do something like "Select * from userUpdates Where userLastUpdate < CurrentQuarterDate". I'm trying to come up with a query like this, any hints on how to do this? I'm also confused about what happens when next calendar year comes along?
Thank you so much for your help, I'll be working to hammer this out in the meantime,
Do users have to update every quarter?
Hi Michael, thanks for your reply. Yes, users have to update every quarter. Those who don't will be flagged for not updating in that quarter
You could do something like this:
1) Pass Quarter and Year to processing page
2) Put a switch statement on your processing page that does something like this:
<cfset dStart = "1/1/#URL.year# 00:00:00">
<cfset dEnd = "3/31/#URL.year# 23:59:59">
3) Use the dates in your SQL statement
Select * from userUpdate
Where lastupdate between '#dSTart#' and '#dEnd#'
Hi Michael, thank you so much for your reply, I will work at this, and reply back if there are any further questions, thanks so much again !