Solved

SQL JOIN

Posted on 2016-09-13
22
57 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
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 42

Expert Comment

by:zephyr_hex
Comment Utility
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 29

Expert Comment

by:Olaf Doschke
Comment Utility
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 42

Expert Comment

by:zephyr_hex
Comment Utility
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
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:zephyr_hex
Comment Utility
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 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
Comment Utility
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
Comment Utility
Can you help please?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Expert Comment

by:zephyr_hex
Comment Utility
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
Comment Utility
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
Comment Utility
error attachedScreen-Shot-2016-09-20-at-16.52.47.png
0
 
LVL 42

Accepted Solution

by:
zephyr_hex earned 500 total points
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:zephyr_hex
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:zephyr_hex
Comment Utility
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
Comment Utility
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 42

Assisted Solution

by:zephyr_hex
zephyr_hex earned 500 total points
Comment Utility
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
Comment Utility
Excellent and very helpful
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now