問(wèn)題描述

我有這個(gè)查詢:

DECLARE @month INT設(shè)置@月=1選擇CLIO_ClientOrderItems.cl_Id，NoOfInv = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @monthAND DATEPART(yyyy, in_date_issued) = 2014然后 1 ELSE 0 結(jié)束)，MonthTotal = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @monthAND DATEPART(yyyy, in_date_issued) = 2014然后 in_total ELSE 0 END),總計(jì) = 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內(nèi)部聯(lián)接IN_發(fā)票在IN_Invoices.MasterOrderId = CLIO_ClientOrderItems.MasterOrderId通過(guò)...分組CLIO_ClientOrderItems.cl_id

我想要像這樣的輸出

如果我傳遞像 3 這樣的月份數(shù)字，它應(yīng)該將其顯示為 Jan Feb 和 March 及其相應(yīng)月份的相關(guān)記錄.

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

這篇關(guān)于如何使用datediff函數(shù)在sql的不同列中顯示月份名稱的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！