SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/10/2013 :  09:15:15  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/10/2013 :  09:20:39  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/10/2013 :  10:24:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  10:30:04  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/11/2013 :  05:29:37  Show Profile  Reply with Quote
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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2013 :  08:17:46  Show Profile  Reply with Quote
so what should be the output for above data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000