Forum Discussion
JoeyDeacan
Jun 12, 2024Copper 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...
olafhelper
Jun 12, 2024Bronze Contributor
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
- JoeyDeacanJun 12, 2024Copper ContributorThank 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.- olafhelperJun 13, 2024Bronze Contributor
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