question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked ErlandSommarskog answered

Display Whole Days of month?

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered akhterhussain-3167 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i have created table of Dates,but do not know that how to join

0 Votes 0 ·