CODEWITHSUNDEEP
sql-server
Sajith Sudhi
Sajith Sudhi

Reputation: 55

how to eliminate zero with a union query

I have table and when i query, i get

SELECT code
  ,year(prime_date) AS year
  ,month(prime_date) AS currentmonth
  ,0 AS lastmonth
  ,count(*) AS currentmonth_vol
  ,0 AS lastmonth_vol
FROM TableA
WHERE month(prime_date) = month(CURRENT_DATE)
  ,year(prime_date) = year(CURRENT_DATE)
GROUP BY code
  ,year
  ,month(prime_date)
  ,year(CURRENT_DATE)

code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    4             450                0            0          
bh    2000    4             333                0            0

SELECT code
  ,year(prime_date) AS year
  ,month(prime_date) AS currentmonth
  ,0 AS lastmonth
  ,count(*) AS currentmonth_vol
  ,0 AS lastmonth_vol
FROM TableA
WHERE month(prime_date) = month(CURRENT_DATE) - 1
  ,year(prime_date) = year(CURRENT_DATE)
GROUP BY code
  ,year
  ,month(prime_date)
  ,year(CURRENT_DATE)

code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    0               0                3            453          
bh    2000    0               0                3            673
kt    2000    0               0                3            838

my question is, is it possible to join the above and eliminate 0 by union query. ?

ans should be 

code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    4               450                3            453          
bh    2000    4               333                3            673
kt    2000    0                0                 3            838

Upvotes: 0

Views: 295

Answers (2)

avery_larry
avery_larry

Reputation: 2135

untested

Not a direct answer to your question. I suggest doing it all in 1 query instead of trying to use union and "eliminate" zeros. Side note -- lastmonth column seems a little silly, and it will be wrong (0) the way you have it written when current month is January.

This presumes CURRENT_DATE is somehow a constant. I wrote it here as a variable set to the current date.

declare @CURRENT_DATE datetime = getdate()
SELECT code
  ,year(@CURRENT_DATE) AS year
  ,month(@CURRENT_DATE) AS currentmonth
  ,month(dateadd("m", -1, @CURRENT_DATE)) AS lastmonth
  ,sum(case when month(prime_date) = month(@CURRENT_DATE) then 1 else 0 end) AS currentmonth_vol
  ,sum(case when month(prime_date) = month(@CURRENT_DATE) then 0 else 1 end) AS lastmonth_vol
FROM TableA
WHERE prime_date >= datefromparts(year(dateadd("m", -1, @CURRENT_DATE)), month(dateadd("m", -1, @CURRENT_DATE)), 1)
    and prime_date < datefromparts(year(dateadd("m", 1, @CURRENT_DATE)), month(dateadd("m", 1, @CURRENT_DATE)), 1)
GROUP BY code

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

Try the following, not tested:

SELECT
    code,
    year,
    MAX(currentmonth) AS currentmonth,
    SUM(currentmonth_vol) AS currentmonth_vol
    lastmonth,
    lastmonth_vol
FROM
    (SELECT * FROM resulta UNION SELECT * FROM resultb) AS p
GROUP BY
    code, year, lastmonth

Upvotes: 1

Related Questions