sql - Using ColdFusion Loops to change database query filter -

sql - Using ColdFusion Loops to change database query filter -

i have table amounts, , created dates. want create query loops - , changes date within query. otherwise have manually.

current query:

<cfquery name=qweekly datasource="#dsn#"> select sum(amount) transactions ( createddate between '1-19-2014' , '1-25-2014' ) , ( amount > 0 ) </cfquery>

i manually alter filter ... (createddate between '1-19-22014' , '1-25-2014')..., manually alter 1 time again to: (createddate ...between '1-26-2014' , '2-8-2014').

what want between 'x' , 'x+7', 1 weeks worth of data, incremented 7 days, can generate output weekly date ranges. in psuedo code, this:

<cfif createddate < now()> <cfloop index="x" step="7"> <cfquery name=qweekly datasource="#dsn#"> select sum(amount) transactions ( createddate between 'x' , 'x+7' ) , ( amount > 0 ) </cfquery> </cfloop> </cfif>

is possible?

you did not mention dbms (always include sql questions) possible accomplish goal without looping. example, in sql server, utilize cte generate table of week start dates. bring together transactions table , aggregate amounts week.

sqlfiddle

<!--- example: generate range of 7 weeks ---> <cfset firstsunday = createdate(2014,1,19)> <cfset lastsunday = dateadd("ww", 7, firstsunday) ... <!--- calculate totals weeks in range ---> <cfquery name="gettotalsbyweek" ...> ;with ranges ( weekstartdate ) ( select <cfqueryparam value="#firstsunday#" cfsqltype="cf_sql_date"> weekstartdate union select dateadd(d, 7, weekstartdate) ranges <cfqueryparam value="#lastsunday#" cfsqltype="cf_sql_date"> > weekstartdate ) select r.weekstartdate, sum(t.amount) totalamount ranges r left bring together transactions t <!--- createddate falls within 7 days of start date ---> on t.createddate >= r.weekstartdate , t.createddate < dateadd(d, 8, r.weekstartdate) grouping r.weekstartdate order r.weekstartdate </cfquery>

results:

2014-01-19 00:00:00.000 | 1915.74 2014-01-26 00:00:00.000 | 567.00 2014-02-02 00:00:00.000 | 1250.00 2014-02-09 00:00:00.000 | null 2014-02-16 00:00:00.000 | 300.00 2014-02-23 00:00:00.000 | null 2014-03-02 00:00:00.000 | null 2014-03-09 00:00:00.000 | null

nb: query above uses special build date comparisons work regardless of whether createddate column contains date (only) or date , time.

col >= startdateatmidnight , col < dayafterenddateatmidnight

sql coldfusion