what wrong with this script sql?

Question

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

What do you mean it returns NULL? You have three columns in your SELECT statement. Does it return NULL for all of them? One of them? An error (which is what I would expect from the code above)? — Jeff Rosenberg
– Jeff Rosenberg, Commented Apr 15, 2013 at 12:41
Also priceC doesn't appear to be a thing, based on your table structure. — Jeff Rosenberg
– Jeff Rosenberg, Commented Apr 15, 2013 at 12:43
this script work only if the USERSID had a row in the three tables otherwise script return nothing — Mike Station
– Mike Station, Commented Apr 15, 2013 at 12:47

Oleksandr Fedorenko · Accepted Answer · 2013-04-15 13:10:36Z

1

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

edited Apr 15, 2013 at 13:10

answered Apr 15, 2013 at 12:55

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

criticalfix · Accepted Answer · 2013-04-15 12:47:59Z

0

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.

answered Apr 15, 2013 at 12:47

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Comments

Michael Harmon · Accepted Answer · 2013-04-15 12:54:35Z

0

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()))

answered Apr 15, 2013 at 12:54

Michael Harmon

7465 silver badges10 bronze badges

Collectives™ on Stack Overflow

what wrong with this script sql?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related