dongpaocuan7498
2013-11-19 07:31
浏览 74
已采纳

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

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:

图片转代码服务由CSDN问答提供 功能建议

我正在构建一个销售仪表板并遇到一个小问题,我有理由相信它有一个简单的解决方案,但是 我无法完全实现这一目标。

所以我在SQL Server 2008中构建了一个SQL数据库。就这个数据库而言,我没有深入研究信息以获得销售相关的问题 数据。 但有一个例外; 每月重新出现的收入。 写入数据库的应用程序(Connectwise)将重复出现的收入写为一个单独的条目,该条目由开始日期和结束日期的日期时间字段控制。

目前我有销售仪表板从另一个获取信息 字段,但我无法让它实际上正确地挂钩日期时间约束。 我用php用两个查询数据库? 字段是用户定义的开始和结束日期。参见下面:

  $ sql =“选择Owner_Level.Description为'Location',v_rpt_Opportunity_SalesDash.Sales_Rep1为'Member',(选择 sum(v_Quota.GM)来自v_Quota,其中v_Quota.Forecast_Year =?和v_Quota.Forecast_Month介于?和?和v_Quota.Member_RecID = v_rpt_Opportunity_SalesDash.MemberRec)为'Quota',
sum(v_rpt_Opportunity_SalesDash.Sales_Stage = 11时的情况)(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))否则0结束)为'已开票'  ,
   
 
 

我正在阅读有关Datediff命令的信息,但试图输入此信息无效。如果有人可以提供任何帮助或建议,我真的无法' 告诉你我有多喜欢它。

示例数据以证明日期为一个条目的想法。例如,这将构成52个重复发生的实例: \ Ñ

SO_Forecast_Dtl_RecID:1 Opportunity_RecID:1 描述:Opportunity1 Revenue:1000 Cost:500 SO_Opp_Status_RecID:42 Include_Flag:1 QuoteWerks_DocNO:3938 QuoteWerks_DocName:1223 Updated_By:用户 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(与周设置交叉引用) Cycle_Basis_ID:1 Recurring_Flag :1 Product_Flag:1 Mobile_Guid:

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongzecai0684 2013-11-19 08:01
    已采纳

    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
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题