SQL Development

Hello -
I am not all a SQL expert but have to create a query which displays data from 2 different databases.

Please see attached image -

I have Database A  Table 1 which lists my applications and their job execution status
I have Database B Table 1 which lists my application availability status
As shown in attachment I need to join each database and table in question for 1 report which shows both application availability along with each applications total failed job count along with availability for past day as opposed to every hour of the day.

The only way I am able to relate both tables is via part of the application name being in the the job_name column of Database A and AppName column of Database B.  For example "App1"  

I have no idea if / how it is possible to join 2 databases where as in this case the common application name not represented the same way in each database.SQL Report
ntgOneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You can just use 3-part naming and join the tables like you would tables in the same database.

SELECT ...
FROM DatabaseA.dbo.Table1 AS AT1
INNER JOIN DatabaseB.dbo.Table1 AS BT1
   ON ...
PortletPaulEE Topic AdvisorCommented:
To join those 2 tables you have to manipulate the [job_name] to get the left part of it only. This requires locating the space after the app name - use CHARINDEX() to do that, then that result is used in the LEFT() function to arrive at "App1" from a job_name of "App1 File Upload". In addition to that you also need to relate the dates. I have assumed those date/datetime columns are NOT strings, but I used CAST(.. AS DATE) to keep just the date and remove "time" e.g. 2015-11-21 13:00:00 becomes just 2015-11-21

So once we have AppName and a simple date only column in both tables we can join them.

Further to this your report shows a COUNT() of failed jobs, so we need to calculate that using GROUP BY. Also, the report shows a single availability number, but the data has many rows per day for availability, so I have chosen to average that availability per day which also requires a calculation done by using GROUP BY.

So. Both tables need GROUP BY and one needs AppName calculated and at least one needs a date-only column (but I have applied the CAST() to both "just in case"). I suggest using GROUP BY as "subqueries" so that the calculations are performed before the join.

Finally, because there might be days where there are no matching records in the job data for an application I suggest using a LEFT OUTER JOIN, so the application will still be listed and the failed jobs shown as zero.

phew, attempting to explain it was harder than the query itself
SELECT
      avail.AppName
    , avail.todays_date
    , ISNULL(jobs.failed_jobs, 0) AS failed_jobs
FROM (
      SELECT
            AppName
          , CAST(todays_date AS date) AS todays_date
          , AVG(Availability) AS Availability
      FROM DatabaseB.dbo.Table1
      GROUP BY
            AppName
          , CAST(todays_date AS date)
) AS avail
      LEFT OUTER JOIN (
            SELECT
                  LEFT(job_name, CHARINDEX(' ', job_name + ' ') - 1) AS AppName
                , CAST(todays_date AS date) AS todays_date
                , COUNT(CASE WHEN finalsts_txt = 'fail' THEN finalsts_txt END) AS failed_jobs
            FROM DatabaseA.dbo.Table1
            GROUP BY
                  LEFT(job_name, CHARINDEX(' ', job_name + ' ') - 1)
      ) AS jobs ON avail.AppName = jobs.AppName
                  AND avail.todays_date = jobs.todays_date

Open in new window

{+edit}
I have not attempted to show any WHERE conditions. The report image does suggest you only want "Past Day" but I wasn't too sure if that was all you wanted.

If you really only want "yesterday" (= "Past Day") then:

in BOTH subqueries, after the FROM line include where clauses like this:

      FROM DatabaseB.dbo.Table1 WHERE todays_date >= dateadd(day,-1,cast(getdate() as date)) and todays_date < cast(getdate() as date)

            FROM DatabaseA.dbo.Table1 WHERE  todays_date >= dateadd(day,-1,cast(getdate() as date)) and todays_date < cast(getdate() as date)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ntgOneAuthor Commented:
Thank you Paul  - this is outstanding.  I will give it a try this weekend / monday.

Regards
Rich
Vitor MontalvãoMSSQL Senior EngineerCommented:
ntgOne, do you still need help on this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.