Display Whole Days of month?

akhter hussain 2,491 Reputation points
2021-03-27T06:31:23.987+00:00

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  
 
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,503 questions
No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-03-27T11:32:04.763+00:00

    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
    
    No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-03-27T09:48:47.913+00:00

    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.