I have written a query like
SELECT
Sum(a.in_total) as Totall,
DATEPART(dd, a.in_date_issued) as dateissued
FROM
dbo.IN_Invoices as a
WHERE
DATEPART(mm, a.in_date_issued) = 5
AND DATEPART(yyyy, a.in_date_issued)= 2015
AND a.sy_id='DPI'
AND EXISTS(SELECT DISTINCT cl_id as clid
FROM dbo.cloi_clientorderitems as c
WHERE c.masterorderid=a.masterorderid)
--AND c.cl_id NOT IN ('100001_DPO'))
GROUP BY
DATEPART(dd, a.in_date_issued)
ORDER BY dateissued ASC
This query will written the out put like
Total| day
100 | 1
0 | 3
150 | 10
-
-
200 | 31
Expected output
Total| day
100 | 1
150 | 2
0 | 3
600 | 4
-
-
200 | 31
Please help me in that query.
You can try something like this:
DECLARE @y INT;
DECLARE @m INT;
SET @y = 2015;
SET @m = 5;
WITH
m AS(SELECT 1 AS d UNION ALL SELECT d+1 FROM m WHERE d < datediff(d, DATEADD(mm, @m - 1, CAST(@y AS VARCHAR(8)) + '0101'), dateadd(m, 1, DATEADD(mm, @m - 1, CAST(@y AS VARCHAR(8)) + '0101'))))
SELECT isnull(Totall, 0) AS Totall, m.d AS dateissued
FROM m
LEFT JOIN(
SELECT SUM(a.in_total) AS Totall ,
DATEPART(dd, a.in_date_issued) AS dateissued
FROM dbo.IN_Invoices AS a
WHERE DATEPART(mm, a.in_date_issued) = @m
AND DATEPART(yyyy, a.in_date_issued) = @y
AND a.sy_id = 'DPI'
AND EXISTS ( SELECT DISTINCT
cl_id AS clid
FROM dbo.cloi_clientorderitems AS c
WHERE c.masterorderid = a.masterorderid )
--and c.cl_id not in ('100001_DPO'))
GROUP BY DATEPART(dd, a.in_date_issued)
)t ON m.d = t.dateissued
ORDER BY m.d ASC
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다