Or help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Or help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-01-03 : 06:38:36

Hey guys

I need some help

I have a situation where the fdmsaccountno, can have multiple productid associated with it

For eg in the query below

Fdmsaccountno can be have the productid 81 and 83,
However instead of counting 81 and 83 twice, i need it to count it once

Any ideas how this can be achieved

My query is as follows

SELECT 'Visa debit',
Count (distinct Dim_Outlet.FDMSAccountNo)AS [Number]
FROM Dim_Outlet
INNER JOIN stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_Product ON stg_FDMS_Card_Entitlements.[Product Code] = Dim_Product.ProductID
where [ProductID] ='81'
or ProductID ='83'
and [Account_Status]='16'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-03 : 06:44:41

SELECT 'Visa debit',
Count (distinct Dim_Outlet.FDMSAccountNo)AS [Number]
FROM Dim_Outlet
INNER JOIN stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_Product ON stg_FDMS_Card_Entitlements.[Product Code] = Dim_Product.ProductID
where
(
[ProductID] ='81'
or ProductID ='83'
)
and [Account_Status]='16'

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-03 : 07:09:52

Probably simpler

where ProductID in ('81','83')
and [Account_Status]='16'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources