Help with Derived tables in SELECT

[sconti11]

Below is a simple SELECT statement that I am building which is supposed to include two derived tables...but I believe the syntax is wrong....or something else is messed up somewhere.

SELECT a.SRR_Total, b.WO_Total, b.dateCreated, b.franchiseID
FROM ((select COUNT(srvcReqRefID) as SRR_Total, dateCreated, franchiseeID
from dbo.SVC_REQUEST
group by dateCreated, franchiseeID) as a,
(select COUNT(workorderID) as WO_Total, dateCreated, franchiseID
from dbo.WRK_ORDER
group by dateCreated, franchiseID)as b)
WHERE a.dateCreated = b.dateCreated and a.franchiseeID = b.franchiseID
ORDER BY b.dateCreated ASC

Can someone please show me what I am doing wrong...i have been away from SQL for awhile.

 

[gmmastros]

You have too many parenthesis.

This should work:

SELECT a.SRR_Total, b.WO_Total, b.dateCreated, b.franchiseID
FROM (select COUNT(srvcReqRefID) as SRR_Total, dateCreated, franchiseeID
        from dbo.SVC_REQUEST
        group by dateCreated, franchiseeID) as a,
        (select COUNT(workorderID) as WO_Total, dateCreated, franchiseID
        from dbo.WRK_ORDER
        group by dateCreated, franchiseID)as b
WHERE a.dateCreated = b.dateCreated and a.franchiseeID = b.franchiseID
ORDER BY b.dateCreated ASC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

gmmastros,

Thanks! The result is fine...but I believe that I need to add a UNION or OUTER JOIN in there. The reason is that table b may have more dates than table a....so I am only getting those rows where the dates match.

Rather, I need to see all dates, and then the COUNT values for those that have dates...So it should look something like below:

Date WO_Total SRR_Total
1/1/11 5 2
1/2/11 3 0

The zero value is because there was no date of 1/2/11 in the a table.

Does this make sense?

 

[gmmastros]

Sounds like you want an outer join.

If this were me, I'd probably do a FULL OUTER Join.

SELECT  Coalesce(a.SRR_Total, 0) As SRR_Total,
        Coalesce(b.WO_Total, 0) As WO_Total,
        Coalesce(b.dateCreated, a.dateCreated) As dateCreated,
        Coalesce(b.franchiseID, a.franchiseeID) As franchiseID
FROM   
		(
		select COUNT(srvcReqRefID) as SRR_Total, dateCreated, franchiseeID
        from dbo.SVC_REQUEST
        group by dateCreated, franchiseeID
        ) as a
        Full Outer Join
        (
        select COUNT(workorderID) as WO_Total, dateCreated, franchiseID
        from dbo.WRK_ORDER
        group by dateCreated, franchiseID
        ) as b
        On A.franchiseeID = b.franchiseID
        And A.dateCreated = B.dateCreated

ORDER BY Coalesce(b.dateCreated, a.dateCreated) ASC

If this returns the correct information and you would like me to explain any part of it, just let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

YES!!! That's it!

Please explain the Coalesce statement and how it works for this soution?

BTW...you are the man (or woman)

 

[gmmastros]

Coalesce is simple. Coalesce will return the first parameter (in the list of parameters) that is not null.

Select Coalesce(NULL, NULL, NULL, 'Hello World')
-- This would return Hello World

Since the query uses a full outer join, it's (theoretically) possible that either of the derived tables could be missing the join data. Specifically, the a table data could be missing or the b table data could be missing.

By using coalesce on dateCreated and franchiseId, we are guaranteed to get a value. If you didn't use coalesce, it would be possible to get nulls in either of those columns.

Now... based on your data, it could be that a left join or right join would have worked just fine, but I suspect the difference in performance between a left join and a full join is negligible, and the full join could return better data.

Make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

That does make sense...thanks for the clarification.

So if I wanted to add a parameter within the WHERE clause of the main SELECT statement like:

WHERE dateCreated BETWEEN @startDate AND @endDate

Would this mean that I would have to adjust the Coalesce? Because I have attempted to add the WHERE clause as seen above, and all it will return are those rows where both table have values in the dates selected. I need to see the whole range of dates regardless if table a has a value of that date.

 

[gmmastros]

If you want to filter on a date range, you should probably put it in twice, once for each derived table. Otherwise you may get counts outside of the date range.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

As each derived table is grouped by datecreated, an outer WHERE should be sufficient, but of course with Coalesce, too:

WHERE Coalesce(b.dateCreated, a.dateCreated) BETWEEN @startDate AND @endDate

Bye, Olaf.

 

[markros]

BTW, never heard of a woman named 'George'

PluralSight Learning Library

 

[sconti11]

Ok..so I am making some great progress with my query for reporting through SSRS, but I have small question that pertains to this query.

If I have to add on more derived tables, what is the appropriate syntax?

I mean, I am sure there is a better way of doing this, but if I add another SELECT statment that points to one of the tables already in the query, but is using a different WHERE clause...should I use another FULL OUTER JOIN?

Below is an example of what I am trying to do:

SELECT COALESCE (a.SRR_Total, 0) AS SRR_Total, COALESCE (b.WO_Total, 0) AS WO_Total, COALESCE (c.taskCostTotal, 0) AS Estimated_Total,
COALESCE (b.franchiseID, a.franchiseID, c.franchiseID) AS franchiseID, COALESCE (b.turfID, a.turfID, c.turfID) AS turfID, COALESCE (b.RAAG_Year,
a.RAAG_Year, c.RAAG_Year) AS RAAG_Year
FROM (SELECT COUNT(srvcReqRefID) AS SRR_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID
FROM SVC_REQUEST
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
GROUP BY YEAR(dateCreated), franchiseID, turfID) AS a FULL OUTER JOIN
(SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID
FROM WRK_ORDER
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
GROUP BY YEAR(dateCreated), franchiseID, turfID) AS b ON a.franchiseID = b.franchiseID AND a.RAAG_Year = b.RAAG_Year AND
a.turfID = b.turfID FULL OUTER JOIN
(SELECT COUNT(taskCostTotal) AS Estimated_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID, datePriced
FROM WRK_ORDER AS WRK_ORDER_1
WHERE (datePriced IS NOT NULL) AND (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
GROUP BY YEAR(dateCreated), franchiseID, turfID, datePriced) AS c ON a.franchiseID = c.franchiseID AND a.RAAG_Year = c.RAAG_Year AND
a.turfID = c.turfID

I am currently getting an error that says the new field 'taskCostTotal' is an invalid column name.

 

[gmmastros]

I am currently getting an error that says the new field 'taskCostTotal' is an invalid column name.

When you use derived tables, you should be able to copy/paste just that derived table part in to a new query window and run it without any errors.

What do you get when you run this query:

SELECT     COUNT(taskCostTotal) AS Estimated_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID, datePriced
FROM          WRK_ORDER AS WRK_ORDER_1
WHERE      (datePriced IS NOT NULL) AND (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
GROUP BY YEAR(dateCreated), franchiseID, turfID, datePriced

This would imply that there is a column in the WRK_ORDER table names taskCostTotal. Perhaps you spelled this wrong??? Could it be taskTotalCost instead?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

I ran the same query as you presented within SQL 2005, and got the Invalid Column Name 'taskCostTotal'.

I confirmed that I have the correct spelling.

 

[sconti11]

I am sorry....I was looking at the wrong screen. when I run that single dervied table in SQL I do get back correct results.

So something in the way I am building the multiple joins is causing the error.

 

[gmmastros]

I see the problem now.

SELECT     COALESCE (a.SRR_Total, 0) AS SRR_Total, COALESCE (b.WO_Total, 0) AS WO_Total, COALESCE ([!]c.taskCostTotal[/!], 0) AS Estimated_Total, 
                      COALESCE (b.franchiseID, a.franchiseID, c.franchiseID) AS franchiseID, COALESCE (b.turfID, a.turfID, c.turfID) AS turfID, COALESCE (b.RAAG_Year, 
                      a.RAAG_Year, c.RAAG_Year) AS RAAG_Year
FROM         (SELECT     COUNT(srvcReqRefID) AS SRR_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID
                       FROM          SVC_REQUEST
                       WHERE      (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
                       GROUP BY YEAR(dateCreated), franchiseID, turfID) AS a FULL OUTER JOIN
                          (SELECT     COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID
                            FROM          WRK_ORDER
                            WHERE      (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
                            GROUP BY YEAR(dateCreated), franchiseID, turfID) AS b ON a.franchiseID = b.franchiseID AND a.RAAG_Year = b.RAAG_Year AND 
                      a.turfID = b.turfID FULL OUTER JOIN
                          (SELECT     [!]COUNT(taskCostTotal) AS Estimated_Total[/!], YEAR(dateCreated) AS RAAG_Year, franchiseID, turfID, datePriced
                            FROM          WRK_ORDER AS WRK_ORDER_1
                            WHERE      (datePriced IS NOT NULL) AND (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf)
                            GROUP BY YEAR(dateCreated), franchiseID, turfID, datePriced) AS c ON a.franchiseID = c.franchiseID AND a.RAAG_Year = c.RAAG_Year AND 
                      a.turfID = c.turfID

Your derived table is not returning taskCostTotal. It is returning the count of taskCostTotal and using the name Estimated_Total. This column alias must be used outside of the derived table. so you'll need to change the main select part to use c.Estimated_Total instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

That resolved the error. But I am not sure I understand why??

Because the other derived tables use the COUNT and alias, which in the main select it uses the original column name and then creates another alias.

 

[gmmastros]

Let me try to explain.

The reason this type of query is called a derived table is because you have a query that is "acting" like a table. You need to separate (in your mind) things that happen inside the derived table, and that which happens outside the derived table.

The only parts of the derived table that are accessible outside of it are those parts that you specifically expose (columns and rows). Let me say this again, whatever the inside part of the derived table returns is accessible outside the derived table.

Let's take a look at your query with a lot of the details removed (just to aide in understanding)...

SELECT COALESCE ([red]a.SRR_Total[/red], 0) AS SRR_Total, 
       COALESCE ([blue]b.WO_Total[/blue], 0) AS WO_Total, 
       COALESCE ([purple]c.Estimated_Total[/purple], 0) AS Estimated_Total, 
       COALESCE ([green]b.franchiseID, a.franchiseID, c.franchiseID[/green]) AS franchiseID, 
       COALESCE (b.turfID, a.turfID, c.turfID) AS turfID, 
       COALESCE (b.RAAG_Year, a.RAAG_Year, c.RAAG_Year) AS RAAG_Year
FROM   (
           SELECT   COUNT(srvcReqRefID) AS [red]SRR_Total[/red], 
                    YEAR(dateCreated) AS RAAG_Year, 
                    [green]franchiseID[/green], 
                    turfID
       ) AS a 
       FULL OUTER JOIN
       (
           SELECT   COUNT(workorderID) AS [blue]WO_Total[/blue], 
                    YEAR(dateCreated) AS RAAG_Year, 
                    [green]franchiseID[/green], 
                    turfID
       ) AS b ON (Conditions here)
       FULL OUTER JOIN
       (
           SELECT   COUNT(taskCostTotal) AS [purple]Estimated_Total[/purple], 
                    YEAR(dateCreated) AS RAAG_Year, 
                    [green]franchiseID[/green], 
                    turfID, 
                    datePriced
       ) AS c ON (Conditions Here)

Don't try to run the query above, you'll get more errors than you can shake a stick at. But, look at it closely. The only data we use in the main select part (the top 6 lines) is columns exposed in at least one of the 3 derived tables.

Take a close look at the colored parts. Notice how the colored part in the top/main select (first 6 lines of the query) correspond to the column aliases exposed in the derived tables.

Does this make sense now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

YES!!

This was very helpful. Thanks for taking the time to assist me in this understanding.