Alias table in join select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alias table in join select query

Author

Topic

fartmaster
Starting Member

2 Posts

Posted - 2010-09-21 : 09:15:27

Hi,

I would appreciate if someone could help me with my problem.

I have table called 'apps' with following columns:

id
customer_id
time_application
accepted
confirmed
cancelled

Now, I'm writing a query which should return a report of events daily.

SELECT
'date'=time_application,
'total'=Count(*),
'accepted'=Sum(Case When accepted=1 Then 1 Else 0 End),
'confirmed'=Sum(Case When confirmed=1 Then 1 Else 0 End),
'cancelled'=Sum(Case When confirmed=1 Then 1 Else 0 End),
'later accepted'=later
FROM apps a1 INNER JOIN (
SELECT customer_id, count(*) as 'later'
FROM apps
WHERE
accepted=1 and
confirmed=1 and
cancelled=0 and
time_application>a1.time_application
GROUP BY customer_id) a2 ON a1.customer_id=a2.customer_id
GROUP BY date
ORDER BY date

But this returns error: "The multi-part identifier "a1.time_application" could not be bound."

Basicly I would like to get a number of accepted applications after a specific date by the same customer. The report has other stuff in it as well but this is the idea.

What do you think is the correct way to do this?

Thanks in advance!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 09:23:48

DELETED

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 10:15:25

i am having following sample data in table apps

customer_id time_application accepted confirmed cancelled
1 2010-09-01 1 0 0
6 2010-09-01 1 1 0
7 2010-09-02 1 1 0
2 2010-09-02 1 1 0
3 2010-09-10 1 1 0
1 2010-09-11 1 1 0
4 2010-09-15 1 0 0
5 2010-09-16 1 0 0
4 2010-09-17 1 1 0
5 2010-09-17 1 1 0
and i assume you want the o/p something like this

date Total accepted confirmed cancelled later accepted
2010-09-01 2 2 1 0 1
2010-09-02 2 2 2 0 0
2010-09-10 1 1 1 0 0
2010-09-11 1 1 1 0 0
2010-09-15 1 1 0 0 1
2010-09-16 1 1 0 0 1
2010-09-17 2 2 2 0 0

Is it so?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 10:31:00

following query will give you the required output:


SELECT 
     time_application AS date,
     COUNT(*) AS Total,
     SUM(Case When accepted=1 Then 1 Else 0 End) [accepted],
     SUM(Case When confirmed=1 Then 1 Else 0 End) [confirmed] ,
     SUM(Case When cancelled=1 Then 1 Else 0 End) [cancelled],
	 SUM(B.laterAccepted) AS [later accepted]
FROM
(
	 SELECT 
			time_application,
			accepted,
			confirmed,
			cancelled,
			[laterAccepted] = ( 
								SELECT COUNT(*) 
								FROM apps A 
								WHERE 
									A.accepted = 1 AND
									A.cancelled = 0 AND 
									A.time_application > A1.time_application AND
									A.customer_id = A1.customer_id
						       )
	FROM apps A1
)B
GROUP BY time_application 
ORDER BY time_application