Issue with where clause - SQL Server Forums

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

Issue with where clause

Author

Topic

wabaker95
Starting Member

12 Posts

Posted - 2011-07-07 : 14:48:57

I have a query that needs to get the member number, campaignid, campaign name, add date, and branch name. Also the query should only get this information for the last time the data was entered into the the database table. I tried to use the Max function with the adddate column to get this information. However the query is returning the correct information but it is not getting the info by last adddate.

USE [memberWORKS_PreProduction]
GO
/****** Object: StoredProcedure [dbo].[MRM_Get_LoanStealingByMembership] Script Date: 07/07/2011 12:03:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[MRM_Get_LoanStealingByMembership]
@FID AS Integer,
@CampaignID AS BigInt,
--@ReportStartDate As DateTime,
--@ReportEndDate As DateTime,
@MemberBranches AS VARCHAR(2048)

AS

--IF(Select OBJECT_ID('tempdb.dbo.#Campaign_Status', 'U')) IS NOT NULL
--BEGIN
-- DROP TABLE #Campaign_Status
--END

--IF(Select OBJECT_ID('tempdb.dbo.#Teller_Branch', 'U')) IS NOT NULL
--BEGIN
-- DROP TABLE #Teller_Branch
--END

--CREATE TABLE #Teller_Branch
-- (
-- BranchID INT,
-- StartDate DATETIME,
-- EndDate DATETIME,
-- PRIMARY KEY (BranchID, StartDate),
-- UNIQUE(BranchID, StartDate))

--CREATE TABLE #Campaign_Status
-- (
-- MembershipNo BIGINT,
-- Name nvarchar(128),
-- CampaignIDs INT,
-- MID BIGINT,
-- PrimaryMembership_IID BIGINT,
-- Member_BranchID INT DEFAULT NULL)

-- BEGIN

--Insert Into #Campaign_Status
Select distinct
MembershipNo
, Sales_Campaign.Name
,Sales_Campaign.CampaignID
--,Sales_Campaign_Memberships.MID
--,[Individual_Demographic].IID
,Sales_Campaign_Participants.AddDate
,[Branch].[BranchName]
FROM [dbo].[Sales_Campaign_Participants]
INNER JOIN Sales_Campaign
ON
Sales_Campaign_Participants.CampaignID = Sales_Campaign.CampaignID and
Sales_Campaign.FID = @FID
INNER JOIN Sales_Campaign_Memberships
ON
Sales_Campaign_Participants.ItemID = Sales_Campaign_Memberships.ItemID

INNER JOIN Membership
ON
Sales_Campaign_Memberships.MID = Membership.MID and
Membership.FID = @FID
INNER JOIN MembershipDetail
ON
Sales_Campaign_Memberships.MID = MembershipDetail.MID
and MembershipDetail.FID = @FID
INNER JOIN [Membership_to_Individual]
ON
[Membership_to_Individual].[FID] = @FID AND
[Membership_to_Individual].[MID] = [Sales_Campaign_Memberships].[MID]
INNER JOIN
[Individual_Demographic] (NOLOCK)
ON
[Individual_Demographic].[FID] = @FID AND
[Individual_Demographic].[IID] = [Membership_to_Individual].[IID]
INNER JOIN Branch
ON
[Individual_Demographic].BranchID = Branch.BranchID and
[Branch].FID = @FID
Group By Membership.MembershipNo, Sales_Campaign.Name,Sales_Campaign.CampaignID
,Sales_Campaign_Participants.AddDate, Sales_Campaign.FID, Sales_Campaign_Participants.CampaignID, Sales_Campaign_Participants.StatusID
,[Branch].[BranchName], Individual_Demographic.BranchID

Having Sales_Campaign_Participants.AddDate In (Select MAX(Sales_Campaign_Participants.AddDate)) And Sales_Campaign.FID = @FID And Sales_Campaign_Participants.CampaignID = @CampaignID
AND Sales_Campaign_Participants.StatusID = 0 AND ([Individual_Demographic].[BranchID] IN (SELECT value FROM dbo.fn_Split(@MemberBranches, ',')))

order by BranchName asc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 19:06:07

The stored procedure is really long for anyone to look through and understand the logic to tell what might be wrong. If you can simplify it to demonstrate the problem, I am sure someone on this forum would be able to help. Also, post the table DDL and some sample data. Brett's blog here might help:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources