如何使用datediff函数在sql的不同列中显示月份名称

我有这个查询:

DECLARE @month INT设置@月=1选择CLIO_ClientOrderItems.cl_Id，NoOfInv = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @monthAND DATEPART(yyyy, in_date_issued) = 2014然后 1 ELSE 0 结束)，MonthTotal = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @monthAND DATEPART(yyyy, in_date_issued) = 2014然后 in_total ELSE 0 END),总计 = SUM(in_total),RemainingAmount = SUM(in_total) - SUM(CASE)WHEN DATEPART(mm, in_date_issued) <= @monthTHEN in_total ELSE 0 END)从(选择DISTINCT MasterOrderId, cl_Id从CLOI_ClientOrderItems) 作为 CLOI_ClientOrderItems内部联接IN_发票在IN_Invoices.MasterOrderId = CLIO_ClientOrderItems.MasterOrderId通过...分组CLIO_ClientOrderItems.cl_id

我想要像这样的输出

<前>noofinv |amt |clid |总计 |一月 |二月 |三月 |剩余时间5 |7.00 |100000_Pri |245.00 |0.00 |238.00 |7.00 |238.0012 |2510.12 |100001_pro |181110.29 |138891.92 |9708.25 |510.12 |178600.17

如果我传递像 3 这样的月份数字，它应该将其显示为 Jan Feb 和 March 及其相应月份的相关记录.

解决方案

You can Try like This...

 声明 @MonthCount int=1SELECT DATENAME(month, DATEADD(month, @MonthCount-1 ,CAST('2014-01-01' AS datetime))) 作为 Month_Name

操作:

Month_Name一月

I have this query:

DECLARE @month INT
SET @month=1

SELECT 

    CLOI_ClientOrderItems.cl_Id,

    NoOfInv = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @month
                        AND DATEPART(yyyy, in_date_issued) = 2014
                       THEN 1 ELSE 0 END),

    MonthTotal = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @month 
                           AND DATEPART(yyyy, in_date_issued) = 2014
                          THEN in_total ELSE 0 END),

    Grandtotal = SUM(in_total),

    RemainingAmount = SUM(in_total) - SUM(CASE 
                          WHEN DATEPART(mm, in_date_issued) <= @month 
                          THEN in_total ELSE 0 END)


FROM (SELECT 
          DISTINCT MasterOrderId, cl_Id 
      FROM 
          CLOI_ClientOrderItems) as CLOI_ClientOrderItems 

INNER JOIN 
      IN_Invoices 
ON
      IN_Invoices.MasterOrderId = CLOI_ClientOrderItems.MasterOrderId
GROUP BY 
      CLOI_ClientOrderItems.cl_id

i want output like

noofinv |amt      |clid       | grandtotal |    jan         |feb     |march  |remainingamt
5   |7.00     |100000_Pri | 245.00     |      0.00      |238.00  |7.00   |238.00
12  |2510.12  |100001_pro | 181110.29  |    138891.92   |9708.25 |510.12 |178600.17

If I pass month number like 3, it should display it as like Jan Feb and March and its related records in the respective month.

解决方案

You can Try like This...

 declare @MonthCount int=1
 SELECT DATENAME(month, DATEADD(month, @MonthCount-1 , 
CAST('2014-01-01' AS datetime))) as Month_Name

OP:

Month_Name
January