February 22, 2008 at 11:11 am
I've got the following query and i need to get one result, can you help on this point, please?
select c.accountid,c.offername,c.startdate,c.enddate
from billingRecord c
where c.bundlename like '%pack%' and
c.accountid='9ABD'
This query gives me the following result.
ID offer startdate enddate
9ABDPack TV 2007-11-15 03:31:43.860NULL
9ABDPack TV NULL 2007-12-21 02:34:38.887
Null value appears between the initial and end date, because while someone its owner of one product, but i need to get people who buy one pack and then in what date did he cancel it.
I need to get one result like the following, but i've no idea how can i get it :crazy:
ID offer startdate enddate
9ABDPack TV 2007-11-15 03:31:43.8602007-12-21 02:34:38.887
I feel like donkey :blush:but i'm to tired, so hope you can help me
Thanks and regards,
JMSM;)
February 22, 2008 at 11:27 am
select c.accountid,c.offername,min(c.startdate),max(c.enddate)
from billingRecord c
where c.bundlename like '%pack%' and
c.accountid='9ABD'
group by c.accountid,c.offername
That should work, not checked it but i cant see why it wouldnt. You could also query the table twice and return the results on one row...
select c.accountid,c.offername,c.startdate,d.enddate
from billingRecord c
inner join billingRecord d on d.accountid = c.accountid and d.enddate is not null
where c.bundlename like '%pack%' and
c.accountid='9ABD' and c.startdate is not null
February 22, 2008 at 12:59 pm
It looks like you may have some issues with your table design as well. First off, any time you run a LIKE comparison using the wildcard for the first character in a string, you are going to get a table scan.
Secondly, is there a reason why you are not updating the original row with the end date instead of creating a new row?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply