CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
bipro
bipro

Reputation: 23

Conditional union in SQL Server until it reaches a specific date

I have a table with dates from first of previous year till last month i.e., Jan 2015 to Sep 2016.

I have to perform a two months comparison in the same table as below i. e., select all rows in Jan month from table1 except rows in Feb month from table 1 ; select all rows in Feb month except rows in march month from table1.....

Till it reaches last month.

Here I need do conditional union for each two month comparison till it reaches last month.

Sample:

select col1,col2 from table1 where month=jan 2015 
except 
select col1,col2 from table1 where month=feb 2015
union
select col1,col2 from table1 where month=feb 2015 
except 
select col1,col2 from table1 where month=mar 2015
.
.
.
union
select col1,col2 from table1 where month=aug 2016 
except 
select col1,col2 from table1 where month=sep 2016

Sample data:

Customer    Product       date       amount
-------------------------------------------    
a           p1            1/31/2015  $12
a           p2            1/31/2015  $13
a           p2            2/28/2015  $1

So here product p1 exist in jan and no longer in feb 2015. So I need all products in Jan which are not there in Feb 2015 ....

Can anyone suggest a solution?

Upvotes: 0

Views: 269

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

Please check this.

These are the good

select      *

from       (select      t.*
                       ,max (sale_month) over (partition by customer_id,product_id,sale_year)  as last_sale_month_of_year

            from        t
            ) t

where       t.last_sale_month_of_year in (1,12) -- Jan,Dec
        or  (    sale_year                  = year(getdate())
             and last_sale_month_of_year    = moth(getdate()) 
            )
;

These are the bad

select      *

from       (select      t.*
                       ,max (sale_month) over (partition by customer_id,product_id,sale_year)  as last_sale_month_of_year

            from        t
            ) t

where       t.last_sale_month_of_year not in (1,12) -- Jan,Dec
        and  (   sale_year                  <> year(getdate())
             or  last_sale_month_of_year    <> moth(getdate()) 
            )
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

First, you appear to have all data in a single table. Under such circumstances, union all (and related operations) are not usually necessary.

Second, you seem to want rows that are in one month but not the next month.

I would think something like this:

select t.*
from table1 t
where not exists (select 1
                  from table1 t2
                  where t2.col1 = t.col1 and t2.col2 = t.col2 and
                        t2.month = t.month + 1
                 ) and
      month >= jan 2015 and
      month <= sep 2016;

Of course, you haven't shown what the data actually looks like, so I don't know what t.month + 1 really should look like. But the idea is to add one month to the "month" value.

Upvotes: 0

Related Questions