Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Not Exists Returns Nothing

    Hi

    In this query, I have a 'NOT EXISTS' clause which joins 1 select statement to another (obviously!) Each returns a different number of results when using a selected date. If I run each statement on its own, I get lots of results and I also get lots of reuslts using an 'EXISTS' clause.

    What can I do to ensure that I can return only a few results when running the whole piece of SQL?:

    Code:
    select ZCLIDE, ZPOIDE, ZP2DEV, ImportDate
    from eqestp2 AS EQ                     	
    WHERE CONVERT(varchar, CONVERT(datetime, eq.ImportDate, 121), 105) + ' 00:00:00.000'
    --WHERE CONVERT(varchar(10), eq.ImportDate, 121) + ' 00:00:00.000'
    = CONVERT(varchar (10), DATEADD([day], -1, CONVERT(datetime, '03/10/2008', 103)), 105)  + ' 00:00:00.000' 
    
    AND NOT EXISTS
    -- 2008-10-03 09:18:00
    (select ZCLIDE, ZPOIDE, ZP2DEV, ImportDate
    from eqestp2 as eq2
    WHERE CONVERT(varchar, CONVERT(datetime, eq2.ImportDate, 121), 105) + ' 00:00:00.000'
    --WHERE CONVERT(varchar(10), eq2.importdate, 121) =
    --where CONVERT(varchar(10), CONVERT(datetime, eq2.ImportDate, 121), 105) =
    = CONVERT(varchar (10), CONVERT(datetime, '03/10/2008', 103), 105) + ' 00:00:00.000'
    AND eq2.zpoide = eq.zpoide)

    For reference, the table definition is as follows:

    Code:
    CREATE TABLE [Eqestp2] (
    	[ZCLIDE] [nvarchar] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
    	[ZPOIDE] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
    	[ZP2DEV] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    	[P2PORT] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    	[P2PNAM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
    	[ZOPDAT] [nvarchar] (9) COLLATE Latin1_General_CI_AS NULL ,
    	[importdate] [smalldatetime] NULL CONSTRAINT [DF_Eqestp2_importdate] DEFAULT (getdate())
    ) ON [PRIMARY]
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's the query supposed to do, in lamens terms?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2008
    Posts
    4
    Sorry, my apologies for attempting to jump to conclusions....

    The query looks for customer portfolios between 1 day and the next. Say from 27/05/2008 to 28/05/2008. If we find portfolios that appeared on the 27/05/2008 but did not appear on the 28th, then we deem that portfolio 'missing' and want to print out the entire database row.

    Initially, I just compared the date in the table to getdate() but the datetime in the table has different times for each of the records and so I need to strip out the time from the table date and the users selected date e.g. '03/10/2008'

    Hope this logic enables you to see how I can change this query to make it work.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This any good?
    Code:
    CREATE TABLE dbo.mbewers980 (
       customer_id int
     , import_date smalldatetime
     , description varchar(30)
    )
    
    INSERT INTO dbo.mbewers980 (customer_id, import_date, description)
          SELECT 1, '20080527', 'found'
    UNION SELECT 1, '20080528', 'found'
    UNION SELECT 2, '20080527', 'portfolio missing'
    UNION SELECT 3, '20080527', 'portfolio missing'
    UNION SELECT 4, '20080528', 'not in test scope'
    UNION SELECT 5, '20080525', 'dates out of our test scope'
    
    ----------------------------
    
    DECLARE @the_date datetime
        SET @the_date = '20080527'
        --Truncate time value
        SET @the_date = DateAdd(dd, DateDiff(dd, 0, @the_date), 0)
    
    ----------------------------
    SELECT today.*
    FROM   (
            SELECT customer_id
                 , import_date
                 , description
            FROM   dbo.mbewers980
            WHERE  import_date = @the_date
           ) As [today]
     LEFT
      JOIN (
            SELECT customer_id
                 , import_date
                 , description
            FROM   dbo.mbewers980
            WHERE  import_date = DateAdd(dd, 1, @the_date)
           ) As [tomorrow]
        ON today.customer_id = tomorrow.customer_id
    WHERE  tomorrow.customer_id IS NULL
    
    GO
    DROP TABLE dbo.mbewers980
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Argueably neater.
    Code:
    SELECT today.*
    FROM   dbo.mbewers980 As [today]
     LEFT
      JOIN (
            SELECT customer_id
                 , import_date
                 , description
            FROM   dbo.mbewers980
            WHERE  import_date = DateAdd(dd, 1, @the_date)
           ) As [tomorrow]
        ON today.customer_id = tomorrow.customer_id
    WHERE  today.import_date = @the_date
    AND    tomorrow.customer_id IS NULL
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    Code:
    SELECT		t.customer_id,
    		t.import_date,
    		t.description
    FROM		dbo.mbewers980 AS t
    LEFT JOIN	dbo.mbewers980 AS x ON x.customer_id = t.customer_id
    			AND x.import_date = DATEADD(DAY, 1, @the_date)
    WHERE		t.import_date = @the_date
    		AND x.customer_id IS NULL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice approach on the join Peso, I feel silly that I didn't think of it first
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    Nah, don't worry.
    It's just me bering bored right now...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •