Update 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

Update Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-10 : 09:15:15

Hey guys

I need some help (yet again)

Aim – identify whether an fdmsaccountno, has an LGB_Status as “Accepted” , if so look at the highest level “Corp_Chain_no” and populate every Fdmsaccountno, underneath that Corp_chain_no as LGB_Status as “Accepted”

Eg

FDMSAccountNo Chain_Chain_No Corp_Chain_No LBG_Status
878124314880 878124300889 878234145885 Accepted
878124900886 878124900886 878234145885 N/A

My query is

SELECT
[FDMSAccountNo]
,[Chain_Chain_No]
,[Corp_Chain_No]
,[LBG_Status]
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878234145885'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 09:20:39

something like


SELECT 
[FDMSAccountNo] 
,[Chain_Chain_No]
,[Corp_Chain_No]
,[LBG_Status],
CASE WHEN SUM(CASE WHEN LBG_Status = 'Accepted' THEN 1 ELSE 0 END) OVER (PARTITION BY Corp_Chain_No) > 0 THEN 'Has Accepted' ELSE 'Not yet Accepted' END
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878234145885'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-10 : 10:24:21

Hi

Thank you for your response,
After reviewing your query and looking and the final output, its not providing the correct data in all instances

For instance and [Corp_Chain_No] with the value of ‘000000000000’ automatically defaults to “Has Accepted” when in fact it should be 'Not yet Accepted'
Is there anyway i can counter this into the equation ?

Regards
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 10:30:04

quote:
Originally posted by masond

Hi

Thank you for your response,
After reviewing your query and looking and the final output, its not providing the correct data in all instances

For instance and [Corp_Chain_No] with the value of ‘000000000000’ automatically defaults to “Has Accepted” when in fact it should be 'Not yet Accepted'
Is there anyway i can counter this into the equation ?

Regards
D

you've not even shown us how data is existing for value 000000000000
then how do you think I can account for that
Post proper data to cover all your scenarios and then I may be able to provide solution which exactly suits your requirement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-11 : 05:29:37

HI visakh16

Thank you for your response,
In regards to your latest post, i completely forget about the other scenarios

Fdmsaccountno – Lowest level
[Chain_Chain_No] - Mid Level
[Corp_Chain_No] - Highest Level
LBG_Status, can be flagged at any of these levels,

for example 1
[LBG_Status]can be flagged at fdmsaccountno, as there is no [Chain_Chain_No] or [Corp_Chain_No]the new column only needs to be flagged for that single fdmsaccountno

fdmsaccountno, 295268
[Chain_Chain_No] 00000000
[Corp_Chain_No] 00000000

for example 2
[LBG_Status] can be flagged at fdmsaccountno, but has a [Chain_Chain_No] but no [Corp_Chain_No]the new column only needs to be identify the [Chain_Chain_No] and populate all the fdmsaccountno, below the [Chain_Chain_No]
fdmsaccountno, 295268
[Chain_Chain_No] 999999
[Corp_Chain_No] 00000000

for example 2
[LBG_Status] can be flagged at fdmsaccountno, but has a [Chain_Chain_No] and [Corp_Chain_No]the new column only needs to be identify the [Corp_Chain_No]and populate all the fdmsaccountno, below the [Corp_Chain_No]
fdmsaccountno, 295268
[Chain_Chain_No] 999999
[Corp_Chain_No] 8888888

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:17:46

so what should be the output for above data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources