June 21, 2012 at 11:18 am
I've the two below queries which I've identified as 1 and 2. All I want is to get the 'Denied Customers Count' column from my 2nd query with the first one. In other words, I want to see 5 different columns in one single query.
Thanks for you help in advance.
-- 1 Total COUNT of customers who were boarded
SELECT t.OriginCity
,t.Destination
,COUNT(DISTINCT t.FlightNumber+f.OriginAirportCode) as 'Total Flights'
,COUNT(DISTINCT c.CustomerID,c,LastName,c.FirstName) as 'Total Customers Boarded'
FROM dbo.TravelDetails as t
,dbo.Customer as c
WHERE t.TravelNbr = c.TravelNbr
AND t.ServiceDate = c.ServiceDate
AND t.OriginCity = c.OriginCity
AND c.BoardIndicator = 'Y'
AND t.ServiceDate BETWEEN @StartDate and @EndDate
GROUP BY t.OriginCity,t.Destination
-- 2 Total Count of Customers who were denied boarding
SELECT t.OriginCity
,t.Destination
,COUNT(DISTINCT t.FlightNumber+f.OriginAirportCode) as 'Total Flights'
,COUNT(DISTINCT c.CustomerID,c,LastName,c.FirstName) as 'Denied Customers Count'
FROM dbo.TravelDetails as t
,dbo.Customer as c
WHERE t.TravelNbr = c.TravelNbr
AND t.ServiceDate = c.ServiceDate
AND t.OriginCity = c.OriginCity
AND c.BoardIndicator = 'N'
AND t.ServiceDate BETWEEN @StartDate and @EndDate
GROUP BY t.OriginCity,t.Destination
June 21, 2012 at 11:53 am
This it totally untested because you didn't post any ddl or sample data but...
SELECT t.OriginCity
,t.Destination
,sum(case when t.FlightNumber IS not null and f.OriginAirportCode IS not null and c.BoardIndicator = 'Y' then 1 else 0 end) as 'Total Flights'
,sum(Case when c.CustomerID IS not null and c.LastName IS not null and c.FirstName IS not null and c.BoardIndicator = 'Y' then 1 else 0 end) as 'Total Customers Boarded',
sum(case when t.FlightNumber IS not null and f.OriginAirportCode IS not null and c.BoardIndicator = 'N' then 1 else 0 end) as 'Total Flights',
sum(case when c.CustomerID IS not null and c.LastName IS not null and c.FirstName IS not null and c.BoardIndicator = 'N' then 1 else 0 end) as 'Denied Customers Count'
FROM dbo.TravelDetails as t
join dbo.Customer as c on t.TravelNbr = c.TravelNbr
AND t.ServiceDate = c.ServiceDate
AND t.OriginCity = c.OriginCity
WHERE t.ServiceDate BETWEEN @StartDate and @EndDate
GROUP BY t.OriginCity, t.Destination
Notice I also changed your join from a cross join to an inner join. Then I moved the join condition to the join instead of the where clause.
Next time you post a question you should consider taking a look at the first link in my signature. It explains best practices when posting questions and what to post to help you get the best responses.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 1:30 pm
Thanks Sean. This helped me to get what I was looking for now. I'll keep your suggestion for the future posts.
Once again thanks so much
June 21, 2012 at 3:01 pm
sql1411 (6/21/2012)
Thanks Sean. This helped me to get what I was looking for now. I'll keep your suggestion for the future posts.Once again thanks so much
You are very welcome. Glad you were able to find a fix and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply