MIN problem in sub-query

[jposner]

Hello,

I am having problems running the below portion of a query. Any suggestions are greatly appreciated. Ultimately, I am trying to pull the minimum date from a benefit table, where the minimum date is in 2003. It is important to have both parameters because I need to know whether an employee entered into the plan for the first time in 2003 or not.

(select min(eb1.endatebeg) from ebenefit as eb1 where eb1.enflxideb = ebase.ebflxid and min(eb1.endatebeg) >= '01/01/2003')

Thanks!

Joe

 

[jposner]

oops. sorry, here is the whole piece:

...and(ebenefit.endatebeg = (select min(eb1.endatebeg) from ebenefit as eb1 where eb1.enflxideb = ebase.ebflxid and min(eb1.endatebeg) >= '01/01/2003'))

I have the min in both the select and the where which I dont think can happen, but amn not sure of the syntax to retrieve this information otherwise.

Thanks,

Joe

 

[ESquared]

(select min(eb1.endatebeg) from ebenefit as eb1 where eb1.enflxideb = ebase.ebflxid and min(eb1.endatebeg) >= '01/01/2003')


and(ebenefit.endatebeg = (select min(eb1.endatebeg) from ebenefit as eb1 where eb1.enflxideb = ebase.ebflxid and min(eb1.endatebeg) >= '01/01/2003'))

why not use GROUP BY and HAVING?

SELECT
   enflxideb,
   Min(eb1.endatebeg)
FROM ebenefit as eb1
GROUP BY enflxideb eb1
HAVING Min(eb1.endatebeg) >= '1/1/2003'

To also select records you don't want to group by from this or other tables, put this in as a derived table and join your other table to DerivedTable.enflxideb.

 

[jposner]

when I try:

and (ebenefit.endatebeg =(SELECT Min(eb1.endatebeg) FROM ebenefit as eb1 GROUP BY endatebeg HAVING Min(eb1.endatebeg) >= '1/1/2003'))

I am told that the subquery is returning more than 1 value which is not allowed. If it's selecting the minimum date where the minimum date is greater than Jan. 1, 2003, it should only be returning 1 value. Do I still have the syntax wrong?

Thanks again

 

[ESquared]

FYI I didn't mean to include the stuff above my question, I pasted it in for just reference when responding.

I don't know enough about your table and data structures to help you here... but for what it's worth I recommend a join, not a subquery.

As for what you have above, you are not selecting a correct record for each row in the outer query, you are selecting the *same* record each time.

You need to give more information, a lot more.

 

[SQLBill]

Here's the problem with your original query:

where <more code> and min(eb1.endatebeg) >= '01/01/2003'))

at this point SQL Server doesn't know what min(eb1.endatebeg) is. You would need to make it:

where .... and (select min(eb1.endatebeg) from ebenefit) >= '01/01/2003'))

but even that is making it harder than needed, try this:

where eb1.enflxideb = ebase.ebflxid and endatebeg >= '01/01/2003')

You want the minimum date value of endatebeg where eb1.enflxideb = ebase.ebflxid and endatebeg is greater than or equal to 01/01/2003.

-SQLBill

 

[ESquared]

I was assuming there were many records and he needed an aggregate query in order to figure out which one, by primary key, had a minimum date. But if there's only one possible then using a WHERE condition is the way to go.

 

[jposner]

here is the entire query. I didn't want to paste all this if the issue was just with the subquery.

SELECT
EBase."EbSocNumber", EBase."EbFirstName", EBase."EbLastName",
EBenefit."EnDateBeg", EBenefit."EnDateEnd", EBenefit."EnEECostPerPay", EBenefit."EnERCostPerPay", BenBase."BbDescrip"
FROM
((((EBASE INNER JOIN EEMPLOY ON EBASE.EBFLXID = EEMPLOY.EEFLXIDEB) INNER JOIN EJOB ON EBASE.EBFLXID = EJOB.EJFLXIDEB) INNER JOIN EBENEFIT ON EBASE.EBFLXID = EBENEFIT.ENFLXIDEB) INNER JOIN BENBASE ON EBENEFIT.ENFLXIDBB = BENBASE.BBFLXID) LEFT JOIN ((EFUNDELECTION INNER JOIN FUNDITEM ON EFUNDELECTION.FEFLXIDFI = FUNDITEM.FIFLXID) INNER JOIN CARDFILE ON FUNDITEM.FIFLXIDCF = CARDFILE.CFFLXID) ON ENFLXID = FEFLXIDEN
WHERE
EBASE.EBFLAGEMP = 'Y' AND (EEMPLOY.EEDATEBEG <= '12/31/2003' AND (EEMPLOY.EEDATEEND >= '01/01/2003' OR EEMPLOY.EEDATEEND IS NULL)) AND (EJOB.EJDATEBEG <= '12/31/2003' AND (EJOB.EJDATEEND >= '01/01/2003' OR EJOB.EJDATEEND IS NULL)) AND (EBENEFIT.ENDATEBEG <= '12/31/2003' AND (EBENEFIT.ENDATEEND >= '01/01/2003' OR EBENEFIT.ENDATEEND IS NULL)) AND BENBASE.BBDESCRIP = '403B' and (ebenefit.endatebeg =(SELECT Min(eb1.endatebeg) FROM ebenefit as eb1 HAVING Min(eb1.endatebeg) >= '1/1/2003'))

 

[jposner]

Thanks SQLBill. Actually that suggestion would only return the minimum value that is greater than Jan. 1, 2003. I needed to return the minimum value for someone whose minimum value is at least Jan. 1, 2003. So if they had a record in 2002 and 2003, I don't want that on the report at all.

Thanks anyway. I was able to get it to work with the following. It was simple I was just ahving difficulty getting the syntax right. (I am not a programmer).

SELECT EBase."EbSocNumber",
EBase."EbFirstName",
EBase."EbLastName",
min(EBenefit."EnDateBeg"),
EBenefit."EnDateEnd",
EBenefit."EnEECostPerPay",
EBenefit."EnERCostPerPay",
BenBase."BbDescrip"
FROM

((((EBASE INNER JOIN EEMPLOY ON EBASE.EBFLXID = EEMPLOY.EEFLXIDEB)INNER JOIN EJOB ON EBASE.EBFLXID = EJOB.EJFLXIDEB) INNER JOIN EBENEFIT ON EBASE.EBFLXID = EBENEFIT.ENFLXIDEB)INNER JOIN BENBASE ON EBENEFIT.ENFLXIDBB = BENBASE.BBFLXID) LEFT JOIN((EFUNDELECTION INNER JOIN FUNDITEM ON EFUNDELECTION.FEFLXIDFI = FUNDITEM.FIFLXID)
INNER JOIN CARDFILE ON FUNDITEM.FIFLXIDCF = CARDFILE.CFFLXID)ON ENFLXID = FEFLXIDEN
WHERE
EBASE.EBFLAGEMP = 'Y' AND (EEMPLOY.EEDATEBEG <= '12/31/2003' AND (EEMPLOY.EEDATEEND >= '01/01/2003' OR EEMPLOY.EEDATEEND IS NULL)) AND (EJOB.EJDATEBEG <= '12/31/2003' AND (EJOB.EJDATEEND >= '01/01/2003' OR EJOB.EJDATEEND IS NULL)) AND (EBENEFIT.ENDATEBEG <= '12/31/2003' AND(EBENEFIT.ENDATEEND >= '01/01/2003' OR EBENEFIT.ENDATEEND IS NULL)) AND BENBASE.BBDESCRIP = '403B'
GROUP BY EBase."EbSocNumber",
EBase."EbFirstName",
EBase."EbLastName",
EBenefit."EnDateEnd",
EBenefit."EnEECostPerPay",
EBenefit."EnERCostPerPay",
BenBase."BbDescrip"
HAVING
min(ebenefit.endatebeg) >= '01/01/2003')

Joe

 

[ESquared]

In trying to see if there was any way I could help you further, I reformatted your query as below. It's identical to yours, but to me, it's easier to read and understand. I hope this is helpful.

SELECT
	EBase.EbSocNumber, 
	EBase.EbFirstName, 
	EBase.EbLastName,
	min(EBenefit.EnDateBeg), 
	EBenefit.EnDateEnd, 
	EBenefit.EnEECostPerPay, 
	EBenefit.EnERCostPerPay,  
	BEnBase.BbDescrip
FROM
	EBase
	INNER JOIN EEmploy ON EBase.EbflxID = EEmploy.EeflxIDEb
	INNER JOIN EJob ON EBase.EbflxID = EJob.EjflxIDEb
	INNER JOIN EBenefit ON EBase.EbflxID = EBenefit.EnflxIDEb
	INNER JOIN BEnBase ON EBenefit.EnflxIDBb = BEnBase.BbflxID
	LEFT JOIN (
		EFundElection
		INNER JOIN FundItem ON EFundElection.FeflxIDfi = FundItem.FiflxID
		INNER JOIN CardFile ON FundItem.FiflxIDcf = CardFile.CfflxID
	) ON EBase.EnflxID = EFundElection.FeflxIDen
WHERE
	EBase.EbFlagEmp = 'Y'
	AND (
		EEmploy.EeDateBeg <= '12/31/2003'
		AND (EEmploy.EEDateEnd >= '01/01/2003' OR EEmploy.EEDateEnd IS NULL)
	) AND (
		EJob.EJDateBeg <= '12/31/2003'
		AND (EJob.EJDateEnd >= '01/01/2003' OR EJob.EJDateEnd IS NULL)
	) AND (
		EBenefit.EnDateBeg <= '12/31/2003' 
		AND(EBenefit.EnDateEnd >= '01/01/2003' OR EBenefit.EnDateEnd IS NULL)
	) AND BEnBase.BbDescrip = '403B'
GROUP BY
	EBase.EbSocNumber, 
   EBase.EbFirstName, 
   EBase.EbLastName,
   EBenefit.EnDateEnd, 
   EBenefit.EnEECostPerPay, 
   EBenefit.EnERCostPerPay,  
   BEnBase.BbDescrip
HAVING
	Min(EBenefit.EnDateBeg) >= '01/01/2003')

For what it's worth, if your dates have times attached to them, then you need to make the date clauses be less than the next day, not less than or equal to the final day or you will miss a day's worth of records, i.e.,

EEmploy.EeDateBeg < '1/1/2004'

--not
EEmploy.EeDateBeg <= '12/31/2003'