Urgent Help Data from different tables.

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

Urgent Help Data from different tables.

Author

Topic

Ninjataktikz
Starting Member

3 Posts

Posted - 2011-07-14 : 13:28:09

Hi Guys,
Just joined today I was looking for help online could not find it so thought I would post here.

Here is the situation. I been assigned to project where I work with Pentaho Reporting. The queries are done in SQL.

Basic Info : 2 Tables adv_detail, adv_summary

New Tables : vdat_2874, vdat_2992 these will be replacing the adv_detail table

Objective : Create a Migration date to pull data from 2 different tables vdat_2992 and vdat_2874.

Data : Data I am suppose to pull is CTS (Clicks to Site)

What I have done so far:

I made a union with vdat_2992 and vdat_2874 since both of them have extacly same columns.

(
      SELECT * FROM vdat_2874 WHERE increment_day >= '2010-06-01'
      UNION
      SELECT * FROM vdat_2992 WHERE increment_day <= '2010-06-01'
       )

Problem : Usually the data is pulled from adv_summary(vdat_2449) table and we have a column called impressions. So what we do is get the totalCTS and divide that by impressions to get the CTSR(Click to site rate). Now the objective is to get the CTSR but this time it has to pull CTS from vdat_2874 before 2010-06-01 and then pull the CTS from vdat_2992 after 2010-06-01 then add both of them together to get the CTS rate and dived that by impressions to get the CTSR.

Now this is the code that I came up with to get the CTS

SUM ( 
       IF ( INCREMENT_DAY < 'MigrationDate' 
             ,SUM(vdat_2992.counts where eventtype="cta")
             ,SUM(vdat_2449.clicktositeindicator)
          )
    ) as totalCTS

counts = CTS(Click to sites)
clicktositeindicator = Also CTS just named differently.

I keep getting errors that tables are not found but they are all in the database. Once I get the data and the totalCTS is complete I will need to go into Pentaho and use the formula there to get the totalCTS. Any help is appreciated. If you need more info please ask. I know this is little bit confusing. I am really confused to.

Thank You

Gary

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-14 : 18:12:07

That's pretty confusing!

Maybe this

SUM (CASE WHEN INCREMENT_DAY < MigrationDate and
THEN CASE WHEN eventType = 'CTA'
THEN vdat_2992.counts
ELSE vdat_2449.Counts
END
END
)as TotaLCTS

vdat_2992 and vdat_2449 must appear in the FROM clause of your query. I think the way you're going about this you need to add a column 'Source' to your union table, making the value the name if the table
SELECT 'vdat_2992' as Source, etc, and then reference that in your update query. There's a lot of miles to go to get this query right. The more info you provide, the better we can help you.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources