SQL query

Transact-SQL
#1

Please help me to solve this problem.

create table dbo.lib (
lib_id INT,
name varchar(10),
book varchar(10),
flag INT,
Month_year DATE,
event_date DATE
)

INSERT INTO lib
select 1,'C1', 'Book1', 1,'2018-05-01', '2018-05-02' UNION ALL
select 2,'C1', 'Book1', 2,'2018-05-01', '2018-05-04' UNION ALL

select 3,'C2', 'Book2', 2,'2018-05-01', '2018-05-02' UNION ALL
select 4,'C2', 'Book2', 1,'2018-05-01', '2018-05-04' UNION ALL

select 5,'C3', 'Book3', 1,'2018-05-01', '2018-05-02' UNION ALL
select 6,'C3', 'Book3', 2,'2018-05-01', '2018-05-04' UNION ALL
select 7,'C3', 'Book3', 1,'2018-05-01', '2018-05-06' UNION ALL

select 8,'C4', 'Book4', 2,'2018-05-01', '2018-05-02' UNION ALL
select 9,'C4', 'Book4', 1,'2018-05-01', '2018-05-04' UNION ALL
select 10,'C4','Book4', 2,'2018-05-01', '2018-05-06'

1 is rented
2 is returned

Capture

i want to result only the white rows and want to suppress the grey ones.

the logic is if the user has first rented and then returned within same month , it will cancel out.
If the user returned and rent again , it should show up .

#2

i tried this .

select * from lib T1
where not exists (
select 1 from lib T2 WHERE T1.name=T2.name and t1.book = T2.book
and t1.Month_year=T2.Month_year and T1.event_date<=T2.event_date and T1.flag=1 and T2.flag= 2 )
and t1.Month_year='2018-05-01'
order by name, book

OR....

SELECT name, book,flag, month_year ,event_date, flag_adjustment
FROM (
Select name, book,flag, month_year ,event_date
, SUM( CASE flag WHEN 2 THEN -1 ELSE 1 END)
OVER (PARTITION BY name, book order by event_date)As flag_adjustment
from dbo.lib (NOLOCK)

) CTE
WHERE flag_adjustment<>0 and month_year='2018-05-01'

not getting the expected result.

#3

According to your data, Book4 wasn't rented out prior to 5/2/2018, so why do you want to see lib_id 8? And why don't you want to see lib_id 9 and 10?

1 Like
#4

Book4 was booked in previous month .I did not have any entry . I am sorry about that.

So rule is if a book was first rented and then return in the same month , it should cancel out and not show up.

So in this case Lib_id 9 and 10 will cancel out.but lib_id 8 will remain , as the corespoding rent would have been in previous month .

#6

Try this:

with cte
  as (select a.lib_id as id1
            ,b.lib_id as id2
        from dbo.lib as a
             inner join dbo.lib as b
                     on b.book=a.book
                    and b.event_date>a.event_date
                    and b.flag=2
       where a.month_year=cast('2018-05-01' as date)
         and a.flag=1
     )
select *
  from dbo.lib as a
 where not exists (select 1
                     from cte as b
                    where a.lib_id in (b.id1,b.id2)
                  )
;
1 Like
#7

Amazing...