SQL

« first day (1732 days earlier) ← previous day

ARr0w

4:58 AM

hello folks

3 hours later…

Andy K

8:22 AM

morning

→ 4 messages moved to friendly bin

@Alex , @War is very very knowledgeable on EF

Shaneis

9:11 AM

Morning @AndyK, @ARr0w

Andy K

hey Shaneis

ARr0w

9:51 AM

hey shaneis

morning to both of you and Hello Andy k

2 hours later…

ARr0w

11:49 AM

@Shaneis

if row exists : BILL = 'YES'

if row not exists : BILL = 'NO'

this is what i have wrote down:

Select (select
F.CNIC as CNIC,
M.MOBILE_NO as [Mobile No.],
F.INTERNAL_AMT_CR as [Bill Amount]
from CORE_BNK_FINCIALS F
inner join CORE_BNK_DR_CNF C on F.ACT_ID = C.ACT_ID
inner join PARTNER_BILLING_INFO M on M.ACT_NO = F.ACT_ID
Where C.ALT_ACCOUNT_ID != '') as BILLED = 'Yes'

Shaneis

@ARr0w What DB is this @ARr0w

ARr0w

This is the result im seeking

CNIC , [Mobile No], [Bill Amount], [Billed]
30123131, 030123213, 500.00 , 'YES'
30123132, 012332423, 500.00 , 'NO'

its SQL SERVER

if row exists in table (partner_billing_info) : bill = 'yes'
if row not exists in table (partner_billing_info : bill = 'no'

Shaneis

I think you're looking for a mix of EXISTS and a CASE statement man, try this

SELECT  CASE WHEN EXISTS ( SELECT   *
                           FROM CORE_BNK_FINCIALS AS F
                                INNER JOIN CORE_BNK_DR_CNF AS C
                                    ON F.ACT_ID = C.ACT_ID
                                INNER JOIN PARTNER_BILLING_INFO AS M
                                    ON M.ACT_NO = F.ACT_ID
                           WHERE    C.ALT_ACCOUNT_ID != '' ) THEN 'Yes'
             ELSE 'No'
        END

ARr0w

12:05 PM

yeah, it is returning just yes and no.

i also need three columns.

and plus, i am unable to give alias to its column of this result.
i tried
'as Billed' but it gives me an error that it is expecting syntax such as : OR, AND, THEN

Shaneis

Wait, what's your query script @ARr0w?

Sounds like you want this check inside the script that you gave me, not outside it

ARr0w

Select
F.CNIC as CNIC,
M.MOBILE_NO as [Mobile No.],
F.INTERNAL_AMT_CR as [Bill Amount]
from CORE_BNK_FINCIALS F
inner join CORE_BNK_DR_CNF C on F.ACT_ID = C.ACT_ID
inner join PARTNER_BILLING_INFO M on M.ACT_NO = F.ACT_ID
Where C.ALT_ACCOUNT_ID != ''
AND
NOT EXISTS (Select 1 from PARTNER_BILLING_INFO inner join CORE_BNK_FINCIALS on M.ACT_NO = C.ACT_ID )

i want both the data that exists or don't in table 'PARTNER_BILLING_INFO'

on the basis of act_no in 'PARTNER_BILLING_INFO'
Act_no is named as ACT_ID in other two tables.

--------------
CREATE TABLE [dbo].[PARTNER_BILLING_INFO](
[TXN_ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[MOBILE_NO] [varchar](20) NULL,
[CNIC] [varchar](20) NULL,
[CUST_NM] [varchar](200) NULL,
[ACT_NO] [varchar](25) NULL,
[PKG] [varchar](20) NULL,
[BILL_AMT] [decimal](18, 2) NULL,
[PRJCT_SK] [int] NULL,
[BILL_DT_TM] [datetime] NULL,
[BATCH_SK] [int] NULL,

this is the table query.

ARr0w

12:24 PM

Select
F.CNIC as CNIC,
M.MOBILE_NO as [Mobile No.],
F.INTERNAL_AMT_CR as [Bill Amount],
[Billed] = ( SELECT CASE WHEN EXISTS ( SELECT *
FROM PARTNER_BILLING_INFO AS M
INNER JOIN CORE_BNK_DR_CNF AS C
ON M.ACT_NO = C.ACT_ID
INNER JOIN CORE_BNK_FINCIALS AS F
ON M.ACT_NO = F.ACT_ID
WHERE C.ALT_ACCOUNT_ID != '') THEN 'Yes'
ELSE 'No'
END)

from CORE_BNK_FINCIALS F
inner join CORE_BNK_DR_CNF C on F.ACT_ID = C.ACT_ID
inner join PARTNER_BILLING_INFO M on M.ACT_NO = F.ACT_ID
Where C.ALT_ACCOUNT_ID != ''

this is what i did to your given query. It has given me the column with the result :P

now i need the data that don't exists. :D

Shaneis

12:42 PM

Okay, sorry for taking this slow, I just don't know the tables.
So you want the F.CNIC, the M.MOBILE_NO, the F.INTERNAL_AMT_CR from the CORE_BNK_FINCIALS, and CORE_BNK_DR_CNF
Where an entry exists in PARTNER_BILLING_INFO and where an entry doesn't exists in PARTNER_BILLING_INFO?

ARr0w

yes

Shaneis

@ARr0w ... doesn't that final sentence mean that nothing will show up? I mean, nothing can exist and not exist in the same table. It either does and it doesn't, right?

I still think I'm getting this wrong for you @ARr0w

ARr0w

i must be making some mistake.

but you got the scenario correctly.

@Shaneis <-- This is the scenario.

Shaneis

This?

SELECT CNIC = F.CNIC
	, [Mobile No.] = M.MOBILE_NO
	, [Bill Amount] = F.INTERNAL_AMT_CR
	, [Billed] = CASE WHEN M.ACT_NO IS NULL THEN 'NO' WHEN M.ACT_NO IS NOT NULL THEN 'YES' ELSE 'ERROR' END
FROM CORE_BNK_FINCIALS AS F
	INNER JOIN CORE_BNK_DR_CNF AS C
		ON F.ACT_ID = C.ACT_ID
	LEFT JOIN PARTNER_BILLING_INFO AS M
		ON M.ACT_NO = F.ACT_ID
WHERE C.ALT_ACCOUNT_ID != ''

Alex

Morning, all

Shaneis

12:52 PM

hey @Alex

ARr0w

hi alex.
Shaneis, close to the result. Let me try to work on it a little to make it understable for you too, in case if i am unable to resolve this problem.

Shaneis

@ARr0w okay, best o' luck and let me know

ARr0w

1:54 PM

@Shaneis
mission accomplished.

Select Convert(varchar(50), F.ACT_ID), F.CNIC, F.Internal_Amt_cr,
[Billed] = CASE WHEN C.ACT_ID IS NULL THEN 'NO' WHEN C.ACT_ID IS NOT NULL THEN 'YES' ELSE 'ERROR' END
from CORE_BNK_FINCIALS F
left join CORE_BNK_DR_CNF C on f.ACT_ID = C.ACT_ID

Shaneis

@ARr0w Always happy when you figure something out @ARr0w

well done! :D

Andy K

2:07 PM

back