Forum Discussion

JoeyDeacan's avatar
JoeyDeacan
Copper Contributor
Jun 12, 2024

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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 12, 2024

    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

     

    • JoeyDeacan's avatar
      JoeyDeacan
      Copper Contributor
      Jun 12, 2024
      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.
      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        Jun 13, 2024

        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

Resources