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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy