# Query Problem

Copper Contributor

# Query Problem

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

3 Replies

# Re: Query Problem

@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``````

# Re: Query Problem

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.

# Re: Query Problem

@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``````