Modify query with sql server

Question

I have the following query

SELECT      distinct DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as Date
FROM         [Vente A]

union 

SELECT      distinct DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
FROM         [Vente B]

UNION 

SELECT      distinct DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
FROM           [Budget B]

UNION 

SELECT      distinct DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
FROM            [Budget A]

I need to select date from Vente A and Vente B then select select from budget A and budget B where date is less than the max date from Vente A and Vente B

how can i do it ?

Please edit your query with sample data and desired results. Your logic is hard to follow, particularly without any data that explains what you really want. — Gordon Linoff
– Gordon Linoff, Commented Oct 24, 2015 at 22:05

James Z · Accepted Answer · 2015-10-25 04:03:30Z

I am assuming you want this: Pick the max date from the union od Vente A and B. Then show all rows from union of Budget A and B where data is less than the max date selected above.

SELECT BudgetDate
FROM (
  SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) AS BudgetDate
  FROM [Budget B]

  UNION

  SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
  FROM [Budget A]
  ) BudgetAB
WHERE BudgetDate < (
    SELECT MAX(DATE)
    FROM (
      SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) AS DATE
      FROM [Vente A]

      UNION

      SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
      FROM [Vente B]
      ) AS VenteAB
    )

or, like this

;WITH BudgetAB
AS (
  SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) AS BudgetDate
  FROM [Budget B]

  UNION

  SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
  FROM [Budget A]
  )
SELECT BudgetDate
FROM BudgetAB
WHERE BudgetDate < (
    SELECT MAX(DATE)
    FROM (
      SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) AS DATE
      FROM [Vente A]

      UNION

      SELECT DISTINCT DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0)
      FROM [Vente B]
      ) VenteAB
    )

Collectives™ on Stack Overflow

Modify query with sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related