Sql Query's Doubt

[shivashankara]

Calling SQL Experts

EE below query chusi em functionality chestadi ani anukuntunaru?
idi sql lo undi nenu description rayali business level lo (like sql to english)

[b]-- Setting PROMO REDEEMED to be INSERTED (New Redemptions)
UPDATE a
SET a.Process_Status_Flag = 4
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
INNER JOIN
(SELECT
a.Vehicle_Promo_Redeemed_ID,
MAX(a.audit_vehicle_promo_redeemed_id) 'Max_Vehicle_Promo_Redeemed_ID'
FROM
Source_Audit_Vehicle_Promo_Redeemed_Tb a
LEFT OUTER JOIN
JLI_Marketing_Warehouse..Marketing_Promotion_Redeemed_Tb b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID
WHERE
b.Vehicle_Promo_Redeemed_ID IS NULL AND
a.Process_Status_Flag IN(1,2)
GROUP BY
a.Vehicle_Promo_Redeemed_ID) b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID AND
a.Audit_Vehicle_Promo_Redeemed_ID = b.Max_Vehicle_Promo_Redeemed_ID AND
a.Process_Status_Flag IN (1,2)[/b]

[b]---Set DUPLICATE status on associated audit records with the above INSERTED.[/b]
[b]UPDATE a
SET a.Process_Status_Flag = 3
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
a.Audit_Vehicle_Promo_Redeemed_ID IN
(SELECT
a.Audit_Vehicle_Promo_Redeemed_ID
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
Vehicle_Promo_Redeemed_ID IN
(SELECT
a.Vehicle_Promo_Redeemed_ID
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
a.Process_Status_Flag = 4) AND
a.Process_Status_Flag IN (1,2))[/b]

[b]Help me!![/b]

[shivashankara]

help

[bikki]

ltt

[ChampakDas]

Just by looking at it. The first query sets the Flag to 4(whatever it is!) on the records whose Promo_ Redeemed is empty and its status is in 1 and 2. Based on the above records whose status is 4 it is changing to 3 on some condition. Its hard to read and tough to understand unless we see the table structure and some data.

[ghazni]

first analyze the query in innerjoin............. than think what the outer query is doing........

[chelsea]

Help

[ChampakDas]

Instead of looking at the whole query. Break it into pieces like just run below query and see the results. Then the inner join..atleast this would give you some picture of what is happening.
[b]SELECT
a.Vehicle_Promo_Redeemed_ID,
MAX(a.audit_vehicle_promo_redeemed_id) 'Max_Vehicle_Promo_Redeemed_ID'
FROM
Source_Audit_Vehicle_Promo_Redeemed_Tb a
LEFT OUTER JOIN
JLI_Marketing_Warehouse..Marketing_Promotion_Redeemed_Tb b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID
WHERE
b.Vehicle_Promo_Redeemed_ID IS NULL AND
a.Process_Status_Flag IN(1,2)
GROUP BY
a.Vehicle_Promo_Redeemed_ID[/b]

[shivashankara]

[quote name='ChampakDas' timestamp='1343923106' post='1302244548']
Instead of looking at the whole query. Break it into pieces like just run below query and see the results. Then the inner join..atleast this would give you some picture of what is happening.
[b]SELECT
a.Vehicle_Promo_Redeemed_ID,
MAX(a.audit_vehicle_promo_redeemed_id) 'Max_Vehicle_Promo_Redeemed_ID'
FROM
Source_Audit_Vehicle_Promo_Redeemed_Tb a
LEFT OUTER JOIN
JLI_Marketing_Warehouse..Marketing_Promotion_Redeemed_Tb b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID
WHERE
b.Vehicle_Promo_Redeemed_ID IS NULL AND
a.Process_Status_Flag IN(1,2)
GROUP BY
a.Vehicle_Promo_Redeemed_ID[/b]
[/quote]

result chudataniki naku database access ledu
just ardam chesukoni functionality rayali nenu.

[9Pardhu]

As champak das said do it.
Then you get the results...
Analyse the results and make a note of the functionality

Read the business requirement document
or the database documents to get more info regarding the process

[ghazni]

[quote name='shivashankara' timestamp='1343923285' post='1302244568']
result chudataniki naku database access ledu
just ardam chesukoni functionality rayali nenu.
[/quote]
idid mari darunam DB access leka povadam endi.......

[shivashankara]

[quote name='ghazni' timestamp='1343923646' post='1302244611']
idid mari darunam DB access leka povadam endi.......
[/quote]
db access inka raledu,

konni tasks unnayi vati functionality rayamani ichinaru...ivala atleast oka package aina complete cheyali
andukay query ikkada paste chestunna, avaraina telsina vallu ahaa query ardam chesukoni functionality cheptaru kada aday nenu radam ani ikkada post chesina bhayya.

[shivashankara]

help

[king420]

. konni tables lo nunchi konni records select chesi Table update cheyadaniki query [img]http://lh3.ggpht.com/-6_28-c8Y3Rs/T-eJv1xAS5I/AAAAAAAAHCE/Cptie-zYgkc/s150/PK-61.gif[/img]

[cinema pichodu]

[quote name='shivashankara' timestamp='1343922399' post='1302244456']
Calling SQL Experts

EE below query chusi em functionality chestadi ani anukuntunaru?
idi sql lo undi nenu description rayali business level lo (like sql to english)

[b]-- Setting PROMO REDEEMED to be INSERTED (New Redemptions)
UPDATE a
SET a.Process_Status_Flag = 4
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
INNER JOIN
(SELECT
a.Vehicle_Promo_Redeemed_ID,
MAX(a.audit_vehicle_promo_redeemed_id) 'Max_Vehicle_Promo_Redeemed_ID'
FROM
Source_Audit_Vehicle_Promo_Redeemed_Tb a
LEFT OUTER JOIN
JLI_Marketing_Warehouse..Marketing_Promotion_Redeemed_Tb b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID
WHERE
b.Vehicle_Promo_Redeemed_ID IS NULL AND
a.Process_Status_Flag IN(1,2)
GROUP BY
a.Vehicle_Promo_Redeemed_ID) b
ON
a.Vehicle_Promo_Redeemed_ID = b.Vehicle_Promo_Redeemed_ID AND
a.Audit_Vehicle_Promo_Redeemed_ID = b.Max_Vehicle_Promo_Redeemed_ID AND
a.Process_Status_Flag IN (1,2)[/b]

[b]---Set DUPLICATE status on associated audit records with the above INSERTED.[/b]
[b]UPDATE a
SET a.Process_Status_Flag = 3
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
a.Audit_Vehicle_Promo_Redeemed_ID IN
(SELECT
a.Audit_Vehicle_Promo_Redeemed_ID
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
Vehicle_Promo_Redeemed_ID IN
(SELECT
a.Vehicle_Promo_Redeemed_ID
FROM
JLI_Marketing_Staging..Source_Audit_Vehicle_Promo_Redeemed_Tb a
WHERE
a.Process_Status_Flag = 4) AND
a.Process_Status_Flag IN (1,2))[/b]

[b]Help me!![/b]
[/quote]
[b]-- Setting PROMO REDEEMED to be INSERTED (New Redemptions)[/b]
[b]---Set DUPLICATE status on associated audit records with the above INSERTED. [/b]

[ChampakDas]

I am getting the same thing.
The first query is used to retrive all the records whose status is 1/2 and whose doesnt have redeemedId and those are updated to 4.
Now, based on the above result for those records whose statusid is still 1/2 those are updated with 3.

if this wasn't any useful.

[quote name='ChampakDas' timestamp='1343922855' post='1302244513']
Just by looking at it. The first query sets the Flag to 4(whatever it is!) on the records whose Promo_ Redeemed is empty and its status is in 1 and 2. Based on the above records whose status is 4 it is changing to 3 on some condition. Its hard to read and tough to understand unless we see the table structure and some data.
[/quote]