Month wise Data Display in sql?

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

Answer 1

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

Answer 2

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);

Answer 3

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