SQL Help

[php_dave]

Hey guys,

 

This one is eluding me - i have a query which returns grouped date info

 

1/01/2007      120

2/02/2007      300

 

Etc...

 

Now this grows every day what I want to do is make it show only the last 7 days and the sum of everything more than 7days old..  so:

 

<7 days        4,302

5/03/2008      213

6/03/2008      3

7/03/2008      124

 

etc..

 

How do I get the <7 days old column into my query - if iwas to psuedo it i guess it would be

 

if (sop_received_date < curdate() - 7) then sum(count(sop_response.sop_res_id) else (count(sop_response.sop_res_id)

 

Here is the base query to return all dates..

 

Select
sop_response.SOP_RECIEVED_DATE,
Count(sop_response.SOP_RES_ID) AS replies
From
sop_response
Left Join service_order_command ON sop_response.SOP_RES_SOC_ID = service_order_command.SOC_ID
Left Join order_types ON service_order_command.SOC_COM_REF_ID = order_types.order_id
Where
sop_response.SOP_RECIEVED_DATE < curdate() AND
sop_response.SOP_STATUS = 'Received'
Group By
sop_response.SOP_RECIEVED_DATE
Order By
sop_response.SOP_RECIEVED_DATE Asc

 

Thanks for any pointers

 

Dave

 

 

[fenway]

What you'll need to do is "change" the rec date for <7 days using an IF() statement, and then group by this column.

[php_dave]

Bang on mate!!

 

The magic query

 

Select
IF(wlr_prov.sop_response.SOP_RECIEVED_DATE < curdate()-7, 'Older Than 7 Days',wlr_prov.sop_response.SOP_RECIEVED_DATE) AS Reply_Date,
IF(wlr_prov.sop_response.SOP_RECIEVED_DATE < curdate()-7, Count(wlr_prov.sop_response.SOP_RES_ID),Count(wlr_prov.sop_response.SOP_RES_ID)) AS replies
From wlr_prov.sop_response
Left Join wlr_prov.service_order_command ON wlr_prov.sop_response.SOP_RES_SOC_ID = wlr_prov.service_order_command.SOC_ID
Left Join wlr_prov.order_types ON wlr_prov.service_order_command.SOC_COM_REF_ID = wlr_prov.order_types.order_id
Where wlr_prov.sop_response.SOP_RECIEVED_DATE < curdate() AND
wlr_prov.sop_response.SOP_STATUS = 'Received'
Group By	Reply_Date
Order By	wlr_prov.sop_response.SOP_RECIEVED_DATE Asc

 

Thanks Fenway!

 

Dave

[fenway]

No problem... but why the second if?

[php_dave]

I orignally thought i had to sum the <7s - then realised it was just a count - didnt remove the IF is all.