sql server - SQL 2 counts or 2 sums when a date column is selected -

i've been trying figure out, can't right.

i want select id, table s date, 2 different counts number of type = 1 , type = 2 date in table p >= max date table s note = j, , date difference between max table p date , table s date.

example table data:

table s      date          id    note 2/26/2014   688606  j 2/14/2014   688606  j   table p  date          id    type 7/10/2015   688606  1 7/9/2015    688606  1 7/8/2015    688606  1 7/7/2015    688606  2 1/2/2010    688606  1 1/1/2010    688606  2 

ideal result set:

s.date      s.id    p.maxdate   p.t1count   p.t2count   p.datediff 2/26/2014   688606  7/10/2015       3            1         499 

i've tried sums, can't figure out how incorporate dates since sum requires group , dates unique.

i tried counts partition id, makes count include data in table p rather dates >= table s.

any appreciated.

thanks,

here's go @ 1 count:

select  s.id, s.date sdate, p.date pdate, p.type, count(p.date) on (partition p.id) nbr_t1, (p.date - s.date)as nbr_dates p join  ( select distinct(s.id), s.date, s.note   s  s.date = (select max(ss.date) ss ss.id = s.id , ss.note = 'j') , s.id = '688606' ) s on p.id = s.id p.type = '1'  , p.id = '688606' , p.date >= s.date  order  p.date desc 

this easiest if aggregate data in s first—i ctes this—and apply necessary aggregation corresponding data in p. so:

-- sample data. declare @s table ([date] date, [id] bigint, [note] char(1)); insert @s values     ('2014-02-26', 688606, 'j'),     ('2014-02-14', 688606, 'j');  declare @p table ([date] date, [id] bigint, [type] int); insert @p values     ('2015-07-10', 688606, 1),     ('2015-07-09', 688606, 1),     ('2015-07-08', 688606, 1),     ('2015-07-07', 688606, 2),     ('2010-01-02', 688606, 1),     ('2010-01-01', 688606, 2);  -- step 1: aggregate , filter records want in @s. [groups] (     select         [s].[id],         [date] = max([s].[date])             @s [s]             [s].[note] = 'j'     group         [s].[id] )  -- step 2: aggregate corresponding records in @p. select     [s.date] = [groups].[date],     [s.id] = [groups].[id],     [p.maxdate] = max([p].[date]),     [p.t1count] = sum(case [p].[type] when 1 1 else 0 end),     [p.t2count] = sum(case [p].[type] when 2 1 else 0 end),     [p.datediff] = datediff(day, [groups].[date], max([p].[date]))     [groups]     inner join @p [p] on [groups].[id] = [p].[id]     [groups].[date] <= [p].[date] group     [groups].[date],     [groups].[id];