Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help me: QUERY OUTPUT


Help me: QUERY OUTPUT

Author
Message
JMSM
JMSM
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 966
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 Wink
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
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 Smile 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]



Preethiviraj Kulasingham
Preethiviraj Kulasingham
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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!
JMSM
JMSM
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 966
Thanks everyone.

Regards,
JMSM
swamy.gare
swamy.gare
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search