locked
Display all date of the month RRS feed

  • Question

  • User-367318540 posted

    I want to display whole date of the month, whether Data exit or not ,Between 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((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

    Saturday, March 27, 2021 8:52 AM

Answers

  • User-939850651 posted

    Hi akhterr,

    Show all dates in date-range? If this is the case, you could try this query:

    Declare @fromdate Date='2021-01-01'
    Declare @todate   Date='2021-01-30'
    
    SELECT  TOP (DATEDIFF(DAY, @fromdate, @todate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @fromdate)
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b

    Based on the results of your current query, and use Join statement with these two resultsets.

    Declare @fromdate Date='2021-01-01'
    Declare @todate   Date='2021-01-30'
    
    select temp1.Date,temp2.Descriptionitem,temp2.Qty from (
    		SELECT  TOP (DATEDIFF(DAY, @fromdate, @todate) + 1)
    			Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @fromdate)
    		FROM    sys.all_objects a
                    CROSS JOIN sys.all_objects b
    		) temp1
    left join (
    		SELECT I.Descriptionitem,
    			isnull((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,prdQTY
     ) temp2 on temp1.Date = temp2.Entrydate

    Result:

    If I misunderstood anything, could you describe more details?

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 29, 2021 3:50 AM