Incrementally Build Cursor Select Using CASE?

Trying To Build CASE
I'm trying to add a CASE to my statement, I keep getting a syntax error. Can anyone see the issue? Should this CASE work?

Thanks for your thoughts,

Select tmp.Alternate_Name,dbo.tblClient.Salutation
CASE
WHEN (tmp.Alternate_Name > '') Then tmp.Alternate_Name As Card_Name
Else dbo.tblClient.Salutation AS Card_Name END
From dbo.tblClient INNER JOIN dbo.tblCards tmp ON dbo.tblClient.ClientID = tmp.ClientID

View Replies !

---

Cursor, Loop Or Case?
Just started here, so here's what I got. I would ask the developer, but he's already gone (now I see why;-))

We are trying to pull leads from a table that have not been sold (numbsold), is available, not expired, has no agent

Here's an example of a leadtimeframeid - 1= 1month , 2=1-3months 3= 4-5months

Here's an example of LeadtypeID 1= sell 2=buy 3=buy/sell

He seems to be going through the table and looking for a variation of each..

Such as for leadtimeframe 1, pull leadstypes 1 and 2 and union each on the 3.

The result set should be as follows -

lead_id,parent_lead_id,lead_type_id, buy_zip_1, buy_zip_2, buy_zip_3, zip

Hope that explains it!!

I'ved included the SP and the table schema.. any help would be greatly appreciated.

 

 

 


ALTER  PROCEDURE dbo.usp_GetRNLeadsCapOptimization
 @LookBackDays   INT,
 @LeadTimeFrame   INT,
 @PropertyValue   INT,
 @WorkWithRealtor TINYINT,
 @LeadTypeID  TINYINT,
 @strZIP   VARCHAR(5)

AS
 DECLARE @PriceRange MONEY,  
  @DateIn     DATETIME

BEGIN
    
   SET  @DateIn = CAST(CONVERT(VARCHAR(10), DATEADD(d, -@LookBackDays, CURRENT_TIMESTAMP), 101) AS DATETIME) + CAST('12:00:00AM' AS DATETIME)
   SET @PriceRange = 100000.0000

   IF (@PropertyValue = 1)
      SET @PriceRange = 100000.0000
   ELSE IF (@PropertyValue = 2)
      SET @PriceRange = 200000.0000
   ELSE IF (@PropertyValue = 3)
      SET @PriceRange = 300000.0000
   ELSE IF (@PropertyValue = 4)
      SET @PriceRange = 400000.0000
   ELSE IF (@PropertyValue = 5)
      SET @PriceRange = 500000.0000
   ELSE IF (@PropertyValue = 6)
      SET @PriceRange = 600000.0000
   ELSE IF (@PropertyValue = 7)
      SET @PriceRange = 700000.0000
   ELSE IF (@PropertyValue = 8)
      SET @PriceRange = 800000.0000
   ELSE IF (@PropertyValue = 9)
      SET @PriceRange = 900000.0000
   ELSE IF (@PropertyValue = 10)
      SET @PriceRange = 1000000.0000
   ELSE IF (@PropertyValue = 11)
      SET @PriceRange = 2000000.0000
   ELSE IF (@PropertyValue = 12)
      SET @PriceRange = 3000000.0000
   ELSE IF (@PropertyValue = 13)
      SET @PriceRange = 4000000.0000
   ELSE IF (@PropertyValue = 14)
      SET @PriceRange = 5000000.0000
   ELSE IF (@PropertyValue = 15)
      SET @PriceRange = 6000000.0000
   ELSE IF (@PropertyValue = 16)
      SET @PriceRange = 7000000.0000
   ELSE IF (@PropertyValue = 17)
      SET @PriceRange = 8000000.0000
   ELSE IF (@PropertyValue = 18)
      SET @PriceRange = 9000000.0000
   ELSE IF (@PropertyValue = 19)
      SET @PriceRange = 10000000.0000


   IF (@WorkWithRealtor = 0)
   BEGIN
 IF (@LeadTimeFrame = 1)
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 1 -- BUY
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn       
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)  

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3 --BUY/SELL
     AND (
         ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
         OR
         ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  
  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 2 --SELL
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

            UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3 --BUY/SELL
     AND (
         ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
         OR
         ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END  
 END

 ELSE IF(@LeadTimeFrame = 2)
 BEGIN
  IF(@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn  
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  
  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id IN (23, 24)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END   
 END

 ELSE IF(@LeadTimeFrame = 3)
 BEGIN
  IF(@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn    
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION
   
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END

  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id IN (23, 24, 25)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END  
 END

 ELSE IF(@LeadTimeFrame = 4)  
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25, 107) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn    
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25, 107)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  ELSE IF(@LeadTypeID = 2)
  BEGIN
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25, 107) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn     
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25, 107)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn      
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END  
 END

 ELSE IF(@LeadTimeFrame = 5)
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id = 96 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
  
     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
      AND tlq.date_in >= @DateIn 
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END

  ELSE IF(@LeadTypeID = 2)  
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.has_agent = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END  
 END
   END

   ELSE IF (@WorkWithRealtor = 1) 
   BEGIN
 IF (@LeadTimeFrame = 1)
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn       
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)  

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0  
     AND tlq.lead_type_id = 3
     AND (
         ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
         OR
         ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  
  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

            UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0  
     AND tlq.lead_type_id = 3
     AND (
         ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
         OR
         ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 23 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END  
 END

 ELSE IF(@LeadTimeFrame = 2)
 BEGIN
  IF(@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn  
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  
  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0   
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id IN (23, 24)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END
 END

 ELSE IF(@LeadTimeFrame = 3)
 BEGIN
  IF(@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn    
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION
   
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END

  ELSE IF(@LeadTypeID = 2)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id IN (23, 24, 25)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END  
 END

 ELSE IF(@LeadTimeFrame = 4)  
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25, 107) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn    
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25, 107)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END
  ELSE IF(@LeadTypeID = 2)
  BEGIN
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id IN (23, 24, 25, 107) 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn     
     AND tlq.zip = @strZIP

     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id IN (23, 24, 25, 107)
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn      
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END  
 END

 ELSE IF(@LeadTimeFrame = 5)
 BEGIN
  IF (@LeadTypeID = 1)
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0    
     AND tlq.lead_type_id = 1
     AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
     AND tlq.time_frame_id = 96 
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
  
     UNION

     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
          )
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
      AND tlq.date_in >= @DateIn 
     AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
     ORDER BY tlq.lead_id, tlq.lead_type_id 
  END

  ELSE IF(@LeadTypeID = 2)  
  BEGIN
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 2
     AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn
     AND tlq.zip = @strZIP

     UNION
  
     SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
     tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
     FROM tbl_leads_queue tlq WITH (NOLOCK)
     WHERE tlq.lead_id = tlq.parent_lead_id
     AND tlq.NumSold = 0  
     AND tlq.is_available = 1
     AND tlq.Expired = 0
     AND tlq.lead_type_id = 3
     AND (
          ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
          OR
          ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
         )
     AND tlq.time_frame_id = 96
     AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
       WHERE parent_lead_id = tlq.parent_lead_id
       AND (lead_status_id = 3 OR lead_status_id = 4))
     AND tlq.date_in >= @DateIn 
     AND tlq.zip = @strZIP
     ORDER BY tlq.lead_id, tlq.lead_type_id
  END  
 END
   END  
END


 

---Table schema


CREATE TABLE [dbo].[tbl_leads_queue] (
 [lead_id] [bigint] IDENTITY (1, 1) NOT NULL ,
 [parent_lead_id] [bigint] NOT NULL ,
 [partner_id] [int] NOT NULL ,
 [RealtorCompanyID] [bigint] NOT NULL ,
 [RealtorPrimaryContactUserName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorPrimaryContactFirstName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorPrimaryContactLastName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorCompanyName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorTotalOffices] [int] NOT NULL ,
 [RealtorTotalAgents] [int] NOT NULL ,
 [RealtorBrandLogoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorMoreInfoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [RealtorWeekendCoverage] [bit] NOT NULL ,
 [RealtorCustomerServiceRating] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [DesiredMonthlyPayment] [float] NOT NULL ,
 [realtor_id] [bigint] NULL ,
 [lead_type_id] [int] NOT NULL ,
 [lead_status_id] [int] NOT NULL ,
 [buy_property_type_id] [int] NULL ,
 [sell_property_type_id] [int] NULL ,
 [time_frame_id] [int] NOT NULL ,
 [best_time_id] [int] NOT NULL ,
 [matched_on] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [phone_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [phone_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [phone_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [phone_ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [phone_area2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [phone_prefix2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [phone_suffix2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_zip_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_city_state_1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_zip_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [buy_city_state_2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [buy_zip_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [buy_city_state_3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [buy_price_range_start] [money] NOT NULL ,
 [buy_price_range_end] [money] NOT NULL ,
 [buy_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [buy_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [sell_price_desired] [money] NULL ,
 [sell_price_qualifying] [money] NULL ,
 [sell_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [sell_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [sell_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [sell_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [comments] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [additional_info] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [lead_fee] [money] NOT NULL ,
 [no_charge] [int] NULL ,
 [credited] [int] NOT NULL ,
 [lead_problem] [int] NULL ,
 [date_in] [datetime] NOT NULL ,
 [date_sent] [datetime] NULL ,
 [TrafficLogID] [bigint] NOT NULL ,
 [notify_offers] [bit] NOT NULL ,
 [credit_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [has_agent] [bit] NOT NULL ,
 [found_home] [bit] NOT NULL ,
 [cell_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [cell_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [cell_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [why_selling] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [is_buysell] [bit] NOT NULL ,
 [Affiliate_ID] [bigint] NULL ,
 [free_mortgage_quote] [bit] NOT NULL ,
 [loan_type] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [gross_monthly_income] [float] NOT NULL ,
 [can_verify_income] [bit] NOT NULL ,
 [desired_loan_amount] [float] NOT NULL ,
 [existing_loan_balance] [float] NOT NULL ,
 [first_mortgage_monthly_payment] [float] NOT NULL ,
 [current_interest_rate] [float] NOT NULL ,
 [RealtorNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [NumSold] [int] NOT NULL ,
 [is_available] [bit] NOT NULL ,
 [Date_Declined] [datetime] NULL ,
 [Expired] [bit] NOT NULL ,
 [Original_Affiliate] [bigint] NULL ,
 [lead_distance] [float] NULL ,
 [lead_Problem_Comments] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [AdminUserID] [int] NULL ,
 [dateCredited] [datetime] NULL ,
 [OriginalAffiliateID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

View Replies !

---

Need Help To Build A Select
Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated.
Thanks

View Replies !

---

Cannot Build This Custom Select Statement...
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this:
SELECT     filenameFROM         pictableWHERE     (@searchby LIKE @searchwords)
It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works:
SELECT     filenameFROM         pictableWHERE     (keywords LIKE @searchwords)
Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail.
Thanks in advance.

View Replies !

---

How Can I Dynamically Build Sql SELECT Using ASP 1.0 Array Concept
The below code is ASP 1.0 to dynamically search a database and I want to use the same concept for a ASP.Net 2.0 solution.  Do I do this in the code behind or on the aspx page and if on the aspx page what controls do I use for the array split?  Basically where do I start.  It took me a long time to get this old code working, I am hoping it is simpler in 2.0.
Thank you
OLD ASP 1.0 code to dynamically build a Sql Select statement for searching a database using one or more search words entered by user.
If Request.Querystring("kwdSearch") <> "" ThenDim kwdString, ArrKwdString, iCountiCount = 0 kwdString = Replace(Request.Querystring("kwdSearch"), "'", "''")ArrKwdString = Trim(kwdString)ArrKwdString = Split(kwdString, " ",-1,1) For iCount = 0 to UBound(ArrKwdString) If iCount < UBound(ArrKwdString) Then  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' AND "  Else  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' " End ifNext    RS.Open "SELECT * FROM tblLinkInfo Where (" & Criteria & ") AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 If RS.EOF Then  If Rs.State Then RS.Close  RS.Open "SELECT * FROM tblLinkInfo WHERE L_Description LIKE '%" & Replace(Request.Querystring("kwdSearch"),"''","'") & "%' AND  L_Enabled = 1  ORDER BY " & SortBy & "L_Rank", CNN, 3  End If
  RESULTS --- Display results with Repeater1.DataBind(); etc
  Exit SubEnd If

View Replies !

---

Incrementally Returning Records
This question has been posted on the site before but I could not find any resolution....I want to return 10 records at a time for a query that returns 1000's of records without using a cursor or temp table.

The closest query I have found is a query that numbers the rows....
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id

Thanks,

Jim

View Replies !

---

SELECT With GROUP BY And Build A List From Vales Not Shown
Hard to write a subject line to describe this one.

Anayway, I have a table with names and address plus an extra field noting a part number of product.

I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address.

Example:

NAME ADDRESS PART NUMBER
John Smith 555 Main st., los angeles, ca 90003 5000
John Smith 555 Main st., los angeles, ca 90003 6650
Mike Jones 8569 West 18th Ave., San Diego, ca 1255
John Smith 555 Main st., los angeles, ca 90003 5144
Mike Jones 8569 West 18th Ave., San Diego, ca 2399


So I'd like my results to look like this:

NAME ADDRESS PARTS
John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144
Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399

THanks in advance for any suggestions!

Raul

View Replies !

---

Dynamic Build SQL In Store Procedure Based On Select
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL

View Replies !

---

Error On Build Solution, If SSIS Project Is Part Of Build
Hello,
 
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
 
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
 
Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password.  You can wonder for what we need that SSIS packages are part of our build.  We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View Replies !

---

Cursor Vs. Select
buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy

View Replies !

---

Using Select Statement Instead Of Cursor
Hi All, Can anyone please help?

TableA has data as below:

ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s

I have to convert the rel_code into a specific manner so the data will look as below in TableB:

ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31

Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.

I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.

Thanks in advance.

Jannat.

View Replies !

---

Select Statement In Cursor
Hi...


I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria

I am using SQL mail.


I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery

When i have the following code to run my cursor

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.

What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.

Please help!!!

Thanks much indeed

Ramesh

View Replies !

---

Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem
on the select statement in sql cursor

My select statement is stored in 2 variables one holds the select clause
and the other holds the where clause

I am doing a small test as my seelct statement is very complicated
lots of joins and it is built up from lots of parameters
from other queries and from another stored procedure as well

Hope you can help

when i type the following code:

declare @query varchar(100)
declare @query2 varchar(100)

set @query = "SELECT FROM ml_testMaillist "
set @query2 = " WHERE m_Email= 'ramesh@go-events.com' "

DECLARE overdue2 CURSOR

LOCAL FORWARD_ONLY
exec(@query + @query2)

open overdue2



I get the error

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'exec'.


Please please help as this is very impt to me
Thanks Thanks


Regards

View Replies !

---

Select Output With Cursor
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.


--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query



--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet


--Here is the cursor script.--

Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255)
DECLARE emp_skills CURSOR For
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'

DECLARE emp_skills2 CURSOR For
select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'
OPEN emp_skills
OPEN emp_skills2
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
FETCH NEXT FROM emp_skills2 into @skills2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @skills2
FETCH NEXT FROM emp_skills2 into @skills2
Print @empID + ' '+ @Rank + ' ' + @message


FETCH NEXT FROM emp_skills into @empID, @Rank, @skills

End
CLOSE emp_skills
DEALLOCATE emp_skills
CLOSE emp_skills2
DEALLOCATE emp_skills2


--Previous Post--

Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.



--SQL SCRIPT__

select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3')


--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query



--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet

View Replies !

---

Cursor Select And Variables
I have problems to place my variable into the select statement.

DECLARE @DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print @DB_NAME;   --works fine

Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @DB_Name in my select ?

View Replies !

---

CURSOR Select Clause
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:

Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?

select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'
 
DECLARE myCursor CURSOR FOR
 Select @SQL

OPEN myCursor
print @@Cursor_rows
      FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID

View Replies !

---

Dynamic Select For CURSOR
Hi all

I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID

etc etc

Here's the cursor


DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod

OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1

-- DO SOME FUNKY STUFF


FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID

View Replies !

---

Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View Replies !

---

Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

View Replies !

---

How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server.  So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
    print @DBname
    fetch next from testCursorForDB
    into @DBname
end

close testCursorForDB
deallocate testCursorForDB   

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way).  In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View Replies !

---

SELECT DISTINCT Will Always Result In A Static Cursor
an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View Replies !

---

Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:

SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'

Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:

EXEC (@BuildDBPageUsed)

The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?

*Thanks* for any help in this matter.
Brad

View Replies !

---

How To Put Condition In Select Statement To Write A Cursor
col1          col2 col3   col4
36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     .00
13393.05   67   N     .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View Replies !

---

SELECT DISTINCT Will Always Result In A Static Cursor
An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste  CURSOR DYNAMIC
FOR Select DISTINCT name  from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .

 
any idea how to make a select distinct result in a dynamic Cursor?

View Replies !

---

Select Case - A Better Way?
Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records

is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?

TIA

View Replies !

---

Select Case
I need to do nested select case statement. I try to do, however it is not working. Please help
SELECT CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
ELSE 'N/A' END as [Frequency of the schedule execution],
[days the schedule runs]=
Case freq_type WHEN 4 then 'Daily'
WHEN 8 then
CASE freq_interval
WHEN '1' THEN 'Sunday'
WHEN '2' THEN 'Monday'
WHEN '4' THEN 'Tuesday'
WHEN '8' THEN 'Wednesday'
WHEN '16' THEN 'Thursday'
WHEN '32' THEN 'Friday'
WHEN '64' THEN 'Saturday'
ELSE 'N/A' END

WHEN 32 THEN
CASE freq_interval
WHEN '1' THEN 'Sunday'
WHEN '2' THEN 'Monday'
WHEN '3' THEN 'Tuesday'
WHEN '4' THEN 'Wednesday'
WHEN '5' THEN 'Thursday'
WHEN '6' THEN 'Friday'
WHEN '7' THEN 'Saturday'
WHEN '8' THEN 'Day'
WHEN '9' THEN 'Weekday'
WHEN '10' THEN 'Weekned Day'
END

END
FROM msdb..sysjobschedules a
JOIN msdb..sysjobs b
ON a.job_id=b.job_id

View Replies !

---

Is This Possible To Use Select Case
hi, this is my procedure.

CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO

if i run this procedure i need the result as following

result result1 result2
found notfound found

is this posible to do using select case ? please help me to get my need please

View Replies !

---

Increment In Select Query Without Cursor And Temp Table
I have:

Select
T0.Id_Roomtype,
count(T0.ID_Room)as Total,
convert(smalldatetime,'20000601') as ADate --- !!!!
from rmRoom T0
where
T0.id_Property = 1
group by T0.Id_Roomtype

How to do:


declare @x int
set @x = 36901
Select
T0.Id_Roomtype,
count(T0.ID_Room)as Total,
convert(smalldatetime,(set @x = @x+1)) as ADate --- !!!
from rmRoom T0
where
T0.id_Property = 1
group by T0.Id_Roomtype

I am trying to increment value in Column ADate
so to get new date for each row


Is it possible without using cursor and temp table ?

ht

View Replies !

---

Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE               QTYIN  COST_IN_AMT        COST_OUT_AMT(MOVING AVERAGE)   
1          01/01/2007  PURCHASE            10                1000
2          01/01/2007  PURCHAES              5                1100
3          01/01/2007  SALES                    -5                                     *TobeCalculated
4          02/01/2007  Purchase                20                9000
5          02/01/2007  SALES                  -10                                     *TobeCalculated
5          02/01/2007  purchase                50                 8000 
6          03/01/2007  Sales                    -10                                      *TobeCalculate
7         01/01/2007   Purchase                20                12000
 
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order  to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View Replies !

---

CASE In Select Statement
Hi, i've got this stored procedure:
 @LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN

IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END

IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END

CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END

WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END

WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
 According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can  i achieve this cause my case is not working because it is not inside the select statement

View Replies !

---

Select Case Issue
I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,

View Replies !

---

Case When In A Select Statement
Hi everyone,
I have the following problem withj this query:

SELECT DocNumber,
       Title,FirstName,LastName,Paper,
       ANNO,Location,Keywords,
       URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....

When URL is null, I don't get the string showing. It works properly only in the query analyzer.

Any ideas?

Christian

View Replies !

---

Select Within Case Statement?
Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg

What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!

tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk

For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc

View Replies !

---

SELECT CASE WHEN Problem
Hello !
For MS SQL 2000/2500

I am having a table ::

User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,

with this query ::

SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident

I get ::

Numb | Info | Ident

100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1

100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2


what I need is something like::

SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)

to get ::

InfoA | InfoB | InfoC | Ident

dgwrg | dgeth yiuyi | Agt hgn ngtn | 1

jttytujky | yjtut iuiu | dgd 2gggwekew | 2


Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all

thanks a ton for helping

View Replies !

---

Select Case Question
I am doing a mail merge from data returned from a query. My problem is one of my fields is to long for the document. I want to be able to break up the line into 2 sections. I'm assuming I can do this within the case statement but am unsure how.
This is the code I have. I want the break to come before the "at".


case
when rb.building in (9420,420) then 'DEERFIELD SUMMER SCHOOL at BAKER WOODS #99'
when rb.building=9722 then 'JAMES SUMMER SCHOOL' 'at JOHN MARSHALL MS'
when rb.building=9723 then 'NORTHWEST SUMMER SCHOOL at CRISPUS ATTUCS MS'
when rb.building=9717 then 'BROAD SUMMER SCHOOL at SIDENER MS'
when rb.building=9715 then 'MANUAL SUMMER SCHOOL at MCFARLAND MS'
else upper(trim(rb.building_name)) end,

View Replies !

---

Search Or Select CASE????
I'm using a 'searched case' however I don't care which method I use.

I'm open to using anything....

I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.

-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.

TIA

JeffP...

View Replies !

---

Case Statement Within A Select
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.

Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:


Code:


insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID



I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:


Code:

1 = A
2 = B
3 = C
4 = D
5 = E



Any help?

View Replies !

---

Select Case Statement
INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus)
        SELECT
  s.Hic,
 s.DtImported,
 s.Source,
 dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID,
 m.LastName,
m.FirstName,
 dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID,
s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New'
                        CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old

ElSE 'No Valid'
End


        from tbEsccSuspects s inner join tbPDMMembers m
        on s.Hic = m.HicNumber
        Where s.EUFStatus = 1 -- AND DtEUFSent is NULL


what I am doing wrong in the case statment that I am getting an error. ?????

       

View Replies !

---

SELECT QUERY Without USING CASE
I have a select query

select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition,

The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4

I want the column with their values to join with the tables 2,3,4.

Except USING CASE
Except USING CASE

Please Guide me

View Replies !

---

VB 'Select Case' Equivalent In T-SQL?
I'm still new to T-SQL and have been trynig to find an equivalent to VB's select case.  Is the IF statement all that's available?

View Replies !

---

How To Select A Case Sensitive Value In SQL With C#
Hello,

I have an application that at the begining a user will login with a user name and password which is stored in the database. The SQL statement is as follows:

"SELECT id_employee FROM employee WHERE employee_number='" + txtUserName.Text + "' AND passWord='" + txtPassword.Text + "'";

For testing purposes I have set the password to the word test.   The problem is, if the user enters in TEST or TeSt or TESt  it will grant them access. How do I set it to force the correct case?

I am using SQL 2005 for the database.

Thanks!

~zero

View Replies !

---

How To Set The Value Of Variable In Select Case
Select Case l.GLTransactionTypeID
When 'Asset' --AND l.GLTransactionDate BETWEEN convert(datetime,'1/1/2007')AND convert(datetime,'12/31/2007') and l.GLTransactionSource=@assetID
Then Begin Set @Cost=l.GLtransactionAmount end
end from LedgerTransactions l,FixedAssets f where l.GLtransactionsource=f.assetID and l.GLtransactionsource= @assetID

View Replies !

---

Select Case Statement
Hi,

I'm wondering what syntax to use for a select case statement.

I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions.

For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions.

So- I'm saying:
select case when question_id = 488 then don't return question_id 220, 962, 963

I'm trying something like this:

select q.client_id,
case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id
from questions_yes_no q

How would I say 'dont show' question 220? End is not the way to go, obviously.

If you can see what I'm trying to do, can you point me to an article or bol search?

Thank you for any help.

View Replies !

---

Help With Syntax (select Case)
Hi,

I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..

I have my SPROC configured as below, can anyone help me out as to why its not working?

btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)


IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID


thanks very much once again!
mike123



CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)

AS SET NOCOUNT ON


SELECT

CASE @genderID
WHEN 1 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......


WHEN 2 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *

FROM tbl
WHERE .......



END

View Replies !

---

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

---

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list


I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

---

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...


Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:




Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--     interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     --   SET @errmsg = 'Unsupported reporting format:' + @authCode
      --  RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
          ,ImportedReportID
          ,AuthorityCode
          ,[ID]
          ,[Field1] AS RecordType
          ,[Field2] AS FormType
          ,[Field3] AS ItemID
          ,[Field4] AS EntityCode
          ,[Field5] AS LastName
          ,[Field6] AS FirstMiddleNames
          ,[Field7] AS Title
          ,[Field8] AS Suffix
          ,[Field9] AS AddressLine1
          ,[Field10] AS AddressLine2
          ,[Field11] AS City
          ,[Field12] AS [State]
          ,[Field13] AS ZipFull
          ,[Field14] AS OutOfStatePAC
          ,[Field15] AS FecID
          ,[Field16] AS Date
          ,[Field17] AS Amount
          ,[Field18] AS [Description]
          ,[Field19] AS Employer
          ,[Field20] AS Occupation
          ,[Field21] AS AttorneyJob
          ,[Field22] AS SpouseEmployer
          ,[Field23] As ChildParentEmployer1
          ,[Field24] AS ChildParentEmployer2
          ,[Field25] AS InKindTravel
          ,[Field26] AS TravellerLastName
          ,[Field27] AS TravellerFirstMiddleNames
          ,[Field28] AS TravellerTitle
          ,[Field29] AS TravellerSuffix
          ,[Field30] AS TravelMode
          ,[Field31] As DptCity
          ,[Field32] AS DptDate
          ,[Field33] AS ArvCity
          ,[Field34] AS ArvDate
          ,[Field35] AS TravelPurpose
          ,[Field36] AS TravelRecordBackReference
      FROM ImportedNativeRecords
      WHERE ImportedReportID IS NOT NULL
      AND ReportType IN ('RCPT','PLDG')
     ORDER BY ImportedRecordID  -- this should work but gives syntax error!
    );

END

View Replies !

---

SELECT CASE Problem... Me Thinks!!
DECLARE @UserEmail as Varchar(50)
DECLARE @UserZipPassword as Varchar(50)
DECLARE @CoupMember as int
DECLARE @member As Bit
--Check if Email Exists…. If yes Then
--
SELECT @RewardsMember = COUNT(*),
@member =CASE M.EMAIL
WHEN ((LEN(EMAIL)) <> 1)
THEN 1
WHEN (LEN(EMAIL)) IS NULL THEN 0
END
FROM MemberTable M
Trying to get information from table for User based on data provided. What I am trying to do up there is check to see if User provided correct Email, if User provided right password and if User provided right zip code. Basically, I need to return bit values to my client application from this. Ran into some problem with my SELECT CASE statement up there. Need help please. Thanks in Advance.

View Replies !

---