CODEWITHSUNDEEP
sqlsql-server
chawky
chawky

Reputation: 91

Querying data with different conditions on multiple columns

I have a table with date column and 3 different other columns: X, Y, Z.
I want the get the date, X and Y for last month and Z column the sum for all the previous dates.

This is the query that I worked on:

SELECT date,
  x,
  y,
  (
    SELECT z
    FROM Table
    WHERE date < DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
  )
FROM Table
WHERE date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND
  DATEADD(DAY, -(DAY(GETDATE())), GETDATE());

Here's some sample data:

enter image description here

And here's how I want the the result:

date       | X | Y | Z  
-----------+---+---+---  
2019-08-01 | 8 | 8 | 14 
2019-08-02 | 2 | 2 | 14

The date, X and Y only for last month and Z the sum of all time (1+1+2+8+2=14).

Upvotes: 3

Views: 395

Answers (3)

computercarguy
computercarguy

Reputation: 2463

You are on the right track, but you're missing a few things.

SELECT max(OrderDate), CustomerID, EmployeeID, ( select sum(ShipperID) from [Orders] WHERE OrderDate <= '1996-07-15' ) FROM [Orders] WHERE OrderDate <= '1996-07-15';

SELECT OrderDate, CustomerID, EmployeeID, ( select sum(ShipperID) from [Orders] inside WHERE inside.OrderDate <= outside.OrderDate ) FROM [Orders] outside WHERE OrderDate between '1996-07-11' and '1996-07-15';

WITH SumZ as (select sum(ShipperID) as zColumn from [Orders] inside
    WHERE inside.OrderDate <= '1996-07-15')

SELECT OrderDate, CustomerID, EmployeeID, SumZ.zColumn
FROM [Orders] outside, SumZ
WHERE OrderDate between '1996-07-11' and '1996-07-15';

I used a different dataset, but to the same effect.*

https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

Since you want all the z columns to be the same, using a WITH (actually called a "common table expression" or CTE) prevents your query from running the same sub-query multiple times. Since there's nothing to JOIN with, I didn't do one, but you can JOIN on a CTE just like any other table, view, etc.

Further readin on CTEs:
https://www.essentialsql.com/introduction-common-table-expressions-ctes/
and
When to use Common Table Expression (CTE)

It queries the Orders table based on the "largest" Date, gets the data from 3 columns of that row, and sums the values of a 4th column based on the same criteria as the outer query.

*In my example, the data is just there for an example and doesn't mean anything useful. Also, I simplified the date selection, since the data I used didn't actually include a Date column. Turns out, it is a Date column.

Upvotes: 1

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

This checks for last month. Updated for new sample data.

select
(select t2.x from sample t2 where (month(t2.date) = month(getdate()) - 1) and year(t2.date) = year(getdate())
and t2.date = (select max(t3.date) from sample t3)) as x,
(select t2.y from sample t2 where (month(t2.date) = month(getdate()) - 1) and year(t2.date) = year(getdate())
and t2.date = (select max(t3.date) from sample t3)) as y,
    sum(t1.z) as z
from sample t1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Here is one way to do what you want:

select t.date, t.x, t.y, t.new_z
from (select t.*, sum(z) over () as new_z,
             rank() over (order by year(date) desc, month(date) desc) as seqnum
      from t
     ) t
where seqnum = 1;

Actually, you could also do:

select top (1) with ties t.date, t.x, t.y, sum(z) over () as z
from t
order by year(date) desc, month(date) desc;

Upvotes: 2

Related Questions