Do I need view(s) to accomplish this and how?

[PANZON]

Can the following be done without a view(s) or do I need a view?

We have a subscriptions table. We want to report on subscriptions that haven't expired yet but will expire in the date range the user specifies. Of those that will expire, we want to report on those subscriptions that have been renewed and those that have not been renewed. To get this out of our database, I need to join a table to itself and compare the subscription start dates to the subscription expiration dates. Following are the different SQL queries I am using to get Renewed, Not Renewed, and General.

--NOT RENEWED
SELECT s1.startdate,add_months(s1.startdate,s1.term), s1.subkey,s1.arckey,s1.subno,s1.status,s1.subtype,s1.subqty
FROM sbsubs s1,invhead inv, arcust arc
WHERE s1.arckey = arc.arckey and
s1.invkey = inv.invkey and
arc.slsvol is null and
s1.status in('A','F') and
inv.item = '2455'and
add_months(s1.startdate,s1.term) >= to_date('05/01/2000', 'mm/dd/yyyy') and
add_months(s1.startdate,s1.term) <= to_date('11/02/2000', 'mm/dd/yyyy') and
NOT EXISTS (select * from sbsubs s2 where s2.arckey=s1.arckey and
s2.subno=s1.subno and
s2.startdate = add_months(s1.startdate,s1.term) and
s2.startdate<>s1.startdate)
ORDER BY s1.ARCKEY

-- RENEWED
SELECT s1.startdate,add_months(s1.startdate,s1.term), s1.subkey,s1.arckey,s1.subno,s1.status,s1.subtype,s1.subqty
FROM sbsubs s1,invhead inv, arcust arc
WHERE s1.arckey = arc.arckey and
s1.invkey = inv.invkey and
arc.slsvol is null and
s1.status in('A','F') and
inv.item = '2455'and
add_months(s1.startdate,s1.term) >= to_date('05/01/2000', 'mm/dd/yyyy') and
add_months(s1.startdate,s1.term) <= to_date('11/02/2000', 'mm/dd/yyyy') and
EXISTS (select * from sbsubs s2 where s2.arckey=s1.arckey and
s2.subno=s1.subno and
s2.startdate = add_months(s1.startdate,s1.term) and
s2.startdate<>s1.startdate)
ORDER BY s1.ARCKEY

-- GENERAL
SELECT s1.startdate,add_months(s1.startdate,s1.term), s1.subkey,s1.arckey,s1.subno,s1.status,s1.subtype,s1.subqty
FROM sbsubs s1,invhead inv, arcust arc
WHERE s1.arckey = arc.arckey and
s1.invkey = inv.invkey and
arc.slsvol is null and
s1.status in('A','F') and
inv.item = '2455'and
add_months(s1.startdate,s1.term) >= to_date('05/01/2000', 'mm/dd/yyyy') and
add_months(s1.startdate,s1.term) <= to_date('11/02/2000', 'mm/dd/yyyy')
ORDER BY s1.ARCKEY

Brutal eh. Thanks for any thoughts on the subject.

 

[MalcolmW]

Why not just make one query that has both the renewed and not renewed, and report from that query? The syntax below might need some work (if you are using Oracle, which doesn't support the use of derived column names in the standard SQL 92 manner). You could either use this query to create and populate a temporary table, view, stored procedure, or Crystal SQL Query. Which you want to do depends on what is easiest for you/the RDBMS you are using.
eg.

SELECT s1.startdate,add_months(s1.startdate,s1.term), s1.subkey,s1.arckey,s1.subno,s1.status,s1.subtype,s1.subqty,&quot;Not Renewed&quot; AS RenewalStatus
FROM sbsubs s1,invhead inv, arcust arc
WHERE s1.arckey = arc.arckey and
s1.invkey = inv.invkey and
arc.slsvol is null and
s1.status in('A','F') and
inv.item = '2455'and
add_months(s1.startdate,s1.term) >= to_date('05/01/2000', 'mm/dd/yyyy') and
add_months(s1.startdate,s1.term) <= to_date('11/02/2000', 'mm/dd/yyyy') and
NOT EXISTS (select * from sbsubs s2 where s2.arckey=s1.arckey and
s2.subno=s1.subno and
s2.startdate = add_months(s1.startdate,s1.term) and
s2.startdate<>s1.startdate)
UNION ALL
SELECT s1.startdate,add_months(s1.startdate,s1.term), s1.subkey,s1.arckey,s1.subno,s1.status,s1.subtype,s1.subqty,&quot;Renewed&quot; AS RenewalStatus
FROM sbsubs s1,invhead inv, arcust arc
WHERE s1.arckey = arc.arckey and
s1.invkey = inv.invkey and
arc.slsvol is null and
s1.status in('A','F') and
inv.item = '2455'and
add_months(s1.startdate,s1.term) >= to_date('05/01/2000', 'mm/dd/yyyy') and
add_months(s1.startdate,s1.term) <= to_date('11/02/2000', 'mm/dd/yyyy') and
EXISTS (select * from sbsubs s2 where s2.arckey=s1.arckey and
s2.subno=s1.subno and
s2.startdate = add_months(s1.startdate,s1.term) and
s2.startdate<>s1.startdate)
ORDER BY 9, 4

 

[deborahb]

If you use an ADO recordset for the source of your report, (Data Explorer -> More Data Sources -> Active Data -> Active Data (ADO)) you can write any sql you like including a union query. You can then group on &quot;renewed&quot;, &quot;not renewed&quot;, therefore not requiring a view. Alternativelively, you can do the same by having a subreport that looks just like your main report, one selecting on &quot;renewed&quot; and one on &quot;not renewed&quot;. Your job then is to line them up so that it looks like one query. Using Shared Variables you can even pass information between the reports. Your third choice is to base your report on a query built with the Query Builder and then again as with the ADO recordset you can build a query with unions in it.

In option 1 (ADO) you won't be able to pass any parameters to the query itself so unless you are going to pass it an ADO recordset from a VB application or something like that the other two solutions are better for parameter passing.

Good Luck,

Deborah