Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL JOIN

Posted on 2016-09-13
22
Medium Priority
?
91 Views
Last Modified: 2016-09-21
Hi

I am trying to join the below 2 codes, using W3Schools INNER JOIN or UNION ALL functions but i cannot seem to get it working.

Code 1
SELECT DATENAME(month,dateoccured) AS 'Month'
    ,COUNT(faultid) AS 'Total Calls'
    ,SUM(CASE WHEN status = '9' THEN 1 ELSE 0 END) AS 'Closed Calls'
    ,SUM(CASE WHEN status != '9' THEN 1 ELSE 0 END) AS 'Opened Calls'
    ,SUM(CASE WHEN datecleared <= fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
    ,SUM(CASE WHEN datecleared > fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'
FROM faults
WHERE section_ = 'Team'
      AND dateoccured>=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))
GROUP BY DATENAME(month,dateoccured) 

Open in new window


Code 2
SELECT SDSectionName AS Section
    ,isnull(cast((
                SELECT round(sum(FResponseTime) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Response (Last 30 Days)'
    ,isnull(cast((
                SELECT round(sum(Elapsedhrs) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Resolution (Last 30 Days)'
FROM SectionDetail O
                        
GROUP BY SDSectionName

Open in new window


Thanks in Advance and Apologies I havent added an example of data.
0
Comment
Question by:Imran Shabir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41796373
UNION requires that the two result sets have the same columns.  JOIN requires that the two tables have an associated column or columns.

If neither of these conditions is true, then you could use a UNION and return NULL for the columns that a particular result set does not have.  For example:

//you need this structure in order to preserve your GROUP BY
SELECT u1.Month, u1.[Total Calls], u1.[Closed Calls], u1.[Opened Calls], u1.[Within SLA], u1.[SLA Breached], NULL AS Section, NULL AS [AVG Response (Last 30 Days)], NULL AS AVG Resolution (Last 30 Days)
FROM (
	SELECT DATENAME(month,dateoccured) AS 'Month'
		,COUNT(faultid) AS 'Total Calls'
		,SUM(CASE WHEN status = '9' THEN 1 ELSE 0 END) AS 'Closed Calls'
		,SUM(CASE WHEN status != '9' THEN 1 ELSE 0 END) AS 'Opened Calls'
		,SUM(CASE WHEN datecleared <= fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
		,SUM(CASE WHEN datecleared > fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'
	FROM faults
	WHERE section_ = 'Team'
		  AND dateoccured>=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
	RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))
	GROUP BY DATENAME(month,dateoccured) 
)u1

UNION ALL

SELECT NULL AS Month, NULL AS [Total Calls], NULL AS [Closed Calls], NULL AS [Opened Calls], NULL AS [Within SLA], NULL AS [SLA Breached], u2.Section, u2.[AVG Response (Last 30 Days)], u2.AVG Resolution (Last 30 Days)
FROM (
	SELECT SDSectionName AS Section
		,isnull(cast((
					SELECT round(sum(FResponseTime) / nullif(count(*), 0), 2)
					FROM Faults
					WHERE STATUS = 9
						AND Sectio_ = O.SDSectionName
						AND datecleared > getdate()-30
						AND datecleared < getdate()
					) AS NVARCHAR(10)), '') AS 'AVG Response (Last 30 Days)'
		,isnull(cast((
					SELECT round(sum(Elapsedhrs) / nullif(count(*), 0), 2)
					FROM Faults
					WHERE STATUS = 9
						AND Sectio_ = O.SDSectionName
						AND datecleared > getdate()-30
						AND datecleared < getdate()
					) AS NVARCHAR(10)), '') AS 'AVG Resolution (Last 30 Days)'
	FROM SectionDetail O
							
	GROUP BY SDSectionName
) u2

Open in new window

0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41796436
Your Code1 has WHERE section_ = 'Team', your Code2 has the SDSectionName AS Section, it seems a very weak link, that suggests LEFT JOIN by that two Sections.

As consequence only SDSectionName='Team' would join with Code1 data and all the rest of Code2 data has no detail data.

It doesn't make sense to me, to UNION data with no common columns, you'll get a wider table which begins with right side being all empty/null and continuing left side empty/null, that's not what I'd call a good table result.

If you can't relate data to each other, there is no sense in neither joining nor unioning (appending) it.

Bye, Olaf.
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41796448
Olaf - I sometimes build a wide result set to use in a report.  So, the result may not make sense when the query is run directly, but the end user never sees that result -- they see the report.  It all depends.  And a good table result from SQL is not always the goal.  Sometimes the goal is to get the data you need into the report in a manner that fits the restrictions of the reporting tool.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41796463
I know such tools, which fortunately evolved to support multiple resultsets as source, but my argument rather is about the sense of joining that data at all. That's not only obvious, if the joined data looks like a checker board pattern, even if I join employees, family, friends and customers names, the list only may be called "people I know". The reason for the join often tells, where to join with what common denominator, and I want to make sh4bbz think about that.

From what I interpret into this it is about performance statstics and indicators for telephony, eg how effective a service center is. You can easily join wrong indicators to correct employees or teams and vice versa. So be very sure you join the right things together, or you ma fire your best team and keep the worst, just because you didn't explain your data model to a database developer and let him do his job...

Bye, Olaf.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41796655
Assuming that the "section_" in query1 and the "SDSectionName" in query2 are the same, it's rather easy to join the two queries.  

Presumably you could also remove the " WHERE section_ = 'Team' " from the second query and group by section_ instead to list all sections so you could join query2.section_ to query1.Section instead of limiting query1 to only the 'Team' section, if you wanted to.


SELECT query2.*, query1.*
FROM (
    SELECT SDSectionName AS Section
        ,isnull(cast((
                    SELECT round(sum(FResponseTime) / nullif(count(*), 0), 2)
                    FROM Faults
                    WHERE STATUS = 9
                        AND Sectio_ = O.SDSectionName
                        AND datecleared > getdate()-30
                        AND datecleared < getdate()
                    ) AS NVARCHAR(10)), '') AS 'AVG Response (Last 30 Days)'
        ,isnull(cast((
                    SELECT round(sum(Elapsedhrs) / nullif(count(*), 0), 2)
                    FROM Faults
                    WHERE STATUS = 9
                        AND Sectio_ = O.SDSectionName
                        AND datecleared > getdate()-30
                        AND datecleared < getdate()
                    ) AS NVARCHAR(10)), '') AS 'AVG Resolution (Last 30 Days)'
    FROM SectionDetail O
    /*WHERE SDSectionName = 'Team'*/
    GROUP BY SDSectionName
) AS query2
LEFT OUTER JOIN (
    SELECT DATENAME(month,dateoccured) AS 'Month'
        ,COUNT(faultid) AS 'Total Calls'
        ,SUM(CASE WHEN status = '9' THEN 1 ELSE 0 END) AS 'Closed Calls'
        ,SUM(CASE WHEN status != '9' THEN 1 ELSE 0 END) AS 'Opened Calls'
        ,SUM(CASE WHEN datecleared <= fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
        ,SUM(CASE WHEN datecleared > fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'
    FROM faults
    WHERE section_ = 'Team'
          AND dateoccured>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
    GROUP BY DATENAME(month,dateoccured)
    /*GROUP BY section_, DATENAME(month,dateoccured)*/
) AS query1 ON query2.Section = 'Team'
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41796695
Yes, true, but since Code1: WHERE section_ = 'Team' focuses on one section - the Team - only, and Code2: GROUP BY SDSectionName only will have one record for 'Team', this looks like a somewhat skewed join, still. Code2 should perhaps be modified to aggregate on the same level of section and months. And then the question remains, what's up with other sections. You answered that, by simply removing the filter for Team.

I still only give the feedback you should think about what you really want to join how and why. If you just want to learn joiny by w3schools, there are much better sites to learn SQL and there are much better test and educational databases, eg Adventureworks.

Bye, Olaf.
0
 

Author Comment

by:Imran Shabir
ID: 41796705
Hi All

Thankyou for the suggestions, i beleive their are an ID field in both but i will run a query tomorrow to see if they are consistant.

Thanks again
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41796768
If there is an ID field, we need to know what the field is called in each table.  We also need to know whether you want to restrict your results to only those where there is an ID match in both tables, or whether you always want all results from one table regardless of whether or not a match is found in the other table.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41796796
Sounds like a plan, but be careful. Don't just look at the numbers, whether there is a similar range of values to join. Many databases use integer ids and then every table has an ID 1,2,3,4..., some get larger, some not, low values are IDs of anything in a database, also what's not matching. How tables are related should be part of the database meta data, often it isn't, only by name conventions.

On top of that, you're creating new data with aggregations in both your code1 and code2 queries, these have no new ID defined, you can only really left/right join with an ID you "inherit" from the ungrouped raw data and that's not aggregated, so actually the group by is a strong indicator of the core value each row is related to in itself and thus also in relation to any other data.

If you don't know your data model and learn sql it's hardly doable to come up with something correct, that already starts with aggregating data in different "dimensions".

Bye, Olaf.
0
 

Author Comment

by:Imran Shabir
ID: 41797553
Hi All

I run a query on 2 tables Faults (column: section_) and SectionDetail (Column: SDSectionName), both have the same values (ie Team names).

Imran (sh4bbz)
0
 

Author Comment

by:Imran Shabir
ID: 41800335
Can you help please?
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41800381
Try this:

SELECT DATENAME(month,f.dateoccured) AS 'Month'
    ,COUNT(f.faultid) AS 'Total Calls'
    ,SUM(CASE WHEN f.status = '9' THEN 1 ELSE 0 END) AS 'Closed Calls'
    ,SUM(CASE WHEN f.status != '9' THEN 1 ELSE 0 END) AS 'Opened Calls'
    ,SUM(CASE WHEN f.datecleared <= f.fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
    ,SUM(CASE WHEN f.datecleared > f.fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'
	,d.[AVG Response (Last 30 Days)]
	,d.[AVG Resolution (Last 30 Days)]
FROM faults f
INNER JOIN (
	SELECT SDSectionName AS Section
    ,isnull(cast((
                SELECT round(sum(FResponseTime) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Response (Last 30 Days)'
    ,isnull(cast((
                SELECT round(sum(Elapsedhrs) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Resolution (Last 30 Days)'
	FROM SectionDetail

) d ON d.Section = f.section_
WHERE f.section_ = 'Team'
      AND f.dateoccured>=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))
GROUP BY DATENAME(month,f.dateoccured),d.[AVG Response (Last 30 Days)],d.[AVG Resolution (Last 30 Days)]

Open in new window

0
 

Author Comment

by:Imran Shabir
ID: 41805229
Hi Zephyr

Thankyou for that, I did try it and it gave me an error. i will rerun the query and add the error.

Imran
0
 

Author Comment

by:Imran Shabir
ID: 41806959
error attachedScreen-Shot-2016-09-20-at-16.52.47.png
0
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 41807099
OK, I see the problem.  The inner query is referencing O.SDSectionName but the table isn't aliased.  Just remove the alias.  Or add the alias to the table.  Here's what it looks like when I've added the alias:

SELECT DATENAME(month,f.dateoccured) AS 'Month'
    ,COUNT(f.faultid) AS 'Total Calls'
    ,SUM(CASE WHEN f.status = '9' THEN 1 ELSE 0 END) AS 'Closed Calls'
    ,SUM(CASE WHEN f.status != '9' THEN 1 ELSE 0 END) AS 'Opened Calls'
    ,SUM(CASE WHEN f.datecleared <= f.fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
    ,SUM(CASE WHEN f.datecleared > f.fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'
	,d.[AVG Response (Last 30 Days)]
	,d.[AVG Resolution (Last 30 Days)]
FROM faults f
INNER JOIN (
	SELECT O.SDSectionName AS Section
    ,isnull(cast((
                SELECT round(sum(FResponseTime) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Response (Last 30 Days)'
    ,isnull(cast((
                SELECT round(sum(Elapsedhrs) / nullif(count(*), 0), 2)
                FROM Faults
                WHERE STATUS = 9
                    AND Sectio_ = O.SDSectionName
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
                ) AS NVARCHAR(10)), '') AS 'AVG Resolution (Last 30 Days)'
	FROM SectionDetail O

) d ON d.Section = f.section_
WHERE f.section_ = 'Team'
      AND f.dateoccured>=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))
GROUP BY DATENAME(month,f.dateoccured),d.[AVG Response (Last 30 Days)],d.[AVG Resolution (Last 30 Days)]

Open in new window

0
 

Author Comment

by:Imran Shabir
ID: 41808279
Hi Zephyr

That worked perfectly, thanks. Can i ask if its possible to change the 2nd and 3rd section (AVG Resolution and Response) to the same date format as in 1st part (below).

1st Part:
    ,SUM(CASE WHEN f.datecleared <= f.fixbydate THEN 1 ELSE 0 END) AS 'Within SLA'
    ,SUM(CASE WHEN f.datecleared > f.fixbydate THEN 1 ELSE 0 END) AS 'SLA Breached'

2nd & 3rd part
                    AND datecleared > getdate()-30
                    AND datecleared < getdate()
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41809009
I'm not sure what you mean.  Are you saying you want the original first query to use the same date range as the original second query?

Or ?
0
 

Author Comment

by:Imran Shabir
ID: 41809021
Hi Zephyr

Apologies I didnt explain it properly, currently the config you helped creating has on lines 17/18 and 25/26 to show the exact last 30 days but i wanted to ask if its possible to change that part by adding the similar config on lines 5/6.

As you can tell the config shows the last 30 days which is inconsistant with between lines 5/6 and 17/18/25/26.

Imran
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41809242
I still don't understand.  The Average is called "Last 30 days".  If you don't want it to average the last 30 days, what do you want it to average?
0
 

Author Comment

by:Imran Shabir
ID: 41809253
Hi Zephyr

The last 30 day count shows 30 days from when the script is run rather than previous months (ie 1st Aug to 31st Aug) 30 days.

Imran
0
 
LVL 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 2000 total points
ID: 41809316
ok, I think I finally understand what you want.

In the original "Code 1" query, the time frame is defined as:

dateoccured>=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))

Open in new window


which would be records where dateoccured >= the first day of last month.

The date range in Code 2 is:

datecleared > getdate()-30 AND datecleared < getdate()

Open in new window


which is the last 30 days.

I'm not sure how datecleared and dateoccured relate to each other, but if I'm understanding correctly, you just want to match up the average in Code 2 with the timeframe in Code 1.  So, you can change the lines with this:

datecleared > getdate()-30 AND datecleared < getdate()

Open in new window


to this:

datecleared >=DATEADD(month,-1,CAST(CAST(DATEPART(year,GETDATE()) AS CHAR(4)) +
RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2) + '01' AS DATE))

Open in new window


However, you should also change the name of your "Last 30 Days" columns because they are no longer the last 30 days.  They will be a variable number of days, depending on how many days exist between today and the first day of last month.
0
 

Author Closing Comment

by:Imran Shabir
ID: 41809751
Excellent and very helpful
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question