Click here to Skip to main content
15,889,879 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
<pre>(SELECT  distinct g.*, 'Exp. Upto ' + CONVERT(VARCHAR(3), DATEADD (MONTH , -1 ,(CONVERT(varchar(11),'05/01/2018',101))),109) AS 'upto_pretitle',   --cast(A.my_NvarcharColumn as INT) 
	'Exp. During ' + CONVERT(VARCHAR(3), (CONVERT(DATETIME,'05/01/2018',101)),109) AS  'during_monthtitle',isnull(Null,0) as upto_preamt ,isnull(Null,0) as during_monthamt from
	(SELECT e.*   FROM
	(SELECT DISTINCT ddocode,majorheadcode
	,submajorheadcode,minorheadcode,subheadcode,detailedhead,
	soecode, CASE WHEN subsoecode = '00' THEN '' ELSE subsoecode END AS subsoe
	FROM enggbud where amount>0 and finyr='2018-2019' and level='02' ) e inner join 
	(select abc.circle,abc.div_ddocd,abc.div_nm,efg.cir_ddocd,efg.circlenm from 
	(select circle,ddo_cd as div_ddocd,division,nm as div_nm from orgn_stru where ddo_cd is not null and division is not null 
	group by circle,ddo_cd,division,nm)abc left join
	(SELECT DISTINCT ddocode AS cir_ddocd,ddoname as circlenm,circle 
	from ddomaster WHERE ddocode is not null )efg ON abc.circle=efg.circle) f
		ON e.ddocode=f.div_ddocd ) g inner join
		budhead_mas  ON g.majorheadcode=budhead_mas.MAJOR and g.submajorheadcode=budhead_mas.SUB_MAJOR and
		g.minorheadcode=budhead_mas.MINOR and g.subheadcode=budhead_mas.SUB_HEAD and g.detailedhead=budhead_mas.DET_HEAD 
		and g.soecode=budhead_mas.OBJ_CD) 
union all

    SELECT ddo_cd as ddocode,major as majorheadcode,sub_major submajorheadcode,minor minorheadcode,sub_head subheadcode,detailed_head as detailedhead,oc as soecode,soc subsoe,
	SUM(CASE WHEN passingDate >= '04/01/2018'  and passingDate < '05/01/2018' and  status='P' THEN Amount ELSE 0 END)  AS upto_preamt,
    SUM(CASE WHEN passingDate >= '05/01/2018' and passingDate < '06/01/2018' and status='P' THEN Amount ELSE 0 END) AS during_monthamt,
	cast(NULL as varchar(50)) as upto_pretitle, cast(NULL as varchar(50)) as during_monthtitle
	
	 FROM vExpendivision WHERE 
	status='P'  group by 
	major,sub_major,minor,sub_head,detailed_head,oc,soc,ddo_cd


What I have tried:

i want to try join data from two query through union all
Posted
Updated 5-Jun-18 20:02pm

1 solution

SQL
<pre>(SELECT  distinct g.*, 'Exp. Upto ' + CONVERT(VARCHAR(3), DATEADD (MONTH , -1 ,(CONVERT(varchar(11),'05/01/2018',101))),109) AS 'upto_pretitle',   --cast(A.my_NvarcharColumn as INT) 
	'Exp. During ' + CONVERT(VARCHAR(3), (CONVERT(DATETIME,'05/01/2018',101)),109) AS  'during_monthtitle',isnull(Null,0) as upto_preamt ,isnull(Null,0) as during_monthamt from
	(SELECT e.*   FROM
	(SELECT DISTINCT ddocode,majorheadcode
	,submajorheadcode,minorheadcode,subheadcode,detailedhead,
	soecode, CASE WHEN subsoecode = '00' THEN '' ELSE subsoecode END AS subsoe
	FROM enggbud where amount>0 and finyr='2018-2019' and level='02' ) e inner join 
	(select abc.circle,abc.div_ddocd,abc.div_nm,efg.cir_ddocd,efg.circlenm from 
	(select circle,ddo_cd as div_ddocd,division,nm as div_nm from orgn_stru where ddo_cd is not null and division is not null 
	group by circle,ddo_cd,division,nm)abc left join
	(SELECT DISTINCT ddocode AS cir_ddocd,ddoname as circlenm,circle 
	from ddomaster WHERE ddocode is not null )efg ON abc.circle=efg.circle) f
		ON e.ddocode=f.div_ddocd ) g inner join
		budhead_mas  ON g.majorheadcode=budhead_mas.MAJOR and g.submajorheadcode=budhead_mas.SUB_MAJOR and
		g.minorheadcode=budhead_mas.MINOR and g.subheadcode=budhead_mas.SUB_HEAD and g.detailedhead=budhead_mas.DET_HEAD 
		and g.soecode=budhead_mas.OBJ_CD) 
union all

    SELECT ddo_cd as ddocode,major as majorheadcode,sub_major submajorheadcode,minor minorheadcode,sub_head subheadcode,detailed_head as detailedhead,oc as soecode,soc subsoe,
	cast(NULL as varchar(50)) as upto_pretitle, cast(NULL as varchar(50)) as during_monthtitle,
	SUM(CASE WHEN passingDate >= '04/01/2018'  and passingDate < '05/01/2018' and  status='P' THEN Amount ELSE 0 END)  AS upto_preamt,
    SUM(CASE WHEN passingDate >= '05/01/2018' and passingDate < '06/01/2018' and status='P' THEN Amount ELSE 0 END) AS during_monthamt
	
	
	 FROM vExpendivision WHERE 
	status='P'  group by 
	major,sub_major,minor,sub_head,detailed_head,oc,soc,ddo_cd
 
Share this answer
 
Comments
Richard Deeming 6-Jun-18 9:48am    
If you want to edit your question, then click the green "Improve question" link and update your question.

DO NOT post your update as a "solution".

And DO NOT mark your update as the "accepted solution".
Nishant.Chauhan80 7-Jun-18 5:28am    
sorry richard i will remain remember next time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900