|
|
Posted by Magenta on 11/11/06 20:21
Here's a view for invoicing that I had that finds the gaps (i.e.,
places where there is more than one day between the end of one invoice
and the start of the next (by activityid)
Backwards (from your perspective) but it may help you
/*****************************************************************************************
***** Version 20060328_1155
**
** This view will list the invoice id and activity id for all invoices
that have no
** existing previous invoice, e.g., there is no invoice for the
activity that has a
** period ending corresponding to a listed invoices's period
beginning, e.g., the
** period beginning date for these invoices creates a gap in the
invoicing periods
** for the task (activity_id)
**
*****************************************************************************************/
ALTER view dbo.viewInvoice_MissingPreviousInvoice
as
select t1.inv_id, t1.inv_activity_id
from tblinvoice_headers as t1
inner join (select inv_activity_id, min(inv_period_beginning) as
inv_period_beginning
from dbo.tblInvoice_Headers
group by inv_activity_id
) as t2 on t2.inv_activity_id = t1.inv_activity_id
where t1.inv_period_beginning <> t2.inv_period_beginning
and not exists (select s1.inv_id
from dbo.tblInvoice_Headers as s1
where s1.inv_activity_id = t1.inv_activity_id
and datediff(day, s1.inv_period_ending,
t1.inv_period_beginning) = 1
)
Navigation:
[Reply to this message]
|