Display Whole Days of month?

Question

I want to Display whole month Days ,whether data exit or not between given Date range.

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),weight int);  
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate date,DelID int);  
  
  
INSERT INTO #ItemMasterFile VALUES  
  (1,'A',100)  
, (2,'B',100)  
, (3,'C',100)  
, (4,'D',100)  
, (5,'e',100)  
, (6,'f',100)  
, (7,'g',100)  
, (8,'h',100)  
, (9,'K',100)  
, (10,'L',100)  
, (11,'M',100);  
  
  
INSERT INTO #Probale VALUES   
   (1,1,1,001,100,'2021-01-13',null)  
, (2,3,1,001,200,'2021-01-15',null)  
, (3,11,1,002,200,'2021-01-15',null)  
, (5,10,1,002,200,'2021-01-16',null)  
, (6,1,1,003,200,'2021-01-16',null)  
, (7,3,1,003,200,'2021-01-17',null);  
  
  
  Declare @fromdate Date='2021-01-01'  
Declare @todate   Date='2021-01-30'  
  
select I.Descriptionitem,isnull(sum(prdqty),0) as Qty,(P.Entrydate )  from #Probale P right outer join #ItemMasterFile I on I.CodeItem=P.Codeitem  
and P.Entrydate between @fromdate and @todate   
group by i.Descriptionitem,p.Entrydate  
 

Answer 1

I don't know what result set you want exactly, but this query may get you started. Also look at the examples in my article.

select I.Descriptionitem, d.d AS date, isnull(sum(prdQTY),0) as Qty
from   #ItemMasterFile I 
cross  join (select d from NorthNumbers..Dates where d between @fromdate and @todate) AS d
left   join #Probale P on I.CodeItem=P.CodeItem
                        and P.Entrydate = d.d
group by I.Descriptionitem, d.d
order  by  I.Descriptionitem, d.d

Answer 2

To this end, you need a table of dates. I have written about it here: http://www.sommarskog.se/Short%20Stories/table-of-numbers.html#DatesHours.