sql experts please come

[4Vikram]

17 minutes ago, JollyBoy said:

nuv rasina query ikada vey.. sample columns and update column

select A.[MWC_ID]
      ,A.[CATEGORY_1]
      ,A.[CATEGORY_2]
      ,A.[STRING_VALUE1]
      ,A.[STRING_VALUE2]
      ,STATE_ID = ROW_NUMBER() over (partition by A.[STRING_VALUE1] order by A.[STRING_VALUE1]) 
FROM [tbl_APP_DROPDOWN_VALUES] A
WHERE A.[CATEGORY_1] = 'State'
--AND A.[STRING_VALUE1] = 1
AND   A.[CATEGORY_2] = 'County'

 

my values are like this 

 

MWC_ID    CATEGORY_1    CATEGORY_2    STRING_VALUE1    STRING_VALUE2    STATE_ID
1    State    County    Alabama     Autauga County    1
2    State    County    Alabama     Baldwin County    2
3    State    County    Alabama     Barbour County    3

 

But Sate_ID should get values as 1,1,1 nduku ante State_ID value should be 1 to each state

[JollyBoy]

 

6 hours ago, 4Vikram said:

select A.[MWC_ID]
      ,A.[CATEGORY_1]
      ,A.[CATEGORY_2]
      ,A.[STRING_VALUE1]
      ,A.[STRING_VALUE2]
      ,STATE_ID = ROW_NUMBER() over (partition by A.[STRING_VALUE1] order by A.[STRING_VALUE1]) 
FROM [tbl_APP_DROPDOWN_VALUES] A
WHERE A.[CATEGORY_1] = 'State'
--AND A.[STRING_VALUE1] = 1
AND   A.[CATEGORY_2] = 'County'

 

my values are like this 

 

MWC_ID    CATEGORY_1    CATEGORY_2    STRING_VALUE1    STRING_VALUE2    STATE_ID
1    State    County    Alabama     Autauga County    1
2    State    County    Alabama     Baldwin County    2
3    State    County    Alabama     Barbour County    3

 

But Sate_ID should get values as 1,1,1 nduku ante State_ID value should be 1 to each state

;with SS  as

(select 
      ,A.[STRING_VALUE1],STATE_ID
          , ROW_NUMBER() over (partition by A.[STRING_VALUE1] order by A.[STRING_VALUE1])  RN 
FROM [tbl_APP_DROPDOWN_VALUES] ) 

UPDATE  T SET T.STATE_ID = S.RN FROM [tbl_APP_DROPDOWN_VALUES] T inner join  SS S on S.[STRING_VALUE1] = T.[STRING_VALUE1]

[jailokesh]

bhayya another way is having state dimension table and calling it 

DIM_State

Masterstate Number, StateName, StateCode, County, Zip Code, DataStamp, Stamp. 

masterstateNumber will be unique for a given state.

as it will be static table you can use in multiple queries.

 

[4Vikram]

5 hours ago, JollyBoy said:

 

;with SS  as

(select 
      ,A.[STRING_VALUE1],STATE_ID
          , ROW_NUMBER() over (partition by A.[STRING_VALUE1] order by A.[STRING_VALUE1])  RN 
FROM [tbl_APP_DROPDOWN_VALUES] ) 

UPDATE  T SET T.STATE_ID = S.RN FROM [tbl_APP_DROPDOWN_VALUES] T inner join  SS  on S.[STRING_VALUE1] = T.[STRING_VALUE1]

Bro thanks for this but I used another it worked as well... Will use this also and see again... Vere id through pm chesta respond to that... 

[JollyBoy]

2 minutes ago, 4Vikram said:

Bro thanks for this but I used another it worked as well... Will use this also and see again... Vere id through pm chesta respond to that... 

vokay

[4Vikram]

20 minutes ago, JollyBoy said:

vokay

pmed check it bro

[riashli]

5 hours ago, JollyBoy said:

 

;with SS  as

(select 
      ,A.[STRING_VALUE1],STATE_ID
          , ROW_NUMBER() over (partition by A.[STRING_VALUE1] order by A.[STRING_VALUE1])  RN 
FROM [tbl_APP_DROPDOWN_VALUES] ) 

UPDATE  T SET T.STATE_ID = S.RN FROM [tbl_APP_DROPDOWN_VALUES] T inner join  SS  on S.[STRING_VALUE1] = T.[STRING_VALUE1]

[shamsher_007]

Just now, riashli said:

y hitting re @riashli

[JollyBoy]

Just now, riashli said:

names changed when. know i dont 

[JollyBoy]

Just now, shamsher_007 said:

y hitting re @riashli

querry tapu rasa anta mastaru 

[4Vikram]

5 minutes ago, riashli said:

vunty laang tame no see how you?

[4Vikram]

4 minutes ago, JollyBoy said:

querry tapu rasa anta mastaru 

@riashli vunty ki sql kuda asthada 

[shamsher_007]

2 minutes ago, JollyBoy said:

querry tapu rasa anta mastaru 

aythe okay 

[JollyBoy]

Just now, 4Vikram said:

@riashli vunty ki sql kuda asthada 

yes.. sarigga cheyak pote tholu teestadi.. my manager oncch up on a tym 

[Bangaruu]

10 minutes ago, riashli said:

Miss u da vaishu