Did anybody have this problem

• rkordonsky 63916

  SSC Eights!

  Points: 812

  More actions

  December 5, 2017 at 12:39 pm

  #402726

  I have simple query:
  SELECT (select ISNULL(SUM(TOTAL_PAYMENTS),0) from Orders where ST_ID IN(SELECT ID FROM Name where CO_ID = 11750) and ORDER_DATE > '2015-05-08 00:00:00.000') + (select ISNULL(SUM(TOTAL_PAYMENTS),0) from Orders where ST_ID = 11750 and ORDER_DATE > '2015-05-08 00:00:00.000') / (Select (DATEDIFF(YEAR,'2015-05-08 00:00:00.000',GetDATE())))
  First subquery returns 1370.00 second one is two. However when I run the whole query I am still getting 1370.00 instead of 685.00 (1370.00 / 2). Do I do something wrong here? Please advise. Thank you.

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  December 5, 2017 at 12:51 pm

  #1970782

  rkordonsky 63916 - Tuesday, December 5, 2017 12:39 PM

  I have simple query:
  SELECT (select ISNULL(SUM(TOTAL_PAYMENTS),0) from Orders where ST_ID IN(SELECT ID FROM Name where CO_ID = 11750) and ORDER_DATE > '2015-05-08 00:00:00.000') + (select ISNULL(SUM(TOTAL_PAYMENTS),0) from Orders where ST_ID = 11750 and ORDER_DATE > '2015-05-08 00:00:00.000') / (Select (DATEDIFF(YEAR,'2015-05-08 00:00:00.000',GetDATE())))
  First subquery returns 1370.00 second one is two. However when I run the whole query I am still getting 1370.00 instead of 685.00 (1370.00 / 2). Do I do something wrong here? Please advise. Thank you.

  Yes, you forgot to provide sample data and expected results.  Details in the first link in my signature.

  You also forgot to include carriage returns to make your query easier to read.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  December 5, 2017 at 12:51 pm

  #1970783

  And here's that query again, for those with standard-width screens 🙂
  SELECT
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID IN (
          SELECT ID
          FROM Name
          WHERE CO_ID = 11750
        )
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) +
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID    = 11750
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) /
  (
   SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
  );

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 5, 2017 at 1:20 pm

  #1970792

  Phil Parkin - Tuesday, December 5, 2017 12:51 PM

  And here's that query again, for those with standard-width screens 🙂
  SELECT
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID IN (
          SELECT ID
          FROM Name
          WHERE CO_ID = 11750
        )
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) +
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID    = 11750
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) /
  (
   SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
  );

  Try this:

  SELECT
((
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID IN (
SELECT ID
FROM Name
WHERE CO_ID = 11750
)
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) +
(
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID = 11750
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) )/
(
SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
);

• rkordonsky 63916

  SSC Eights!

  Points: 812

  More actions

  December 5, 2017 at 1:37 pm

  #1970797

  Lynn Pettis - Tuesday, December 5, 2017 1:20 PM

  Phil Parkin - Tuesday, December 5, 2017 12:51 PM

  And here's that query again, for those with standard-width screens 🙂
  SELECT
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID IN (
          SELECT ID
          FROM Name
          WHERE CO_ID = 11750
        )
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) +
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID    = 11750
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) /
  (
   SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
  );

  Try this:

  SELECT
((
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID IN (
SELECT ID
FROM Name
WHERE CO_ID = 11750
)
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) +
(
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID = 11750
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) )/
(
SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
);

  Thank you, Lynn. It is working! The only difference I found you added a pare of Parentheses. Thank you one more time.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 5, 2017 at 1:42 pm

  #1970800

  rkordonsky 63916 - Tuesday, December 5, 2017 1:37 PM

  Lynn Pettis - Tuesday, December 5, 2017 1:20 PM

  Phil Parkin - Tuesday, December 5, 2017 12:51 PM

  And here's that query again, for those with standard-width screens 🙂
  SELECT
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID IN (
          SELECT ID
          FROM Name
          WHERE CO_ID = 11750
        )
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) +
  (
   SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
   FROM Orders
   WHERE
     ST_ID    = 11750
     AND ORDER_DATE > '2015-05-08 00:00:00.000'
  ) /
  (
   SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
  );

  Try this:

  SELECT
((
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID IN (
SELECT ID
FROM Name
WHERE CO_ID = 11750
)
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) +
(
SELECT ISNULL(SUM(TOTAL_PAYMENTS), 0)
FROM Orders
WHERE
ST_ID = 11750
AND ORDER_DATE > '2015-05-08 00:00:00.000'
) )/
(
SELECT (DATEDIFF(YEAR, '2015-05-08 00:00:00.000', GETDATE()))
);

  Thank you, Lynn. It is working! The only difference I found you added a pare of Parentheses. Thank you one more time.

  Yes, yes I did.  That is because the division was being done before the addition.