Remove From My Forums

SQL query Require

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

0

Sign in to vote

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

Proposed as answer by Laxmidhar sahoo Wednesday, August 5, 2020 12:29 PM

Tuesday, August 4, 2020 7:15 AM

0

Sign in to vote

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

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

Thursday, August 6, 2020 3:14 AM

0

Sign in to vote

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

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

Thursday, August 6, 2020 9:11 AM

0

Sign in to vote

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

Answered by:

SQL query Require

Question

Answers

All replies