select r.Accountnumber,investmentclass,date
from   ror..ror r
where  r.accountnumber in (select distinct p.portfolio
                           from   midba..performcompletelogmessage l
                                  left join portfolio p
                                    on p.portfolio = substring(l.messagefinal, 36, 11)
                           where  message_type_id = 6055
                                  and p.id is not null
                                  and effective_date_started = '2011-02-04 17:02:45.000')
order  by r.accountnumber,
          r.date 

quote:

---

Originally posted by djorre

Create a table with all month endings and cross join that table with the accountnumbers as tblAllAccount_Months.
Then do a tblAllAccount_Months left join 'your query' and replace your date-nulls by 'missing'?

---

select r.*,
       d.end_of_month_date
from   #date d
       cross join ror..ror r
         where d.end_of_month_date = r.date and
  r.accountnumber in (select distinct p.portfolio
                           from   midba..performcompletelogmessage l
                                  left join portfolio p
                                    on p.portfolio = substring(l.messagefinal, 36, 11)
                           where  message_type_id = 6055
                                  and p.id is not null
                                  and effective_date_started = '2011-02-04 17:02:45.000') 
--and d.end_of_month_date is null
order by r.accountnumber,r.date

select 
q.accountnumber
, q.end_of_month_date
, isnull(cast(t.date as varchar(100)), 'missing')
from   
(select distinct d.end_of_month_date, r.accountnumber from #date d cross join (select distinct accountnumber from ror..ror)r) as q
left join 
ror..ror as t
on q.accountnumber = t.accountnumber and q.end_of_month_date = t.date
where q.accountnumber in ...
order by q.accountnumber, q.date

select 
q.accountnumber
, q.end_of_month_date
, isnull(cast(t.date as varchar(100)), 'missing')
from   
(select distinct d.end_of_month_date, r.accountnumber from (select distinct date as end_of_month_date from ror) as d cross join (select distinct accountnumber from ror..ror)as r) as q
left join 
ror..ror as t
on q.accountnumber = t.accountnumber and q.end_of_month_date = t.date
where q.accountnumber in ...
order by q.accountnumber, q.date

quote:

---

Originally posted by djorre

something like

select 
q.accountnumber
, q.end_of_month_date
, isnull(cast(t.date as varchar(100)), 'missing')
from   
(select distinct d.end_of_month_date, r.accountnumber from #date d cross join (select distinct accountnumber from ror..ror)r) as q
left join 
ror..ror as t
on q.accountnumber = t.accountnumber and q.end_of_month_date = t.date
where q.accountnumber in ...
order by q.accountnumber, q.date

or without the #date table, if all dates would be end of month dates:

select 
q.accountnumber
, q.end_of_month_date
, isnull(cast(t.date as varchar(100)), 'missing')
from   
(select distinct d.end_of_month_date, r.accountnumber from (select distinct date as end_of_month_date from ror) as d cross join (select distinct accountnumber from ror..ror)as r) as q
left join 
ror..ror as t
on q.accountnumber = t.accountnumber and q.end_of_month_date = t.date
where q.accountnumber in ...
order by q.accountnumber, q.date

---

quote:

---

Originally posted by duhaas

quote:

---

Originally posted by djorre

Create a table with all month endings and cross join that table with the accountnumbers as tblAllAccount_Months.
Then do a tblAllAccount_Months left join 'your query' and replace your date-nulls by 'missing'?

---

thanks for the followup, a bit of new territory for me here, can you help out with the following as I appear to be missing something:

select r.*,
       d.end_of_month_date
from   #date d
       cross join ror..ror r
         where d.end_of_month_date = r.date and
  r.accountnumber in (select distinct p.portfolio
                           from   midba..performcompletelogmessage l
                                  left join portfolio p
                                    on p.portfolio = substring(l.messagefinal, 36, 11)
                           where  message_type_id = 6055
                                  and p.id is not null
                                  and effective_date_started = '2011-02-04 17:02:45.000') 
--and d.end_of_month_date is null
order by r.accountnumber,r.date

#date is made up of all distinct month end dates

---

quote:

---

Originally posted by duhaas

quote:

---

Originally posted by duhaas

quote:

---

Originally posted by djorre

Create a table with all month endings and cross join that table with the accountnumbers as tblAllAccount_Months.
Then do a tblAllAccount_Months left join 'your query' and replace your date-nulls by 'missing'?

---

thanks for the followup, a bit of new territory for me here, can you help out with the following as I appear to be missing something:

select r.*,
       d.end_of_month_date
from   #date d
       cross join ror..ror r
         where d.end_of_month_date = r.date and
  r.accountnumber in (select distinct p.portfolio
                           from   midba..performcompletelogmessage l
                                  left join portfolio p
                                    on p.portfolio = substring(l.messagefinal, 36, 11)
                           where  message_type_id = 6055
                                  and p.id is not null
                                  and effective_date_started = '2011-02-04 17:02:45.000') 
--and d.end_of_month_date is null
order by r.accountnumber,r.date

#date is made up of all distinct month end dates

---

one other quick question, is it possible to only have it show me the first month forward for an account and whats missing after its initial month? right now, it goes back to the first month end date available for all accounts in ror. for example:

accountnumber end_of_month_date (No column name)
9841B082000 2003-06-30 00:00:00.000 missing
9841B082000 2003-07-31 00:00:00.000 missing
9841B082000 2003-08-31 00:00:00.000 missing
9841B082000 2003-09-30 00:00:00.000 missing
9841B082000 2003-10-31 00:00:00.000 missing
9841B082000 2003-11-30 00:00:00.000 missing
9841B082000 2003-12-31 00:00:00.000 missing
9841B082000 2004-01-31 00:00:00.000 missing
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM
9841B082000 2004-02-29 00:00:00.000 Feb 29 2004 12:00AM

I really only want it to show me from 2004-02-29 forward in this accounts case, each account would be different

---

create table #result(id int identity(1,1), accountnumber varchar(100), ...... , firstdatetime datetime)
insert into #result (accountnumber,....)
select * from myquery

declare @counter int, @countermax int, @lowestdatee datetime, @accountnumber varchar(100)
set @counter = 0
set @countermax = (select max(id) from #result)

while @counter <= @countermax
 begin
 set @accountnumber = (Select accountnumber from #results where id = @counter)
 set @lowestdatetime = (Select min(date) from #results where accountnumber = @accountnumber)
 update #results set firstdatetime = @lowestdatetime where id = @counter
set @counter = @counter + 1
 end

delete from #results where datetime < firstdatetime

select * from #results

	select top 1000 identity(smallint) as n into integers from sysobjects a cross join sysobjects b
	create unique clustered index ix_integers_n on integers(n)

	select month = dateadd(m, datediff(m, 0, @startdate) + n - 1, 0) from integers
	where n - 1 <= datediff(m, @startdate, getdate())

	select distinct month = dateadd(m, datediff(m, 0, date), 0) from table where date >= @startdate

	select * from ({query from #2 above}) t
	where month not in ({query from #3 above})

change: dateadd(m, datediff(m, 0, @startdate) + n - 1, 0)
to:     dateadd(m, datediff(m, 0, @startdate) + n, 0) - 1

and:    dateadd(m, datediff(m, 0, date), 0)
to:     dateadd(m, datediff(m, 0, date) + 1, 0) - 1