在sql查询中选择语句 [英] Select statement in sql query

问题描述

嘿伙计们，我只是想知道有人可以用一些sql来帮助我，我想检查列日期是否是当前月份，如果有的话，如果是这样的话就会进行某些计算。我的Sql

Hey guys, im just wondering can someone help me with a bit of sql, I want to check if a column date is the current month and if so preform certain calculations if it is. My Sql

SELECT  dg.DestinationGroupName AS Destination, c.CarrierShortName AS Carrier,
       SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11),ba.CDRDate, 106))) AS Month,
   --IF DatePart(ba.CDRDate) = DatePart(getdate())
   --BEGIN
       SUM(ba.TotalDuration) / 60 AS DurationInMinutes,
       SUM(ba.TotalCost) AS TotalRevenue, SUM(av.AvgCost * ba.TotalDuration)
       AS TotalCost, SUM(ba.TotalCost) - SUM(av.AvgCost * ba.TotalDuration) AS Margin
   --ELSE
   --  SUM(ba.TotalDuration) / 60 * 31 AS DurationInMinutes,
   --  SUM(ba.TotalCost) / 31 AS TotalRevenue, SUM(av.AvgCost * ba.TotalDuration) / 31
   --  AS TotalCost, SUM(ba.TotalCost) - SUM(av.AvgCost * ba.TotalDuration) AS Margin
   --END
   FROM

注释掉的代码就是我想要的在我的查询中。

先谢谢你们。

The commented out code is what I am trying to acheive in my query.
Thanks in advance guys.

推荐答案

尝试使用 CASE [ ^ ]声明。

这里是一个样本

Try using CASE[^] statement.
here is a sample

SELECT  dg.DestinationGroupName AS Destination, c.CarrierShortName AS Carrier,
        SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11),ba.CDRDate, 106))) AS Month,

     CASE WHEN DatePart(ba.CDRDate) = DatePart(getdate()) THEN SUM(ba.TotalDuration) / 60
          ELSE SUM(ba.TotalDuration) / 60 * 31 END AS DurationInMinutes,

     CASE WHEN DatePart(ba.CDRDate) = DatePart(getdate()) THEN SUM(ba.TotalCost)
          ELSE SUM(ba.TotalCost) / 31 END AS TotalRevenue,

     CASE WHEN DatePart(ba.CDRDate) = DatePart(getdate()) THEN SUM(av.AvgCost * ba.TotalDuration)
          ELSE SUM(av.AvgCost * ba.TotalDuration) / 31  END AS TotalCost,

     SUM(ba.TotalCost) - SUM(av.AvgCost * ba.TotalDuration) AS Margin
    FROM --....

自您正在使用聚合函数，您还需要使用 GROUP BY（Transact-SQL） [< a href =http://msdn.microsoft.com/en-us/library/ms177673.aspxtarget =_ blanktitle =新窗口> ^ ]

这篇关于在sql查询中选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！