I can grab the 1st record for each cust I need like this in a view:
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Adand then Inner Join it in my SP like this:
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId
CREATE PROCEDURE Reports_GetReportDataNow, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO
Ultimately what I need to use is below with something inside it that will do what the view above did:
CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50))
ASSELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcCommentsFROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcCommentsORDER BY vcCustId
GO
Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.
Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.
Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003
Total Leads this Ad Source: 2
I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.
The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.
The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.
Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.
Let me know if you need more info.
Thanks.|||This seems to work:
CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50))
ASSELECT DISTINCT
TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO
If you see any potential problems with this let me know, it's all I can come up with.
Thanks.
