Quarterly action on records

[coldfuse228]

Hi, thank you for looking at this question, I have an application that has normal users that update data and admin users. Every 3 months (Quarterly), the admin checks to see which users have not updated their records. In the admin interface, there would be buttons like "Quarter1", "Quarter2", "Quarter3", "Quarter4" for given year (now it would be 2007). I am using coldfusion MX7 and sql server 2000.

Do you have an idea what is the best (possibly simple) approach to this, given the fact that I'm not that experienced in sql coding or coldfusion ?

Any help is very appreciated, thanks in advance !
C

[Dan_Bracuk]

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".

[coldfuse228]

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.

Thank you,
Carol

[Dan_Bracuk]

Yes the primary key of our period table is also a foreign key in many other tables. Here is a sample:

select count(registration_number)
from visit_fact join period on registration_date = date
where datepart(year, registration_date) = datepart(year, current_date)
and fiscal_quarter = 'FQ1';

[insuractive]

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'
FROM myTable

[coldfuse228]

Hi Thank you Dan, for your comment, I am going to work extensively on this.

-C

[coldfuse228]

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.

-C

[coldfuse228]

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,
C

[coldfuse228]

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

Thank you,
C

[insuractive]

Do users have to update every quarter?

[insuractive]

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:

<cfswitch expression="#URL.quarter#">
<cfcase value="1">
<cfset dStart = "1/1/#URL.year# 00:00:00">
<cfset dEnd = "3/31/#URL.year# 23:59:59">
</cfcase>
</cfswitch>

3) Use the dates in your SQL statement
Select * from userUpdate
Where lastupdate between '#dSTart#' and '#dEnd#'

[coldfuse228]

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 !

-C