December 7, 2011 at 4:26 pm
I have a query that I am having to alias a field 3 times. I am using that field to pull data based on an application detail. my script looks like so.
SELECT DealerID, Count(appdetail.appid)AS Approved, DealerID, Count(appdetail.appid)AS conditioned, DealerID, Count(appdetail.appid)AS Declined
FROM AppDetails, Applications, Channels
WHERE Status = 'I', and Status = 'D' and Status = 'A' and DateDetail IS NULL.
What I am trying to do is get data for Approved if the status is 'A' and get data for Conditioned if the status is 'I' and Declined if the status is 'D'. I can
run a single script to get the data but when I try to add all of this together, it does not work. How do I do this? Do I incorporat an IF THEN statement or what? I am stuck on this. I can give you more detail if you need. Currently, I am having to run the script 3 times based on single information which looks like this.
SELECT DealerID, Count(appdetail.appid)AS Approved
FROM AppDetails, Applications, Channels
WHERE Status = 'A' and DateDetail IS NULL.
GO
SELECT DealerID, Count(appdetail.appid)AS Conditioned
FROM AppDetails, Applications, Channels
WHERE Status = 'I' and DateDetail IS NULL.
GO
SELECT DealerID, Count(appdetail.appid)AS Declined
FROM AppDetails, Applications, Channels
WHERE Status = 'D' and DateDetail IS NULL.
I basically want to add all this information into one script so that it pulls all the data into one grid.
HELP ME OBI WAN KANOBI.....YOUR MY ONLY HOPE........
December 7, 2011 at 4:41 pm
PIVOT can help here...
SELECT DealerID, [A] AS Approved, AS Conditioned, [D] AS Declined
FROM (SELECT DealerID, AppDetails.AppID, [Status]
FROM AppDetails, Applications, Channels
WHERE DateDetail IS NULL) a
PIVOT(COUNT(AppDetails.AppID) FOR [Status] IN (A, I, D)) pvt
Eddie Wuerch
MCM: SQL
December 7, 2011 at 5:43 pm
Jason,
I can't make out from your posted query any sort of relationships between the 3 tables. The way you posted the queries, they look like a CROSS JOIN of all tables involved with only 2 filters. Also, I doubt that what you posted would actually run since you used an aggregate function and don't have a GROUP BY.
What you probably need is a CROSS TAB type query. Here is an excellent article:
http://www.sqlservercentral.com/articles/T-SQL/63681/
Todd Fifield
December 8, 2011 at 4:51 pm
Where am I going wrong with this?
SELECT OldDealerID,
[A] AS Approved,
AS Conditioned,
[D] AS Declined
FROM (SELECT OldDealerID, AppDetails.AppID, [Status]
FROM AppDetails, Applications, Channels
WHERE AppDetails.AppID = Applications.AppID and Applications.SourceID = Channels.ChannelID and DateDetailDisposed
between '2011-11-01 00:00:00.000' and '2011-11-30 00:00:00.000')
PIVOT(COUNT(AppDetails.AppID) FOR [Status] IN (A, I, D)) pvt
It keeps telling me
Msg 170, Level 15, State 1, Line 8
Line 8: incorrect syntax near '('.
December 8, 2011 at 6:34 pm
PIVOT(COUNT(AppDetails.AppID) FOR [Status] IN (A, I, D)) pvt
mebbe need to put the text in quotes: FOR [Status] IN ('A','I','D')
December 12, 2011 at 1:47 pm
As much as I have tried to make this stupid thing work, it is not working. I still keep getting the same error. The sorry thing is that I can't use reporting services just yet, and I have to dump this into a report. ARGHHH...
December 13, 2011 at 6:33 am
First of all, you should read some of the many posts where people ask for DDL and sample data. We don't understand your structure and what you're expecting to get out of the query. For example:
What is the relationship between AppDetails, Applications and Channels?
Why are you wanting to count AppDetails.AppID, but somehow joining in Applications and Channels?
Are Status and DateDetail in the AppDetails table?
Without knowing exactly how to join in the other two tables, if you want to only count AppID's this might work or it might blow up...
SELECT AD.DealerID,
(SELECT Count(ADa.AppID)
FROM AppDetails ADa
WHERE ADa.Status = 'A'
AND ADa.DateDetail IS NULL
AND ADa.AppID = AD.AppID) AS Approved,
(SELECT Count(ADc.AppID)
FROM AppDetails ADc
WHERE ADc.Status = 'I'
AND ADc.DateDetail IS NULL
AND ADc.AppID = AD.AppID) AS Conditioned,
(SELECT Count(ADd.AppID)
FROM AppDetails ADd
WHERE ADd.Status = 'D'
AND ADd.DateDetail IS NULL
AND ADd.AppID = AD.AppId) AS Declined
FROM AppDetails AD
If you need to make sure the count reflects records that exist in all three tables, then include the proper joins (FROM AppDetails ADa INNER JOIN Applications A ON ADa.SomeKey = A.SomeOtherKey, etc.) in the subqueries.
Just a shot, might work, might not...seeing if I can push you further down the road of learning...
December 14, 2011 at 12:44 pm
There's a pretty simple fix to your script: add a table alias to the derived table.
A "derived table" is any time you use a subquery as a table in the FROM clause. It must be alised with a name, because the query itself doesn't have a name like tables do.
SELECT *
FROM (SELECT * FROM sys.objects) AS ThisIsTheAliasForTheDerivedTable
The PIVOT syntax uses two derived tables: one to set up the input set, and one to define the pivot of it.
SELECT OldDealerID,
[A] AS Approved,
AS Conditioned,
[D] AS Declined
FROM (SELECT OldDealerID, AppDetails.AppID, [Status]
FROM AppDetails, Applications, Channels
WHERE AppDetails.AppID = Applications.AppID and Applications.SourceID = Channels.ChannelID and DateDetailDisposed
between '2011-11-01 00:00:00.000' and '2011-11-30 00:00:00.000') AS AnAliasGoesHere -- <-- That's it
PIVOT(COUNT(AppDetails.AppID) FOR [Status] IN (A, I, D)) pvt
Because you didn't specify an alias for the first derived table, the keyword PIVOT became the alias (the 'AS' is optional), which was followed by an open peren, which made no sense to the parser.
Eddie Wuerch
MCM: SQL
December 14, 2011 at 1:06 pm
So would the script look like this?
SELECT *FROM (SELECT * FROM sys.objects) AS SalesStats
GO
SELECT OldDealerID,
[A] AS Approved,
AS Conditioned,
[D] AS Declined
FROM (SELECT OldDealerID, AppDetails.AppID, [Status]
FROM AppDetails, Applications, Channels
WHERE AppDetails.AppID = Applications.AppID AND Applications.SourceID = Channels.ChannelID AND
DateDetailDisposed BETWEEN '2011-11-01 00:00:00.000' and '2011-11-30 00:00:00.000') AS SalesStats
PIVOT (COUNT(AppDetails.AppID) FOR [Status] IN (A, I, D)) pvt
When I run this script, it says incorrect syntax near 'PIVOT'
I am really struggling with this and I do appreciate your help very much!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply