Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- | DATE | USER_ID | GROUP_ID |
- | 2012-09-20 00:00:00 | 7 | 1 |
- | 2012-09-20 00:00:00 | 6 | 1 |
- | 2012-09-13 00:00:00 | 5 | 1 |
- | 2012-09-10 00:00:00 | 1 | 1 |
- | 2012-09-02 00:00:00 | 5 | 3 |
- | 2012-08-02 00:00:00 | 5 | 2 |
- | 2012-07-01 00:00:00 | 5 | 1 |
- | 2012-07-01 00:00:00 | 3 | 1 |
- SELECT *
- FROM user_group
- where group_id = 1
- and '2012-09-11' <= date
- and date <= '2012-09-20'
- UNION
- SELECT TOP 1 *
- FROM user_group
- where group_id = 1
- and date <= '2012-09-11'
- order by date desc
- | DATE | USER_ID | GROUP_ID |
- | 2012-09-20 00:00:00 | 7 | 1 |
- | 2012-09-20 00:00:00 | 6 | 1 |
- | 2012-09-13 00:00:00 | 5 | 1 |
- | 2012-09-10 00:00:00 | 1 | 1 |
- | 2012-07-01 00:00:00 | 3 | 1 |
- select * from user_group where group_id=1 and date<=convert(date,'2012-09-20',120)
- except
- select * from user_group where group_id=1 and date<=convert(date,'2012-09-11',120)
- select date, user_id, group_id
- from
- (
- select *, ROW_NUMBER() over (partition by user_id order by date desc) rn
- from user_group
- where date<=convert(date,'2012-09-20',120)
- ) v
- where rn=1
- and group_id=1
- order by date desc
- db.user_group.Where(g => g.group_id = group_id &&
- g.date =< to_date);
- var innerAssoc = from assoc in user_group
- where assoc.group_id == group_id
- && dateFrom.Date <= assoc.date.Date
- && date.Date <= dateTo.Date
- select assoc;
- var outerAssoc = from assoc in user_group
- where assoc.group_id == group_id
- && assoc.date.Date == user_group
- .Where(a => a.user_id == assoc.user_id && a.date.Date <= dateFrom.Date)
- .Select(a => a.date.Date).Max()
- select assoc;
- var res = innerAssoc.Union(outerAssoc);
Add Comment
Please, Sign In to add comment