CODEWITHSUNDEEP
sqlsql-server
Fawad Jalal
Fawad Jalal

Reputation: 233

SQL with as expression shows multiple results

I am writing a SQL query using with as expression. I always get a result in the square of what I required.

This is my query:

DECLARE @MAX_DATE AS INT

SET @MAX_DATE = (SELECT DATEPART(MONTH,FECHA) FROM ALBVENTACAB WHERE NUMALBARAN IN (SELECT DISTINCT MAX(NUMALBARAN) FROM ALBVENTACAB));

;WITH TABLE_LAST AS (
SELECT CONCAT(DATEPART(MONTH,FECHA),'-',DATEPART(YEAR,FECHA)) as LAST_YEAR_MONTH
,SUM(TOTALNETO) AS LAST_YEAR_VALUE
FROM ALBVENTACAB
WHERE DATEPART(YEAR,CURRENT_TIMESTAMP) -1 = DATEPART(YEAR,FECHA) AND NUMSERIE LIKE 'A%'
AND DATEPART(MONTH,FECHA) <= @MAX_DATE
GROUP BY CONCAT(DATEPART(MONTH,FECHA),'-',DATEPART(YEAR,FECHA))
)

,TABLE_CURRENT AS(
SELECT CONCAT(DATEPART(MONTH,FECHA),'-',DATEPART(YEAR,FECHA)) as CURR_YEAR_MONTH
,SUM(TOTALNETO) AS CURR_YEAR_VALUE
FROM ALBVENTACAB
WHERE DATEPART(YEAR,CURRENT_TIMESTAMP) <= DATEPART(YEAR,FECHA) AND NUMSERIE LIKE 'A%'
GROUP BY CONCAT(DATEPART(MONTH,FECHA),'-',DATEPART(YEAR,FECHA))
)
SELECT *
FROM TABLE_CURRENT, TABLE_LAST

When I run the query I get exactly the square of the result.

I want to compare sale monthly with last year.

2-2020  814053.3    2-2019  840295.1
1-2020  1094993.65  2-2019  840295.1
3-2020  293927.3    2-2019  840295.1
2-2020  814053.3    1-2019  1050701.68
1-2020  1094993.65  1-2019  1050701.68
3-2020  293927.3    1-2019  1050701.68
2-2020  814053.3    3-2019  887776.1
1-2020  1094993.65  3-2019  887776.1
3-2020  293927.3    3-2019  887776.1

I should get only 3 rows instead of 9 rows.

Upvotes: 0

Views: 84

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

While you already got the answer on how to join the two results, I thought I'd tell you how to typically approach such problems.

From the same table, you want two sums on different conditions (different years that is). You solve this with conditional aggregation, which does just that: aggregate (sum) based on a condition (year).

select
  datepart(month, fecha) as month,
  sum(case when datepart(year, fecha) = datepart(year, getdate()) then totalneto end) as this_year,
  sum(case when datepart(year, fecha) = datepart(year, getdate()) -1 then totalneto end) as last_year
from albventacab
where numserie like 'A%' 
  and fecha > dateadd(year, -2, getdate())
group by datepart(month, fecha)
order by datepart(month, fecha);

Upvotes: 1

marc_s
marc_s

Reputation: 754538

You need to properly join your two CTE - the way you're doing it now, you're getting a Cartesian product of each row in either CTE together.

Do something like:

*;WITH TABLE_LAST AS 
( .... 
),
TABLE_CURRENT AS
( .... 
)
SELECT *
FROM TABLE_CURRENT curr
INNER JOIN TABLE_LAST last ON (some join condition here)

What that join condition is going to be - I have no idea, and cannot tell from your question - but you have to define how these two sets of data "connect" ....

It could be something like:

SELECT *
FROM TABLE_CURRENT curr
INNER JOIN TABLE_LAST last ON curr.CURR_YEAR_MONTH = last.LAST_YEAR_MONT

or whatever else makes sense in your situation - but basically, you need to somehow "tie together" these two sets of data and get only those rows that make sense - not just every row from "last" combined with every row from "curr" ....

Upvotes: 2

Related Questions