root/trunk/SQL/StoredProcedures/sp_GetClientsWithNoContact.txt @ 669

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE  sp_GetClientsWithNoContact
        -- Add the parameters for the stored procedure here
        @agencyId int,
        @numberOfDaysStart int, 
        @numberOfDaysEnd int
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        select top 2000 K.* from KLIENCI K 
    left join dbo.KONTAKTY K2 on K.CustomerId = K2.CustomerID 
    left join dbo.AGENCI A on K.UserName = A.Symbol 
    group by K.NameInMagazine, K.AgentExpo, K.kodKlienta, K.CustomerID, K.FirstName, K.LastName, K.OrganizationName, 
    K.Address, K.City, K.State, K.PostalCode, K.Country, K.Nip, K.ContactName, K.PhoneNumber, K.FaxNumber, 
    K.Note, K.data, K.Adres_Fkatura, K.Adres_Kor, K.Platnik_VAT, K.Aktywny,  
    K.Email, K.UserName, K.Last_Modify, K.Modify_User, K.http, K.VIES, K.regon, K.krs, K.osw_nr,K.osw_wazne_do,K.Old_ID, K.NipKraj, A.Id_agencji 
    HAVING datediff(day,max(K2.data),getdate()) > @numberOfDaysStart AND datediff(day,max(K2.data),getdate()) < @numberOfDaysEnd 
    AND A.Id_agencji = @agencyId order by datediff(day,max(K2.data),getdate()) desc

END
GO