在php中查询SQL数据库 - Datediff？

Question

I am building a sales dashboard and have run into a small problem which I am reasonably sure has a simple solution, but I can't quite get there.

So I have a SQL database built in SQL Server 2008. In terms of this database, I have no issues drilling down into the information to get sales relevant data. There is one exception though; Revenue which reoccurs each month. The application which writes to the database (Connectwise) writes reoccurring revenue as a single entry controlled with a datetime field for start and end dates.

Currently I have the sales dashboard getting information from the other fields, but I haven't been able to get it to actually hook in with the datetime constraint correctly. I am using php to query the database with the two ? fields being a user defined start and end date.See below :

$sql = "select Owner_Level.Description as 'Location', v_rpt_Opportunity_SalesDash.Sales_Rep1 as 'Member', (select sum(v_Quota.GM) from v_Quota where v_Quota.Forecast_Year = ? and v_Quota.Forecast_Month between ? and ? and v_Quota.Member_RecID = v_rpt_Opportunity_SalesDash.MemberRec) as 'Quota',
sum(case when v_rpt_Opportunity_SalesDash.Sales_Stage = 11 then (ISNULL(SO_Forecast_Dtl.Revenue,0) -  ISNULL(SO_Forecast_Dtl.Cost,0) + (ISNULL(SO_Forecast_Dtl.Recurring_Revenue*(Nbr_Cycles/12),0)) -  ISNULL(SO_Forecast_Dtl.Recurring_Cost,0)) else 0 end) as 'Invoiced',

I was reading about the Datediff command but have tried to input it into this to no avail. If anyone can offer any assistance or advice, I really couldn't tell you how much I would appreciate it.

Sample data to demonstrate the idea of the dates as one entry. For example, this would constitute 52 reoccurring instances:

SO_Forecast_Dtl_RecID: 1 Opportunity_RecID: 1 Description: Opportunity1 Revenue: 1000 Cost: 500 SO_Opp_Status_RecID: 42 Include_Flag: 1 QuoteWerks_DocNO: 3938 QuoteWerks_DocName: 1223 Updated_By: User Last_Update: 18/11/2013 SO_Forecast_Type_ID: 1 Link_Flag:1 Recurring_Revenue: 100 Recurring_Cost: 50 Recurring_Date_Start: 01/11/2013 Recurring_Date_End 01/11/2014 Bill_Cycle_RecID:1 (Cross referenced with a weeks setup) Cycle_Basis_ID: 1 Recurring_Flag: 1 Product_Flag: 1 Mobile_Guid:

Answer 1

Something like this

select Owner_Level.Description as 'Location', v_rpt_Opportunity_SalesDash.Sales_Rep1 as 'Member',
 (select sum(v_Quota.GM) from v_Quota where v_Quota.Forecast_Year = ? and
 v_Quota.Forecast_Month between ? and ? and v_Quota.Member_RecID = v_rpt_Opportunity_SalesDash.MemberRec) as 'Quota',
sum(case when v_rpt_Opportunity_SalesDash.Sales_Stage = 11 then (ISNULL(SO_Forecast_Dtl.Revenue,0) -  ISNULL(SO_Forecast_Dtl.Cost,0) +
 (ISNULL(SO_Forecast_Dtl.Recurring_Revenue*(Nbr_Cycles/12),0)) -
 ISNULL(SO_Forecast_Dtl.Recurring_Cost,0)) else 0 end) as 'Invoiced',
DATEDIFF(day,Recurring_Date_End ,Recurring_Date_Start)as datedifference