SQL Experts

[4Vikram]

I have a table which has 4 columns in which fourth column is cumulative of first three and I am joining it with another table for referring. 

Example:

Table Alpha

ID     S1    S2    S3    STotal    DateID

1       1       2       0       3           June4

 

Table Beta has three columns

ID    MonthID           DateID

1     0                    Jan1

2     1                     Feb2

3     2                     March3

4     3                    April4

 

So I join this way, 

Select A*, B*

FROM Alpha A

Join Beta B on A.ID = B.ID and A.DateID = B.DateID

 

So what I want is a case statement where if I join I want to see the DateId wise rows with my S1, S2, S3 as 0 and my STotal should get cumulative of all S1 to S3. Like this

 

ID   SColumns    MonthID 

1   S1 as 0             Jan1

1   S2  as 0            Feb2

1   S3   as 0           March3

1   STotal as 3      April4

So the S1, S2, S3 are recurring, they will be same for all ID's and the MonthID is associated with S columns. Like S1 is for Jan1 MonthID like wise..

So what I am looking is if MonthID is Jan1 then S1 be 0, MonthID is Feb2 S2 be 0, MonthID is March3 then it be 0 as well but finally my STotal should be 3 (Like Cumulative)

I need a case statement but I donno how to write with two tables joined. Please help thanks

[sandie]

Table A lo date column ni

table B lo month numeric batti join cheiala

[4Vikram]

2 minutes ago, sandie said:

Table A lo date column ni

table B lo month numeric batti join cheiala

update sesna sudu bujji

[sandie]

Case lo 

if alpha.id= beta.id and alpha.dateid = beta.dateid then stotal else 0

[xano917]

14 minutes ago, 4Vikram said:

I have a table which has 4 columns in which fourth column is cumulative of first three and I am joining it with another table for referring. 

Example:

Table Alpha

ID     S1    S2    S3    STotal    DateID

1       1       2       0       3           June4

 

Table Beta has three columns

ID    MonthID           DateID

1     0                    Jan1

2     1                     Feb2

3     2                     March3

4     3                    April4

 

So I join this way, 

Select A*, B*

FROM Alpha A

Join Beta B on A.ID = B.ID and A.DateID = B.DateID

 

So what I want is a case statement where if I join I want to see the DateId wise rows with my S1, S2, S3 as 0 and my STotal should get cumulative of all S1 to S3. Like this

 

ID   SColumns    MonthID 

1   S1 as 0             Jan1

1   S2  as 0            Feb2

1   S3   as 0           March3

1   STotal as 3      April4

So the S1, S2, S3 are recurring, they will be same for all ID's and the MonthID is associated with S columns. Like S1 is for Jan1 MonthID like wise..

So what I am looking is if MonthID is Jan1 then S1 be 0, MonthID is Feb2 S2 be 0, MonthID is March3 then it be 0 as well but finally my STotal should be 3 (Like Cumulative)

I need a case statement but I donno how to write with two tables joined. Please help thanks

Use temp tables for simplicity or staging tables . Single query lone cheyyali ante performance debba 

[4Vikram]

3 minutes ago, sandie said:

Case lo 

if alpha.id= beta.id and alpha.dateid = beta.dateid then stotal else 0

let me try this

3 minutes ago, xano917 said:

Use temp tables for simplicity or staging tables . Single query lone cheyyali ante performance debba 

temp ante? do I need to put the output from both tables into temp first and then retrieve to modify?

[sandie]

Just now, 4Vikram said:

let me try this

temp ante? do I need to put the output from both tables into temp first and then retrieve to modify?

Volatile table sample format ani check cheyye. Complex queries aithe tempory ga dhantlo load cheskuni. Condition through final select rastharu..

[CheGuevara]

select A.ID,

case when dateid = Jan1 then 'S1 as 0' 

          when dateid = Feb2 then 'S2 as 0'

           when dateid = Mar3 then 'S3 as 0'

            when dateid = Apr4 then 'STotal as '||A.STotal

end SColumns,

A.DateID from 

Alpha A

Join Beta B on A.ID = B.ID and A.DateID = B.DateID;

[4Vikram]

8 minutes ago, sandie said:

Volatile table sample format ani check cheyye. Complex queries aithe tempory ga dhantlo load cheskuni. Condition through final select rastharu..

will check 

6 minutes ago, CheGuevara said:

select A.ID,

case when dateid = Jan1 then 'S1 as 0' 

          when dateid = Feb2 then 'S2 as 0'

           when dateid = Mar3 then 'S3 as 0'

            when dateid = Apr4 then 'STotal as '||A.STotal

end SColumns,

A.DateID from 

Alpha A

Join Beta B on A.ID = B.ID and A.DateID = B.DateID;

try this one

[karthikn]

4 minutes ago, 4Vikram said:

will check 

try this one

http://sqlfiddle.com/ use to quickly build schema and data so that you can play with query.

[ranku_mogudu]

4 hours ago, sandie said:

Volatile table sample format ani check cheyye. Complex queries aithe tempory ga dhantlo load cheskuni. Condition through final select rastharu..

If using volatile( assuming TD) the  please create stats on key columns

[4Vikram]

ltt