Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help me: QUERY OUTPUT Expand / Collapse
Author
Message
Posted Monday, February 25, 2008 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
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.

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 ;)
Post #459600
Posted Monday, February 25, 2008 4:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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]



Post #459610
Posted Monday, February 25, 2008 4:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, Visits: 86
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!
Post #459612
Posted Monday, February 25, 2008 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
Thanks everyone.

Regards,
JMSM
Post #459637
Posted Monday, May 30, 2011 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 30, 2011 6:30 AM
Points: 1, Visits: 6
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

Post #1116984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse