Help me: QUERY OUTPUT

  • 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 😉

  • 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]

  • 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!

  • Thanks everyone.

    Regards,

    JMSM

  • 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