How to display all the days of a month in SQL Server 2008?

veeresh Yeragatti

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.

Giorgi Nakeuri

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] 삭제

에서 수정
0

몇 마디 만하겠습니다

0리뷰
로그인참여 후 검토

관련 기사

분류에서Dev

SQL Server 2008 : how to remove char(0)

분류에서Dev

How to insert Khmer text into SQL Server 2008

분류에서Dev

Need to print all the missing value column names in SQL Server 2008

분류에서Dev

How to get sum of time field in SQL server 2008

분류에서Dev

how can we change the date format of SQL Server(2008) Database?

분류에서Dev

how to pivot on number of rows in SQL Server 2008+

분류에서Dev

IBM WORKLIGHT SQL Server 2008

분류에서Dev

SQL Server 2008의 ORDER BY

분류에서Dev

SQL Server 2008 connection error

분류에서Dev

SQL SERVER 2008 Select 문

분류에서Dev

SQL Server 2008-With 절

분류에서Dev

SQL Server Display only lines with all values not equal to 0

분류에서Dev

Alter table query plan for sql server 2008

분류에서Dev

Creating clients of SQL Server 2008 in same network

분류에서Dev

SQL Server 2008-피벗

분류에서Dev

SQL Server 2008에서 선택

분류에서Dev

누적 SUM SQL Server 2008

분류에서Dev

Roll Back Sql Server 2008 query

분류에서Dev

Automatic row deletion in SQL Server 2008

분류에서Dev

SQL Server 2008 dbms 및 Visual Studio

분류에서Dev

Access에서 SQL Server 2008로

분류에서Dev

SQL Server 2008 patindex 재귀

분류에서Dev

How many days between two dates are in a certain month?

분류에서Dev

How to run scheduled task 3 days before end of month

분류에서Dev

how to split a xml format data into row column format in sql server 2008 using stored procedure

분류에서Dev

How to check if SQL Server 2008 R2 database exists using power shell

분류에서Dev

SQL Server 2008 two tables with common dates field how to select by datewise from both tables

분류에서Dev

Calculate month with days in javascript

분류에서Dev

get all days and dates from month and year using moment.js

Related 관련 기사

뜨겁다태그

보관