CODEWITHSUNDEEP
sqlsql-server-2005
Mike Station
Mike Station

Reputation: 193

what wrong with this script sql?

I have table users with columns ID,USERSID table :

  t_A(ID,usersID,ADATE,priceA,priceB)
  t_B(ID,usersID,BDATE,priceA,priceB)
  t_C(ID,usersID,CDATE,priceA,priceB)

I'm using this query to get SUM of price from 3 tables for X DATE , and USERSID

    declare @id int 
    set @id = 3 -- for example 

    SELECT SUM(priceA) as TA, SUM(priceB) as TB
    FROM  t_A,t_B,t_C
    WHERE  t_A.USERSID =  @id
    AND     t_B.USERSID =  @id
    AND     t_C.USERSID =  @id
    AND    ADATE >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())) 
   AND     BDATE >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())) 
   AND    CDATE >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate()))

this script work only if the USERSID had a row in the three tables otherwise script return nothing

Upvotes: 1

Views: 72

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Because if only one table has no USERID = 3 that result always will be empty. Workaround: use option with UNION ALL operator

DECLARE @id int 
SET @id = 3
SELECT SUM(x.priceA) as TA, SUM(x.priceB) AS TB
FROM (
      SELECT priceA, priceB
      FROM t_A 
      WHERE t_A.USERSID =  @id
        AND ADATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))
      UNION ALL
      SELECT priceA, priceB
      FROM t_B 
      WHERE t_B.USERSID =  @id
        AND BDATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))
      UNION ALL   
      SELECT priceA, priceB
      FROM t_C 
      WHERE t_C.USERSID =  @id
        AND CDATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))
      ) x

Upvotes: 1

Michael Harmon
Michael Harmon

Reputation: 746

I think this is what you need. This way, it will return results as long as one of the tables satisfies the condition:

SELECT SUM(priceA) as TA, SUM(priceB) as TB, SUM(priceC) as TC
    FROM  t_A
    FULL JOIN t_B
    FULL JOIN t_C
    WHERE  t_A.USERSID =  @id
    AND     t_B.USERSID =  @id
    AND     t_C.USERSID =  @id
    AND    ADATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))
   AND     BDATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))
   AND    CDATE >= DATEDIFF(d,0,dateadd(d,0,getdate()))

Upvotes: 0

criticalfix
criticalfix

Reputation: 2870

You can't compare a date column with a datediff. Here are some things you could do:

AND ADATE >= GETDATE()

or:

AND DATEDIFF(d, ADATE, GETDATE()) > 1

You can check the syntax and some examples at MSDN.

Upvotes: 0

Related Questions