Help with subqueries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with subqueries

Author

Topic

casigirl
Starting Member

4 Posts

Posted - 2005-05-09 : 09:51:56

I'm a sql idiot and I need your helo! I've got two tables. One contains contracts, the other contains payment records for the contracts. Each contract has several payment records. I'm trying to build a query that gets each contract just once, and selects the first start payment date and end payment date that has data in it, ignoring the rest. I'm just not sure how to do the subquery and to filter by the first non-null record. How can I do this?? This is what I have so far:

Select contract_ID,
(select payment_start_date from payments
left join contracts on payments.contract_ID = contracts.contract_ID)

And I have NO idea where to start! (it's actually quite a bit more complicated than this, but this is the jist of it) Any help would be very much appreciated. Thank you so much!

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-09 : 10:00:06

Hmm, you'd need something like this

SELECT c.contract_id, mindate, maxdate FROM contracts c
LEFT JOIN (SELECT contract_id, min(payment_start_date) as mindate FROM payments GROUP BY contract_id) min ON c.contract_id = min.contract_id
LEFT JOIN (SELECT contract_id, max(payment_start_date) as maxdate FROM payments GROUP BY contract_id) max ON c.contract_id = max.contract_id

But bear in mind that this may produce slightly unexpected results for things with just one, or no, payment dates.

-------
Moo. :)

casigirl
Starting Member

4 Posts

Posted - 2005-05-09 : 10:03:58

quote:
Originally posted by mr_mist

Hmm, you'd need something like this

SELECT c.contract_id, mindate, maxdate FROM contracts c
LEFT JOIN (SELECT contract_id, min(payment_start_date) as mindate FROM payments GROUP BY contract_id) min ON c.contract_id = min.contract_id
LEFT JOIN (SELECT contract_id, max(payment_start_date) as maxdate FROM payments GROUP BY contract_id) max ON c.contract_id = max.contract_id

But bear in mind that this may produce slightly unexpected results for things with just one, or no, payment dates.

-------
Moo. :)

I will try that, thanks for the incredibly speedy reply!

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 10:31:15

quote:
Originally posted by casigirl

I'm a sql idiot and I need your helo!

Hello!

Post the DDL of your Tables, what the sample data looks like, and what the expected results are suppose to be.

Do that, you'll have an answer in minutes.

Brett

8-)

casigirl
Starting Member

4 Posts

Posted - 2005-05-09 : 10:43:00

quote:
Originally posted by X002548

quote:
Originally posted by casigirl

I'm a sql idiot and I need your helo!

Hello!

Post the DDL of your Tables, what the sample data looks like, and what the expected results are suppose to be.

Do that, you'll have an answer in minutes.

Brett

8-)

I'm not sure what 'DDL' means <embarrassed>

But let me paste my entire query thus far:

SELECT CC.CPAC_Contract_ID,
CC.Status_Type_ID,
T.Type_Name,
CC.Effective_Date,
CC.Signature_Date,
case
when CC.CPAC_Contract_ID > 700000 then 'CPAC'
when CC.CPAC_Contract_ID > 600000 then 'Scopus'
else 'Jackal'
end as System_Name,
case
when CC.Effective_Date < '2004-08-15' then 'old'
else 'new'
end as Eff_Date_Flag,
case
when CC.Signature_Date < '2004-08-15' then 'old'
when CC.Signature_Date is NULL then 'old'
else 'new'
end as Sig_Date_Flag,
[Future Revenue] = CASE
WHEN EXISTS(
SELECT A.CPAC_Payment_Detail_ID
FROM CPAC_Payment_Detail A
LEFT JOIN CPAC_Product_Entitlement B ON A.CPAC_Payment_ID = B.CPAC_Payment_ID
LEFT JOIN CPAC_Entitlement_Header C ON B.CPAC_Entitlement_Header_ID = C.CPAC_Entitlement_Header_ID
WHERE CC.CPAC_Contract_ID = C.CPAC_Contract_ID
AND A.SMS_Start_Date > GETDATE()
AND A.Status_Type_ID <> 174)
THEN 'Yes'
ELSE 'No'
END,
FIRST CPD.SMS_Start_Date, CPD.SMS_End_Date
(Select CPD.SMS_Start_Date, CPD.SMS_End_Date
from CPAC.dbo.CPAC_Payment_Detail CPD
left join CPAC.dbo.CPAC_Payment CP on CPD.CPAC_Payment_ID = CP.CPAC_Payment_ID
left join CPAC.dbo.CPAC_Product_Entitlement CPE on CP.CPAC_Payment_ID = CPE.CPAC_Payment_ID
left join CPAC.dbo.CPAC_Entitlement_Header CEH on CPE.CPAC_Contract_ID=CEH.CPAC_Contract_ID
Where CC.CPAC_Contract_ID = CEH.CPAC_Contract_ID)
FROM {oj CPAC.dbo.CPAC_Contract CC
LEFT JOIN CPAC.dbo.Type T ON CC.Status_Type_ID = T.Type_ID}

What I want is one line for each CC.CPAC_Contract_ID; I want it to select the first 'SMS_Start_Date' and 'SMS_End_Date' that contains data. If I just select the 'SMS_Start_Date' 'SMS_End_Date' without a subquery, I get eleventy billion lines per each CPAC_Contract_ID. Does that help? Thanks again!!

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-09 : 10:49:44

You will need to join somehow to the subquery similar to that which I posted above. DDL means the create table statements for the tables involved in your query, as produced by if you right click on the table in EM and choose Script Object. (Or something similar i can't remember exactly).

-------
Moo. :)

casigirl
Starting Member

4 Posts

Posted - 2005-05-09 : 11:14:56

quote:
Originally posted by mr_mist

You will need to join somehow to the subquery similar to that which I posted above. DDL means the create table statements for the tables involved in your query, as produced by if you right click on the table in EM and choose Script Object. (Or something similar i can't remember exactly).

-------
Moo. :)

Thank you mr_mist! I will use your previous reply as a guide and try that out!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-09 : 16:01:43

The SQL language is broken into three divisions:

DDL (Data Definition Language) - These are the CREATE, ALTER, DROP, etc. statements that delineate the database schema.

DML (Data Manipulation Language) - These are the SELECT, INSERT, UPDATE, DELETE and other statements that actually impact the data.

DCL (Data Control Language) - These are the security related statements such as GRANT, REVOKE, DENY, etc.

HTH

=================================================================
Some mistakes are too much fun to only make once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources