locked
SQL query Require RRS feed

  • Question

  • Belwo is my data

    Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
    Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
    Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
    Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)
    
    INSERT INTO #ItemMasterFile VALUES
      (1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e')
    , (6,'f')
    , (7,'g')
    , (8,'h')
    , (9,'K')
    , (10,'L')
    , (11,'M');
    
    
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'03-06-2020')
    ,(112,2,1,200,'03-06-2020')
    ,(113,1,1,300,'03-06-2020')
    ,(114,6,1,100,'04-06-2020')
    ,(115,1,1,200,'04-06-2020')
    ,(116,1,1,300,'04-06-2020')
    ,(117,7,1,100,'05-06-2020')
    ,(118,5,1,200,'05-06-2020')
    ,(119,8,1,300,'06-06-2020')
    
    Insert into #DispatchBM Values
    (1001,'Akhter','03-06-2020')
    ,(1002,'Irfan','05-06-2020')
    Insert into #DispatchDB Values
    (11,1001,1,1,500)
    ,(12,1001,2,1,200)
    ,(13,1001,1,1,300)
    ,(14,1002,7,1,100)
    ,(15,1002,5,1,200)
    
    


    akhter

    Monday, August 3, 2020 11:51 AM

Answers

  • Hi akhter,

    I tried my best and please refer below:

    drop table if exists #ItemMasterFile
    drop table if exists #Bigbalprd
    drop table if exists #DispatchBM
    drop table if exists #DispatchDB
    drop table if exists #t
    
    Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
    Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
    Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
    Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)
    
    INSERT INTO #ItemMasterFile VALUES
      (1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e')
    , (6,'f')
    , (7,'g')
    , (8,'h')
    , (9,'K')
    , (10,'L')
    , (11,'M');
    
    
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'03-06-2020')
    ,(112,2,1,200,'03-06-2020')
    ,(113,1,1,300,'03-06-2020')
    ,(114,6,1,100,'04-06-2020')
    ,(115,1,1,200,'04-06-2020')
    ,(116,1,1,300,'04-06-2020')
    ,(117,7,1,100,'05-06-2020')
    ,(118,5,1,200,'05-06-2020')
    ,(119,8,1,300,'06-06-2020')
    
    Insert into #DispatchBM Values
    (1001,'Akhter','03-06-2020')
    ,(1002,'Irfan','05-06-2020')
    Insert into #DispatchDB Values
    (11,1001,1,1,500)
    ,(12,1001,2,1,200)
    ,(13,1001,1,1,300)
    ,(14,1002,7,1,100)
    ,(15,1002,5,1,200)
    
    DECLARE @StartDate  date = '03-06-2020';
    DECLARE @enddate date = '05-06-2020';
    
    ;with cte as (
    select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
    from #ItemMasterFile a
    left join #Bigbalprd b 
    on a.item_ID=b.item_ID
    where convert(date,B_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,B_Date
    )
    ,cte1 as (
    select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
    from #ItemMasterFile a
    left join #DispatchDB c
    on c.item_ID=a.item_ID
    left join #DispatchBM d
    on d.D_ID=c.D_ID 
    where convert(date,D_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,d.D_Date
    )
    
    select c.item_ID,upper(c.item_Name) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
    into #t
    from #ItemMasterFile c 
    left join cte a on a.item_ID=c.item_ID
    left join cte1 b on a.item_ID=b.item_ID
    and a.B_Date=b.D_Date
    
    DECLARE @cols NVARCHAR (MAX)
    
    SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols=SUBSTRING(@cols,2,len(@cols)-1)
    
    DECLARE @cols1 NVARCHAR (MAX)
    
    SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)
    
    DECLARE @cols2 NVARCHAR (MAX)
    
    SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'
    +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    DECLARE @cols3 NVARCHAR (MAX)
    
    SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'
    +',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)
    
    DECLARE @query NVARCHAR(MAX)
    SET @query = '  
    
    select '''' item_id,'''' item_Name,'+@cols3+'
    UNION ALL
    SELECT cast(item_id as varchar(10)) item_id,item_Name,' + @cols1 + '
                FROM (
        SELECT item_ID,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE
        FROM (
            select * from #t
        )s
        UNPIVOT
        (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
    ) src
                 PIVOT 
                 (
    			MAX(VALUE) FOR Name IN (' + @cols + ')
    ) pvt
                '   
    EXEC SP_EXECUTESQL @query
    
    

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Marked as answer by Akhterhussain Thursday, August 6, 2020 10:29 AM
    Thursday, August 6, 2020 9:11 AM

All replies

  • Maybe you should rephrase your question..

    Something like this?

    DECLARE @ItemMasterFile TABLE (
        item_ID INT ,
        item_Name VARCHAR(50)
    );
    
    DECLARE @Bigbalprd TABLE (
        B_ID INT ,
        item_ID INT ,
        B_QTY INT ,
        B_Weight INT ,
        B_Date DATE
    );
    
    DECLARE @DispatchBM TABLE (
        D_ID INT ,
        Name VARCHAR(50) ,
        D_Date DATE
    );
    
    DECLARE @DispatchDB TABLE (
        ID INT ,
        D_ID INT ,
        item_ID INT ,
        D_QTY INT ,
        D_Weight INT
    );
    
    INSERT INTO @ItemMasterFile
    VALUES ( 1, 'A' ) ,
           ( 2, 'B' ) ,
           ( 3, 'C' ) ,
           ( 4, 'D' ) ,
           ( 5, 'e' ) ,
           ( 6, 'f' ) ,
           ( 7, 'g' ) ,
           ( 8, 'h' ) ,
           ( 9, 'K' ) ,
           ( 10, 'L' ) ,
           ( 11, 'M' );
    
    
    INSERT INTO @Bigbalprd
    VALUES ( 111, 1, 1, 500, '03-06-2020' ) ,
           ( 112, 2, 1, 200, '03-06-2020' ) ,
           ( 113, 1, 1, 300, '03-06-2020' ) ,
           ( 114, 6, 1, 100, '04-06-2020' ) ,
           ( 115, 1, 1, 200, '04-06-2020' ) ,
           ( 116, 1, 1, 300, '04-06-2020' ) ,
           ( 117, 7, 1, 100, '05-06-2020' ) ,
           ( 118, 5, 1, 200, '05-06-2020' ) ,
           ( 119, 8, 1, 300, '06-06-2020' );
    
    INSERT INTO @DispatchBM
    VALUES ( 1001, 'Akhter', '03-06-2020' ) ,
           ( 1002, 'Irfan', '05-06-2020' );
    
    INSERT INTO @DispatchDB
    VALUES ( 11, 1001, 1, 1, 500 ) ,
           ( 12, 1001, 2, 1, 200 ) ,
           ( 13, 1001, 1, 1, 300 ) ,
           ( 14, 1002, 7, 1, 100 ) ,
           ( 15, 1002, 5, 1, 200 );
    
    DECLARE @CalendarBaseDate DATE = '2020-06-01';
    DECLARE @CalendarMonthsNumber INT = 1;
    
    WITH Calendar
    AS ( SELECT @CalendarBaseDate AS StartOfMonthDate ,
                EOMONTH(@CalendarBaseDate) AS EndOfMonthDate
         UNION ALL
         SELECT DATEADD(MONTH, 1, C.StartOfMonthDate) ,
                EOMONTH(DATEADD(MONTH, 1, C.StartOfMonthDate))
         FROM   Calendar C
         WHERE  C.StartOfMonthDate < DATEADD(MONTH, @CalendarMonthsNumber - 1, @CalendarBaseDate)) ,
         Dispatch
    AS ( SELECT DD.ID ,
                DD.D_ID ,
                DD.item_ID ,
                DD.D_QTY ,
                DD.D_Weight ,
                DB.Name ,
                DB.D_Date
         FROM   @DispatchDB DD
                INNER JOIN @DispatchBM DB ON DB.D_ID = DD.D_ID ) ,
         Data1
    AS ( SELECT IMF.item_ID ,
                IMF.item_Name ,
                C.StartOfMonthDate ,
                C.EndOfMonthDate ,
                B.B_ID ,
                B.B_QTY ,
                B.B_Weight ,
                B.B_Date ,
                D.ID ,
                D.D_ID ,
                D.D_QTY ,
                D.D_Weight ,
                D.Name ,
                D.D_Date
         FROM   @ItemMasterFile IMF
                CROSS JOIN Calendar C
                LEFT JOIN @Bigbalprd B ON B.item_ID = IMF.item_ID
                                          AND B.B_Date BETWEEN C.StartOfMonthDate AND C.EndOfMonthDate
                LEFT JOIN Dispatch D ON D.item_ID = IMF.item_ID
                                        AND D.D_Date BETWEEN C.StartOfMonthDate AND C.EndOfMonthDate )
    SELECT   D.item_ID ,
             D.item_Name ,
             SUM(IIF(D.StartOfMonthDate = '2020-06-01', D.B_QTY, NULL)) AS B_QTY_06 ,
             SUM(IIF(D.StartOfMonthDate = '2020-06-01', D.B_Weight, NULL)) AS B_QTY_06 ,
             SUM(IIF(D.StartOfMonthDate = '2020-06-01', D.D_QTY, NULL)) AS D_QTY_06 ,
             SUM(IIF(D.StartOfMonthDate = '2020-06-01', D.D_Weight, NULL)) AS D_QTY_06
    FROM     Data1 D
    GROUP BY D.item_ID ,
             D.item_Name;

    Monday, August 3, 2020 1:42 PM
  • Output is coming wrong ,dispatch qty and weight is not coming.

    akhter

    Tuesday, August 4, 2020 1:10 AM
  • Hi Akhter,

    Please refer below:

    ;with cte as (
    select a.item_ID,upper(a.item_Name) item_Name, B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
    from #ItemMasterFile a
    left join #Bigbalprd b 
    on a.item_ID=b.item_ID
    group by a.item_ID,a.item_Name,B_Date
    )
    ,cte1 as (
    select a.item_ID,upper(a.item_Name) item_Name, D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
    from #ItemMasterFile a
    left join #DispatchDB c
    on c.item_ID=a.item_ID
    left join #DispatchBM d
    on d.D_ID=c.D_ID 
    group by a.item_ID,a.item_Name,d.D_Date
    )
    ,cte2 as (
    select a.item_ID,a.item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
    from cte a left join cte1 b 
    on a.item_ID=b.item_ID
    and a.B_Date=b.D_Date
    )
    ,CTE3 AS (
    select item_ID,item_Name,[2020-03-06],[2020-04-06],[2020-05-06] FROM 
    (SELECT item_ID,item_Name,B_Date,COL,VAL 
        FROM cte2
    	CROSS APPLY (VALUES ('B_Qty',B_Qty))CS (COL,VAL))T
    PIVOT  
    (  
    MAX(val)  
    FOR B_Date IN ([2020-03-06],[2020-04-06],[2020-05-06]  )  
    ) AS PivotTable
    ),CTE4 AS (
    select item_ID,item_Name,[2020-03-06],[2020-04-06],[2020-05-06] FROM 
    (SELECT item_ID,item_Name,B_Date,COL,VAL 
        FROM cte2
    	CROSS APPLY (VALUES ('B_Weight',B_Weight))CS (COL,VAL))T
    PIVOT  
    (  
    MAX(val)  
    FOR B_Date IN ([2020-03-06],[2020-04-06],[2020-05-06]  )  
    ) AS PivotTable
    ),cte5 as (
    select item_ID,item_Name,[2020-03-06],[2020-04-06],[2020-05-06] FROM 
    (SELECT item_ID,item_Name,B_Date,COL,VAL 
        FROM cte2
    	CROSS APPLY (VALUES ('D_Qty',D_Qty))CS (COL,VAL))T
    PIVOT  
    (  
    MAX(val)  
    FOR B_Date IN ([2020-03-06],[2020-04-06],[2020-05-06]  )  
    ) AS PivotTable
    ),cte6 as (
    select item_ID,item_Name,[2020-03-06],[2020-04-06],[2020-05-06] FROM 
    (SELECT item_ID,item_Name,B_Date,COL,VAL 
        FROM cte2
    	CROSS APPLY (VALUES ('D_Weight',D_Weight))CS (COL,VAL))T
    PIVOT  
    (  
    MAX(val)  
    FOR B_Date IN ([2020-03-06],[2020-04-06],[2020-05-06]  )  
    ) AS PivotTable
    )
    
    select '' item_id,'' item_Name,
    'B_Qty' [2020-03-06],'B_Weight' [2020-03-06],'D_Qty' [2020-03-06],'D_Weight' [2020-03-06],
    'B_Qty' [2020-04-06],'B_Weight' [2020-04-06],'D_Qty' [2020-04-06],'D_Weight' [2020-04-06],
    'B_Qty' [2020-05-06],'B_Weight' [2020-05-06],'D_Qty' [2020-05-06],'D_Weight' [2020-05-06]
    union all
    select cast(a.item_id as varchar(10)) item_id,a.item_Name,
    cast(isnull(a.[2020-03-06],0) as varchar(10)) [2020-03-06],
    cast(isnull(b.[2020-03-06],0) as varchar(10)) [2020-03-06],
    cast(isnull(c.[2020-03-06],0) as varchar(10)) [2020-03-06],
    cast(isnull(d.[2020-03-06],0) as varchar(10)) [2020-03-06],
    cast(isnull(a.[2020-04-06],0) as varchar(10)) [2020-04-06],
    cast(isnull(b.[2020-04-06],0) as varchar(10)) [2020-04-06],
    cast(isnull(c.[2020-04-06],0) as varchar(10)) [2020-04-06],
    cast(isnull(d.[2020-04-06],0) as varchar(10)) [2020-04-06],
    cast(isnull(a.[2020-05-06],0) as varchar(10)) [2020-05-06],
    cast(isnull(b.[2020-05-06],0) as varchar(10)) [2020-05-06],
    cast(isnull(c.[2020-05-06],0) as varchar(10)) [2020-05-06],
    cast(isnull(d.[2020-05-06],0) as varchar(10)) [2020-05-06]
    from CTE3 a inner join cte4 b on a.item_ID=b.item_ID
    inner join cte5 c on c.item_id=a.item_id
    inner join cte6 d on d.item_id=a.item_id

    Best Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 7:15 AM
  • HI Melissa,

    your provider query ,is working fine ,but how to filter data between two date,mean i want to pass date parameter between two date to fetch record from database .

     


    akhter

    Tuesday, August 4, 2020 8:10 AM
  • Hi akhter,

    I made some modifications on my previous query and we could get the result as below:

    drop table if exists #ItemMasterFile
    drop table if exists #Bigbalprd
    drop table if exists #DispatchBM
    drop table if exists #DispatchDB
    
    Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
    Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
    Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
    Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)
    
    INSERT INTO #ItemMasterFile VALUES
      (1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e')
    , (6,'f')
    , (7,'g')
    , (8,'h')
    , (9,'K')
    , (10,'L')
    , (11,'M');
    
    
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'03-06-2020')
    ,(112,2,1,200,'03-06-2020')
    ,(113,1,1,300,'03-06-2020')
    ,(114,6,1,100,'04-06-2020')
    ,(115,1,1,200,'04-06-2020')
    ,(116,1,1,300,'04-06-2020')
    ,(117,7,1,100,'05-06-2020')
    ,(118,5,1,200,'05-06-2020')
    ,(119,8,1,300,'06-06-2020')
    
    Insert into #DispatchBM Values
    (1001,'Akhter','03-06-2020')
    ,(1002,'Irfan','05-06-2020')
    Insert into #DispatchDB Values
    (11,1001,1,1,500)
    ,(12,1001,2,1,200)
    ,(13,1001,1,1,300)
    ,(14,1002,7,1,100)
    ,(15,1002,5,1,200)
    
    DECLARE @StartDate  date = '03-06-2020';
    DECLARE @enddate date = '05-06-2020';
    
    ;with cte as (
    select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
    from #ItemMasterFile a
    left join #Bigbalprd b 
    on a.item_ID=b.item_ID
    group by a.item_ID,a.item_Name,B_Date
    )
    ,cte1 as (
    select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
    from #ItemMasterFile a
    left join #DispatchDB c
    on c.item_ID=a.item_ID
    left join #DispatchBM d
    on d.D_ID=c.D_ID 
    where convert(date,D_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,d.D_Date
    )
    ,cte2 as (
    select a.item_ID,a.item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
    from cte a left join cte1 b 
    on a.item_ID=b.item_ID
    and a.B_Date=b.D_Date
    )
    
    SELECT item_ID,item_Name,ISNULL([B_QTY 2020-03-06],0) [B_QTY 2020-03-06],
    ISNULL([B_WEIGHT 2020-03-06], 0) [B_WEIGHT 2020-03-06],
    ISNULL([D_QTY 2020-03-06], 0) [D_QTY 2020-03-06],
    ISNULL([D_WEIGHT 2020-03-06],0) [D_WEIGHT 2020-03-06],
    ISNULL([B_QTY 2020-04-06], 0) [B_QTY 2020-04-06],
    ISNULL([B_WEIGHT 2020-04-06], 0) [B_WEIGHT 2020-04-06],
    ISNULL([D_QTY 2020-04-06], 0) [D_QTY 2020-04-06],
    ISNULL([D_WEIGHT 2020-04-06],0) [D_WEIGHT 2020-04-06],
    ISNULL([B_QTY 2020-05-06], 0) [B_QTY 2020-05-06],
    ISNULL([B_WEIGHT 2020-05-06], 0) [B_WEIGHT 2020-05-06],
    ISNULL([D_QTY 2020-05-06], 0) [D_QTY 2020-05-06],
    ISNULL([D_WEIGHT 2020-05-06],0)  [D_WEIGHT 2020-05-06]
    FROM (
        SELECT item_ID,item_Name, ITEM +' '+ CAST(B_Date AS VARCHAR) AS Name, VALUE
        FROM (
            select * from cte2
        )s
        UNPIVOT
        (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
    ) src
    PIVOT
    (MAX(VALUE) FOR Name IN ([B_QTY 2020-03-06], [B_WEIGHT 2020-03-06], [D_QTY 2020-03-06], [D_WEIGHT 2020-03-06],
    [B_QTY 2020-04-06], [B_WEIGHT 2020-04-06], [D_QTY 2020-04-06], [D_WEIGHT 2020-04-06],
    [B_QTY 2020-05-06], [B_WEIGHT 2020-05-06], [D_QTY 2020-05-06], [D_WEIGHT 2020-05-06])
    ) pvt
    
    

    The output is a little different from your expected one due to the particularity of PIVOT function.

    Is it acceptable for you?

    If yes, we could continue with any possiblity of dynamic method.

    Wishes

    Melissa 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 8:59 AM
  • Hi Melissa Ma,

    why here fixed date ,i want to pass parameter of date,

    SELECT item_ID,item_Name,ISNULL([B_QTY 2020-03-06],0) [B_QTY 2020-03-06], ISNULL([B_WEIGHT 2020-03-06], 0) [B_WEIGHT 2020-03-06], ISNULL([D_QTY 2020-03-06], 0) [D_QTY 2020-03-06], ISNULL([D_WEIGHT 2020-03-06],0) [D_WEIGHT 2020-03-06], ISNULL([B_QTY 2020-04-06], 0) [B_QTY 2020-04-06], ISNULL([B_WEIGHT 2020-04-06], 0) [B_WEIGHT 2020-04-06], ISNULL([D_QTY 2020-04-06], 0) [D_QTY 2020-04-06], ISNULL([D_WEIGHT 2020-04-06],0) [D_WEIGHT 2020-04-06], ISNULL([B_QTY 2020-05-06], 0) [B_QTY 2020-05-06], ISNULL([B_WEIGHT 2020-05-06], 0) [B_WEIGHT 2020-05-06], ISNULL([D_QTY 2020-05-06], 0) [D_QTY 2020-05-06], ISNULL([D_WEIGHT 2020-05-06],0) [D_WEIGHT 2020-05-06]

    (MAX(VALUE) FOR Name IN ([B_QTY 2020-03-06], [B_WEIGHT 2020-03-06], [D_QTY 2020-03-06], [D_WEIGHT 2020-03-06],
    [B_QTY 2020-04-06], [B_WEIGHT 2020-04-06], [D_QTY 2020-04-06], [D_WEIGHT 2020-04-06],
    [B_QTY 2020-05-06], [B_WEIGHT 2020-05-06], [D_QTY 2020-05-06], [D_WEIGHT 2020-05-06])



    akhter

    Wednesday, August 5, 2020 10:19 AM
  • Hi akhter,

    Please refer dynamic pivot method from below:

    drop table if exists #ItemMasterFile
    drop table if exists #Bigbalprd
    drop table if exists #DispatchBM
    drop table if exists #DispatchDB
    drop table if exists #t
    
    Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
    Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
    Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
    Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)
    
    INSERT INTO #ItemMasterFile VALUES
      (1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e')
    , (6,'f')
    , (7,'g')
    , (8,'h')
    , (9,'K')
    , (10,'L')
    , (11,'M');
    
    
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'03-06-2020')
    ,(112,2,1,200,'03-06-2020')
    ,(113,1,1,300,'03-06-2020')
    ,(114,6,1,100,'04-06-2020')
    ,(115,1,1,200,'04-06-2020')
    ,(116,1,1,300,'04-06-2020')
    ,(117,7,1,100,'05-06-2020')
    ,(118,5,1,200,'05-06-2020')
    ,(119,8,1,300,'06-06-2020')
    
    Insert into #DispatchBM Values
    (1001,'Akhter','03-06-2020')
    ,(1002,'Irfan','05-06-2020')
    Insert into #DispatchDB Values
    (11,1001,1,1,500)
    ,(12,1001,2,1,200)
    ,(13,1001,1,1,300)
    ,(14,1002,7,1,100)
    ,(15,1002,5,1,200)
    
    DECLARE @StartDate  date = '03-06-2020';
    DECLARE @enddate date = '05-06-2020';
    
    ;with cte as (
    select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
    from #ItemMasterFile a
    left join #Bigbalprd b 
    on a.item_ID=b.item_ID
    where convert(date,B_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,B_Date
    )
    ,cte1 as (
    select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
    from #ItemMasterFile a
    left join #DispatchDB c
    on c.item_ID=a.item_ID
    left join #DispatchBM d
    on d.D_ID=c.D_ID 
    where convert(date,D_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,d.D_Date
    )
    
    select c.item_ID,upper(c.item_Name) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
    into #t
    from #ItemMasterFile c 
    left join cte a on a.item_ID=c.item_ID
    left join cte1 b on a.item_ID=b.item_ID
    and a.B_Date=b.D_Date
    
    DECLARE @cols NVARCHAR (MAX)
    
    SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols=SUBSTRING(@cols,2,len(@cols)-1)
    
    DECLARE @cols1 NVARCHAR (MAX)
    
    SET @cols1 = (SELECT DISTINCT ',ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'
    +',ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'
    +',ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)
    
    DECLARE @query NVARCHAR(MAX)
    
    SET @query = '   
    SELECT item_ID,item_Name,' + @cols1 + '
                FROM (
        SELECT item_ID,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE
        FROM (
            select * from #t
        )s
        UNPIVOT
        (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
    ) src
                 PIVOT 
                 (
    			MAX(VALUE) FOR Name IN (' + @cols + ')
    ) pvt
                '     
    EXEC SP_EXECUTESQL @query

    Wishes

    Melissa



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 3:14 AM
  • Hi Melissa Ma,

    Thanks Thanks alot,more thing need to be done,but i will post another question ,last thing can you align heading as you provided last query below image


    akhter

    Thursday, August 6, 2020 8:37 AM
  • Hi akhter,

    I tried my best and please refer below:

    drop table if exists #ItemMasterFile
    drop table if exists #Bigbalprd
    drop table if exists #DispatchBM
    drop table if exists #DispatchDB
    drop table if exists #t
    
    Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
    Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
    Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
    Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)
    
    INSERT INTO #ItemMasterFile VALUES
      (1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e')
    , (6,'f')
    , (7,'g')
    , (8,'h')
    , (9,'K')
    , (10,'L')
    , (11,'M');
    
    
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'03-06-2020')
    ,(112,2,1,200,'03-06-2020')
    ,(113,1,1,300,'03-06-2020')
    ,(114,6,1,100,'04-06-2020')
    ,(115,1,1,200,'04-06-2020')
    ,(116,1,1,300,'04-06-2020')
    ,(117,7,1,100,'05-06-2020')
    ,(118,5,1,200,'05-06-2020')
    ,(119,8,1,300,'06-06-2020')
    
    Insert into #DispatchBM Values
    (1001,'Akhter','03-06-2020')
    ,(1002,'Irfan','05-06-2020')
    Insert into #DispatchDB Values
    (11,1001,1,1,500)
    ,(12,1001,2,1,200)
    ,(13,1001,1,1,300)
    ,(14,1002,7,1,100)
    ,(15,1002,5,1,200)
    
    DECLARE @StartDate  date = '03-06-2020';
    DECLARE @enddate date = '05-06-2020';
    
    ;with cte as (
    select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
    from #ItemMasterFile a
    left join #Bigbalprd b 
    on a.item_ID=b.item_ID
    where convert(date,B_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,B_Date
    )
    ,cte1 as (
    select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
    from #ItemMasterFile a
    left join #DispatchDB c
    on c.item_ID=a.item_ID
    left join #DispatchBM d
    on d.D_ID=c.D_ID 
    where convert(date,D_Date,105) between @startdate and @enddate
    group by a.item_ID,a.item_Name,d.D_Date
    )
    
    select c.item_ID,upper(c.item_Name) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
    into #t
    from #ItemMasterFile c 
    left join cte a on a.item_ID=c.item_ID
    left join cte1 b on a.item_ID=b.item_ID
    and a.B_Date=b.D_Date
    
    DECLARE @cols NVARCHAR (MAX)
    
    SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols=SUBSTRING(@cols,2,len(@cols)-1)
    
    DECLARE @cols1 NVARCHAR (MAX)
    
    SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'
    +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)
    
    DECLARE @cols2 NVARCHAR (MAX)
    
    SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'
    +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    DECLARE @cols3 NVARCHAR (MAX)
    
    SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'
    +',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '
                    from #t where ISNULL(B_date,'')<>'' for xml path(''))
    
    set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)
    
    DECLARE @query NVARCHAR(MAX)
    SET @query = '  
    
    select '''' item_id,'''' item_Name,'+@cols3+'
    UNION ALL
    SELECT cast(item_id as varchar(10)) item_id,item_Name,' + @cols1 + '
                FROM (
        SELECT item_ID,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE
        FROM (
            select * from #t
        )s
        UNPIVOT
        (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
    ) src
                 PIVOT 
                 (
    			MAX(VALUE) FOR Name IN (' + @cols + ')
    ) pvt
                '   
    EXEC SP_EXECUTESQL @query
    
    

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Marked as answer by Akhterhussain Thursday, August 6, 2020 10:29 AM
    Thursday, August 6, 2020 9:11 AM
  • Thanks alot Melissa Ma,

    Hope you will further guide me,thanks alot again for being a kind .

    Thanks agains


    akhter

    Thursday, August 6, 2020 10:30 AM