SQL Server SQL Select Where date in (Jan and March) but not in Feb

esyap4oy 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(116)

I have a table like this in SQL called Balance

+----+-----------+-------+------+
    | id | accountId | Date  | Type |
    +----+-----------+-------+------+
    | PK | FK        | Date  | Int  |
    +----+-----------+-------+------+

I need to find the accountId s that has balance entries in January and March, but not in Febuary. Only in 2018 and Type should be 2.

How would I go about writing my sql select statement?

Thanks

Edit: What's I've done so far: Selecting rows that either in Jan OR March is not a problem for me.

SELECT AccountId, Date FROM Balance
WHERE Month(Date) in (1,3) AND YEAR(Date) = 2018 AND Type =2
ORDER BY AccountId, Date

But if an AccountId has a single entry, say in January, then this will be included. And that's not what I want. Only if an Account has entries in both Jan and March, and not in Feb is it interesting.

I suspect Group BY and HAVING are keys here, but I'm unsure how to proceed

sql-server

来源：https://stackoverflow.com/questions/49155423/sql-select-where-date-in-jan-and-march-but-not-in-feb

2条答案

按热度按时间

i5desfxk1#

I would do this using aggregation:

select b.accountid
from balance b
where date >= '2018-01-01' and date < '2019-01-01'
group by b.accountid
having sum(case when month(date) = 1 then 1 else 0 end) > 0 and  -- has january
       sum(case when month(date) = 3 then 1 else 0 end) > 0 and  -- has march
       sum(case when month(date) = 2 then 1 else 0 end) = 0  -- does not have february

赞(0）回复(0）举报 2023-08-02

r55awzrz2#

You can entirely avoid grouping by using exists:

select distinct b.accountid
from balance b
where exists (
        select *
        from balance b1
        where month(b1.date) = 1 and b.accountid = b.accountid
    ) and exists (
        select *
        from balance b1
        where month(b1.date) = 3 and b.accountid = b.accountid
    ) and not exists (
        select *
        from balance b1
        where month(b1.date) = 2 and b.accountid = b.accountid
    )

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL Select Where date in (Jan and March) but not in Feb

2条答案

相关问题

热门标签

最新问答