问题描述
我有这个查询:
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