SSMS INNER QUERY CONDITIN LOGIC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SSMS INNER QUERY CONDITIN LOGIC

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-01-30 : 05:52:39

HI

I EXECUTED BELOW QUERY

I DID NOT GET THE PROPER OUTPUT

BGISSUED ACTIVE CLOSED
10 3 7
4 1 3

I GOT THE PROPER OUTPUT PER BGISSUED AND AND ACTIVE COLUMN
I DIDNOT GET THE CLOSED COLUMN PROPERLY

ANY BODY KNOWS PLS HELP ME
BELOW ONE IS IS THAT QUERY

select distinct
BGM.CUST_ID,
CMG.CUST_NAME ,
BGM.BG_TYPE,
BGM.CRNCY_CODE ,
a.issued as "Bgs Issued" ,
a.totamt as "Bgs Issued Amt",
b.acitve as"Active BGs",
b.activeamt as"Active Amt",
b1.clsoed "BGs closed",
b1.closedamt " Closed Amt"

from bgm inner join CMG on BGM.CUST_ID =CMG.cust_id

inner join (select COUNT(BGM.BG_SRL_NUM) AS issued ,sum(bgm.bg_amt) as totamt,BGM.CUST_ID,BGM.BG_TYPE from bgm group by BGM.CUST_ID,bgm.BG_TYPE ) a on a.CUST_ID =BGM.CUST_ID AND A.BG_TYPE =BGM.BG_TYPE

inner join (select BGM.CUST_ID,COUNT(BGM.BG_SRL_NUM) as acitve ,sum(bgm.bg_amt) as activeamt ,BGM.BG_TYPE from BGM where bgm.func_code not in('z') group by bgm.CUST_ID,BGM.BG_TYPE) b on b.CUST_ID =a.CUST_ID AND B.BG_TYPE =A.BG_TYPE

inner join (select BGM.CUST_ID,COUNT(bgm.bg_srl_num) as clsoed ,sum(bgm.bg_amt) as closedamt from BGM where bgm.func_code in('z') group by bgm.CUST_ID,BGM.BG_TYPE) b1 on b1.CUST_ID =a.CUST_ID
--where BGM.BG_TYPE='BUYCR'
WHERE BGM.CUST_ID ='10071926' --AND BGM.BG_TYPE='OBGPG'

group by BGM.CUST_ID
,CMG.CUST_NAME
,BGM.BG_TYPE
,a.issued
,BGM.CUST_ID
,a.totamt
,b.activeamt
,b1.clsoed,
b1.closedamt,
b.acitve,
BGM.CRNCY_CODE

CUST_ID CUST_NAME BG_TYPE CRNCY_CODE Bgs Issued Bgs Issued Amt Active BGs Active Amt BGs closed Closed Amt
10071926 CMS INFO SYSTEMS PVT LTD OBGFG INR 6 112500000.0000 6 112500000.0000 1 5000000.0000
10071926 CMS INFO SYSTEMS PVT LTD OBGPG INR 17 145000000.0000 16 140000000.0000 1 5000000.0000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:09:27

when you say you're not getting count properly explain us what you're expecting and also calculation logic for that. or post some sample data and output out it.
Its quite difficult to understand where you're going wrong seeing posted query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources