本文介紹了如何使用datediff函數(shù)在sql的不同列中顯示月份名稱的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我有這個查詢:
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),總計 = 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通過...分組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 這樣的月份數(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的不同列中顯示月份名稱的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！