locked
Month wise Data Display in sql? RRS feed

  • Question

  • User-367318540 posted

    I want to display data in sql Month wise ...here is my data and expected result...

    CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
     
    INSERT INTO #ItemMasterFile VALUES
      (1,'A',1,100)
    , (2,'B',2,100)
    , (3,'C',3,100)
    , (4,'D',4,100)
    , (5,'e',5,100)
    , (6,'f',6,100)
    , (7,'g',4,100)
    , (8,'h',4,100)
    , (9,'K',2,100)
    , (10,'L',4,100)
    , (11,'M',2,100);
     
    INSERT INTO #Probale VALUES
       (1,1,1,001,100,'01-05-2019',null),
          (1,1,1,001,100,'01-06-2019',null)
    , (2,3,1,001,200,'02-07-2019',null)
    , (3,11,1,002,200,'03-08-2019',null)
    , (4,10,1,002,200,'08-08-2019',null)
    , (4,1,1,003,200,'08-08-2019',null)
    , (4,1,1,003,200,'08-08-2019',null);

    Expected result

    S.No Item May-19 Jun-19 Jul-19 Aug-19
    1 A 1 1 0 2
    2 B 0 0 0 0
    3 C 0 0 1 0
    4 D 0 0 0 0
    5 E 0 0 0 0
    6 F 0 0 0 0
    7 E 0 0 0 0
    8 G 0 0 0 0
    9 H 0 0 0 0
    10 K 0 0 0 1
    11 L 0 0 0 1
    12 m 0 0 0 0
    Friday, August 2, 2019 5:54 AM

Answers

  • User77042963 posted
    CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
     
    INSERT INTO #ItemMasterFile VALUES
      (1,'A',1,100)
    , (2,'B',2,100)
    , (3,'C',3,100)
    , (4,'D',4,100)
    , (5,'e',5,100)
    , (6,'f',6,100)
    , (7,'g',4,100)
    , (8,'h',4,100)
    , (9,'K',2,100)
    , (10,'L',4,100)
    , (11,'M',2,100);
     
    INSERT INTO #Probale VALUES
       (1,1,1,001,100,'2019-5-1',null),
          (1,1,1,001,100,'2019-6-1',null)
    , (2,3,1,001,200,'2019-7-2',null)
    , (3,11,1,002,200,'2019-8-3',null)
    , (4,10,1,002,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null);
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
     
    
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when Convert(char(7),Entrydate,112)='+ quotename( Convert(char(7),Entrydate,112),'''') +' then  1  else null end) as ' + Quotename(Left(datename(month,Entrydate),3)+Right(Year(Entrydate),2),'[')  + char(10)+char(13)
    FROM  (select distinct Entrydate
      from  #ItemMasterFile i 
    left join #Probale p on i.CodeItem=p.CodeItem) t
    --Order by  Entrydate
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    
    
    --print @ColumnHeaders
     
     SET @sql = N'Select CodeItem,Descriptionitem, '
    + @ColumnHeaders 
    +' FROM (select i.CodeItem,i.Descriptionitem, p.Entrydate
      FROM  #ItemMasterFile i 
    LEFT JOIN #Probale p on i.CodeItem=p.CodeItem) t
    Group by CodeItem,Descriptionitem '
     
    --print @sql
     EXEC sp_executesql @sql;
    
    drop table #ItemMasterFile,#Probale

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 2, 2019 4:08 PM

All replies

  • User-719153870 posted

    Hi akhterr,

    According your expected result, you have inserted wrong format Entrydate.

    What you want is '05-01-2019'(mm-dd-yyyy) but you put '01-05-2019', please take notice and update your code.

    Below is a one time query which you don't need to excute them step by step like the one before, and temp tables created in the process will be dropped at the end.

    CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
     
    INSERT INTO #ItemMasterFile VALUES
      (1,'A',1,100)
    , (2,'B',2,100)
    , (3,'C',3,100)
    , (4,'D',4,100)
    , (5,'e',5,100)
    , (6,'f',6,100)
    , (7,'g',4,100)
    , (8,'h',4,100)
    , (9,'K',2,100)
    , (10,'L',4,100)
    , (11,'M',2,100);
     
    INSERT INTO #Probale VALUES
       (1,1,1,001,100,'2019-5-1',null),
          (1,1,1,001,100,'2019-6-1',null)
    , (2,3,1,001,200,'2019-7-2',null)
    , (3,11,1,002,200,'2019-8-3',null)
    , (4,10,1,002,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null);
    --------------------------------------------------------
    select a.CodeItem as [S.No],a.Descriptionitem as [Item],a.Entrydate as [Date] into #AAA from( select i.CodeItem,p.Entrydate,i.Descriptionitem from #ItemMasterFile i left join #Probale p on i.CodeItem=p.CodeItem) a order by a.CodeItem 
    select distinct(MONTH([Date])) as [month] into #Month from #AAA where [Date] is not null
    select distinct([S.No]) as [S.No],Item as [Item] into #Target from #AAA
    declare @i int
    declare @s varchar(2000)
    declare @sp varchar(2000)
    declare @m int
    set @i=1
    
    while @i<=(select count(*) from #Month)
    begin
    set @m=(select top 1 [month] from #Month where [month] not in (select top(@i-1) [month] from #Month))
    set @s='alter table #Target add ['+CONVERT(varchar(50),@m)+'-19] int not null default(0)'
    set @sp='update #Target set ['+CONVERT(varchar(50),@m)+'-19]=['+CONVERT(varchar(50),@m)+'-19]+1 where [S.No] in (select [S.No] from #AAA where MONTH([Date])='''+CONVERT(varchar(50),@m)+''')'
    exec(@s)
    exec(@sp)
    set @i+=1
    end
    select * from #Target
    ------------------
    drop table #ItemMasterFile
    drop table #Probale
    drop table #AAA
    drop table #Month
    drop table #Target

    Here's the result of this query:

    Best Regard,

    Yang Shen

    Friday, August 2, 2019 7:19 AM
  • User-367318540 posted

    THank for Response....

    Yang Shen,

    but having little more correction need

    prdQTY INT, column will be sum 

    (1,1,1,001,100,'01-05-2019',null),
    (2,1,1,001,100,'01-05-2019',null),

    if one item have two qty on same day there total is coming wrong...

    please check this with this data...

    INSERT INTO #Probale VALUES
       (1,1,1,001,100,'01-05-2019',null),
        (2,1,1,001,100,'01-05-2019',null),
          (3,1,1,001,100,'01-06-2019',null)
    , (4,3,1,001,200,'02-07-2019',null)
    , (5,11,1,002,200,'03-08-2019',null)
    , (6,10,1,002,200,'08-08-2019',null)
    , (7,1,1,003,200,'08-08-2019',null)
    , (9,1,1,003,200,'09-09-2019',null)
    , (9,1,1,003,200,'09-09-2019',null)
    , (8,1,1,003,200,'08-08-2019',null);

    Friday, August 2, 2019 8:12 AM
  • User77042963 posted
    CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
     
    INSERT INTO #ItemMasterFile VALUES
      (1,'A',1,100)
    , (2,'B',2,100)
    , (3,'C',3,100)
    , (4,'D',4,100)
    , (5,'e',5,100)
    , (6,'f',6,100)
    , (7,'g',4,100)
    , (8,'h',4,100)
    , (9,'K',2,100)
    , (10,'L',4,100)
    , (11,'M',2,100);
     
    INSERT INTO #Probale VALUES
       (1,1,1,001,100,'2019-5-1',null),
          (1,1,1,001,100,'2019-6-1',null)
    , (2,3,1,001,200,'2019-7-2',null)
    , (3,11,1,002,200,'2019-8-3',null)
    , (4,10,1,002,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null)
    , (4,1,1,003,200,'2019-8-8',null);
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
     
    
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when Convert(char(7),Entrydate,112)='+ quotename( Convert(char(7),Entrydate,112),'''') +' then  1  else null end) as ' + Quotename(Left(datename(month,Entrydate),3)+Right(Year(Entrydate),2),'[')  + char(10)+char(13)
    FROM  (select distinct Entrydate
      from  #ItemMasterFile i 
    left join #Probale p on i.CodeItem=p.CodeItem) t
    --Order by  Entrydate
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    
    
    --print @ColumnHeaders
     
     SET @sql = N'Select CodeItem,Descriptionitem, '
    + @ColumnHeaders 
    +' FROM (select i.CodeItem,i.Descriptionitem, p.Entrydate
      FROM  #ItemMasterFile i 
    LEFT JOIN #Probale p on i.CodeItem=p.CodeItem) t
    Group by CodeItem,Descriptionitem '
     
    --print @sql
     EXEC sp_executesql @sql;
    
    drop table #ItemMasterFile,#Probale

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 2, 2019 4:08 PM