February 25, 2008 at 3:54 am
Hello i've got the following query, but the output is not the desired.
I need to get only values where the c.startdate or c.enddate are NOT NULL but the point is that with this query i don't receive the result as i need can you help me please. :blush:
Query:
------
select c.accountid,c.offername,min(c.startdate),max(c.enddate)
from billingRecord c, packagetype p
where packagetypeid in (0,2) and c.accountid in ('123456789', '321654987')
group by c.accountid,c.offername
order by 1
go
Output:
-------
c.accountid c.offername c.startdate c.enddate
---------------------------------------------------------------
123456789 TV123 20-02-2008 11:23:25 NULL
123456789 TV123 NULL 21-02-2008 12:30:00
321654987 TV1 18-02-2008 16:58:25 NULL
321654987 TV1 NULL 22-02-2008 18:37:15
Output desired:
---------------
c.accountid c.offername c.startdate c.enddate
---------------------------------------------------------------
123456789 TV123 20-02-2008 11:23:25 21-02-2008 12:30:00
321654987 TV1 18-02-2008 16:58:25 22-02-2008 18:37:15
Thanks and regards,
JMSM 😉
February 25, 2008 at 4:28 am
You wish to really join two sets of information - the start dates and the finish dates. There are numerous ways to get this information. One could be
select
c.accountid,c.offername,
(select min(cmin.startDate) from billingRecord cmin where cmin.accountid = c.accountid and cmin.offername = c.offername) as StartDate,
(select max(cmin.startDate) from billingRecord cmin where cmin.accountid = c.accountid and cmin.offername = c.offername) as FinishDate
from billingRecord c
inner join packageType p
on p.packageTypeID in (0, 2)
and c.accountID in ('123456789', '321654987')
group by c.accountid, c.offername --you could also scrap this and use distinct after select but the group by should perform better
Note that you can still use an inner join even though the tables aren't directly related 🙂 Your syntax in that area was also fine, although using an explicit CROSS JOIN rather than separating the tables with commas is (imho) a bit nicer.
You could also do something like
select
c.accountid,c.offername, dates.minstartDate, dates.maxStartDate
from (select distinct accountID, offerName from billingRecord) c
inner join packageType p
on p.packageTypeID in (0, 2)
and c.accountID in ('123456789', '321654987')
inner join (
select accountID, offerName, min(startDate) as minStartDate, max(startDate) as maxStartDate
from billingRecord
group by accountID, offerName
) dates
on c.acountID = dates.accountID
and c.offerName = dates.offerName
I'm curious though. If you're not relating packageType and billingRecord, what purpose does the packageTypeID field being in (0, 2) serve? Perhaps you do have a field to link the tables but forgot to include it?
In that case, amend the inner join appropriately and you could (if desired) get rid of the inner join criteria from the above queries and move it to the where clause again.
select c.accountid,c.offername,min(c.startdate),max(c.enddate)
from billingRecord c, packagetype p
where packagetypeid in (0,2) and c.accountid in ('123456789', '321654987')
group by c.accountid,c.offername
order by 1
[/code]
February 25, 2008 at 4:33 am
Are you missing the Join condition?
In your query, all columns in the SELECT clause belong to BillingRecord table.
You are using the package type table but it is not JOINed with BillingRecord Table. So, it displays twice.
select c.accountid,c.offername,min(c.startdate),max(c.enddate)
from billingRecord c, packagetype p
where
-- This could be the missing part
-- c....= p... and
packagetypeid in (0,2) and c.accountid in ('123456789', '321654987')
group by c.accountid,c.offername
order by 1
Cheers,
Prithiviraj Kulasingham
Plan to Test your Plan!
February 25, 2008 at 6:22 am
Thanks everyone.
Regards,
JMSM
May 30, 2011 at 6:31 am
select c.accountid,c.offername,min(c.startdate),max(c.enddate)
from billingRecord c, packagetype p
where packagetypeid in (0,2) and c.accountid in ('123456789', '321654987')
and c.startdate is NOT NULL and c.enddate is NOT NULL
group by c.accountid,c.offername
order by 1
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply