本文介绍了如何使用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
这篇关于如何使用datediff函数在sql的不同列中显示月份名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！