Query Problem

[JoeyDeacan]

Good morning,

I wonder if someone could help, I have two queries based on date ranges, I would like to see them side by side but cant work out how to.

select customer, sum(nett)Nett,sum(items)Items,Sum(area)M2
from goorders
where ordered BETWEEN GETDATE()-7 AND GETDATE()
group by customer
order by Nett DESC

select customer, sum(nett)Nett,sum(items)Items,Sum(area)M2
from goorders
where ordered BETWEEN GETDATE()-15 AND GETDATE()-8
group by customer
order by Nett DESC

I would like to see as :-

Customer

Nett (This Week)

Nett (Last Week)

Items (This Week)

Items (Last Week)

M2 ()This Week)

M2 (Lats Week)

Can anyone better than me help please,, i would be vert grateful.

JD

[#2]

@JoeyDeacan , an easy way is to use a condition for the SUM, more complexe is using the extsiting PIVOT function in Transact-SQL. Keep in mind that GETDATE returns a time portion, so you may miss some orders.

Example query:

SELECT G.customer, 
       SUM(CASE WHEN ordered BETWEEN GETDATE() -7 AND GETDATE()     THEN g.nett ELSE 0.00) END AS Nett_This_Week,
       SUM(CASE WHEN ordered BETWEEN GETDATE()-15 AND GETDATE() - 8 THEN g.nett ELSE 0.00) END AS Nett_Last_Week
FROM goorders AS G
where g.ordered BETWEEN GETDATE()-15 AND GETDATE()
GROUP BY G.customer

[#3]

Thank for taking the time, I really appreciate it.

Im getting
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Am i doing something wrong, I just copied exactly and ran.

[#4]

@JoeyDeacan , just the clsoing bracket at the wrong place (I don't have your database to test it)

SELECT G.customer, 
       SUM(CASE WHEN ordered BETWEEN GETDATE() -7 AND GETDATE()     THEN g.nett ELSE 0.00 END) AS Nett_This_Week,
       SUM(CASE WHEN ordered BETWEEN GETDATE()-15 AND GETDATE() - 8 THEN g.nett ELSE 0.00 END) AS Nett_Last_Week
FROM goorders AS G
where g.ordered BETWEEN GETDATE()-15 AND GETDATE()
GROUP BY G.customer