Problem with searching MAX Date in HAVING Clause

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)

Problem with searching MAX Date in HAVING Clause

Author

Topic

5fifty5
Starting Member

35 Posts

Posted - 2009-12-15 : 06:24:39

Hi,
I will explain the scenario before getting to the promlem.

This process runs every night and picks up certain records (for instance sales from Alaska. Its just an example). Now those certain records have to be inserted into a NewTable which will keep them for future reference. This query also keeps a record of the date when the record was modified and that also gets inserted into the NewTable.
When the query runs the next time, it looks for records who have a Last Modified GREATER than what was Inserted in the NewTable. If the LastModified date is greater for the record, which was inserted in NewTable the previous night, this record with the new modified date is inserted in the same table. I am using HAVING clause and looking for MAXIMUM LastModifiedDate for those records.

The problem I am facing is that when the query runs again, i.e. the third time, it brings the SAME record, even when the LastModified date was not greater than the Last night.

I will try & explain with records:

First night run:

ID Name LastModifiedDate
===========================================
1 Alaska 2009-12-15 10:05:25.000
2 Seattle 2009-12-15 10:10:00.000
3 NY 2009-12-15 10:30:25.000

Record 3 was Modified and the next night, query will only bring the 3 records as output and insert it into NewTable

Output

ID Name LastModifiedDate
===========================================
4 NewYork 2009-12-15 10:45:01.000

NewTable

ID Name LastModifiedDate
===========================================
1 Alaska 2009-12-15 10:05:25.000
2 Seattle 2009-12-15 10:10:00.000
3 NY 2009-12-15 10:30:25.000
4 NewYork 2009-12-15 10:45:01.000

However, When the query runs the next day, even if there was no change in the record, it still brings it up with the same date, so the NewTable will have the following records:

NewTable On third night
ID Name LastModifiedDate
===========================================
1 Alaska 2009-12-15 10:05:25.000
2 Seattle 2009-12-15 10:10:00.000
3 NY 2009-12-15 10:30:25.000
4 NewYork 2009-12-15 10:45:01.000
5 NewYork 2009-12-15 10:45:01.000

As you can see in LastModified for ID 4 and 5, they are the same, which I dont want. In my case, the output should be as follows:

NewTable On third night
ID Name LastModifiedDate
===========================================
1 Alaska 2009-12-15 10:05:25.000
2 Seattle 2009-12-15 10:10:00.000
3 NY 2009-12-15 10:30:25.000
4 NewYork 2009-12-15 10:45:01.000

i.e. No record insertion since the LastModified was NOT greater on third night.

I hope someone can help.

Cheers.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-15 : 06:46:40

How about posting your query as i'm sure you have an issue with the query, not the data?

5fifty5
Starting Member

35 Posts

Posted - 2009-12-15 : 10:34:18

Here is the code:



--USE 


DECLARE	@AcademicYearID varchar(5)


SET @AcademicYearID =
(SELECT	AcademicYearID
FROM	AcademicYear WITH (NOLOCK)
WHERE
	DATEADD(Year, 0, GETDATE()) BETWEEN StartDate AND EndDate
)


--Start of Query

IF (SELECT MAX([Detail Number]) FROM Extra.[dbo].[QLCashBookExport]) IS NULL	--Check Table for NULL


BEGIN

SELECT	IDENTITY(int, 1, 1) AS [Detail Number],
	
	S.StudentDetailID,	

	fc.FeeContributionID,
	
	LEFT(S.RefNo, 6) AS RefNo,

	'TEFC' AS [Journal Code],

	'SL1' AS [Ledger Code],

	--REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) [Transaction Date],

	--CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3) [Transaction Date],
	
	CAST(CURRENT_TIMESTAMP AS SMALLDATETIME) [Transaction Date],

	' ' AS [Reference1],

	pm.[Description] AS [Reference2],

	S.FirstForeName + ' ' + S.Surname AS [Description],
	
	CASE when pm.PaymentMethodID IN (1, 2)
		THEN
		REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) + ISNULL(fc.TillCode, ' No Till')
		ELSE 
			REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0)) + 'CC'
		END	
	AS [Paying In Reference],
	
	CASE WHEN e.FeeSourceID = 01
		THEN CAST ((CASE when s.EmployerID IS NULL THEN fc.OrganisationID else s.EmployerID END) as varchar(4)) + 'T'
		ELSE LEFT(s.RefNo, 6)

	END

	AS [Account Code],

	S.FirstForeName + ' ' + S.Surname AS [Payee Name],
	
	CASE WHEN fc.ReceiptCode like 'Receipt%' 
		THEN RIGHT(fc.ReceiptCode, 5)
		WHEN fc.ReceiptCode like 'CHQ%'
		THEN RIGHT(fc.ReceiptCode, 6)
		WHEN fc.ReceiptCode IS NULL
		THEN ''
		ELSE fc.ReceiptCode

	END
	AS [Receipt Number],
	
	fc.Amount AS [Header Net Value],

	' ' AS [Header VAT Code],

	'0' AS [Header VAT Value],
	
	fc.Amount AS [Header Gross Value],

	'0' AS [Header Discount Value],

	' ' AS [Project Chart Code],

	' ' AS [Project Account Code],

	O.Code,

	fc.Amount AS [Line Net Value],

	' ' AS [Line VAT Code],

	'0' AS [Line VAT Value],

	fc.Amount AS [Line Gross Value],

	'0' AS [Line Discount],
	
	ISNULL ( MAX ( fc.[LastModifiedDate] ), CAST (GETDATE() AS DATETIME)) AS LastModifiedDate


INTO	#QLCashBookKNew


FROM	FeeContribution AS fc WITH (NOLOCK)

	INNER JOIN Enrolment AS e WITH (NOLOCK)
		ON e.EnrolmentID = fc.EnrolmentID

	INNER JOIN Offering AS o 
		ON e.OfferingID = o.OfferingID
	
	INNER JOIN StudentDetail AS s WITH (NOLOCK)
		ON	e.StudentDetailID = s.StudentDetailID
		and	fc.StudentDetailID = s.StudentDetailID
	
	LEFT join PaymentMethod as pm
		on pm.PaymentMethodID = fc.PaymentMethodID
		--and e.FeeSourceID = pm.PaymentMethodID

	LEFT join Organisation as org WITH (NOLOCK)
		on org.OrganisationID = s.SchoolID


WHERE	s.AcademicYearID = '09/10'

AND	OfferingTypeID <> 3 	-- Exclude units

AND 	fc.PaymentMethodID IN (1, 2, 3, 6)	--Cash, Cheque, Direct Debit, Credit Card

AND	fc.FeeContributionTypeID = 2		--Payment

and	fc.DatePaid IS NOT NULL


GROUP BY	S.StudentDetailID,
		fc.FeeContributionID,
		s.RefNo,
		pm.Description,
		s.FirstForename,
		s.Surname,
		pm.PaymentMethodID,
		fc.OrganisationID,
		s.EmployerID,
		e.FeeSourceID,
		fc.ReceiptCode,
		fc.Amount,
		fc.TillCode,
		o.Code


ORDER BY ISNULL ( MAX ( fc.[LastModifiedDate] ), CAST ( GETDATE() AS DATETIME))


/*
SELECT * FROM #QLCashBookKNew
DROP TABLE #QLCashBookKNew
*/




--*******************--

--INSERT into a Table for Checks

--*******************--

--SET IDENTITY_INSERT Extra.dbo.QLCashBookExport ON 



INSERT INTO Extra.dbo.QLCashBookExport
(
	[StudentDetailID],
	[FeeContributionID],
	[RefNo], 
	[Journal Code], 
	[Ledger Code], 
	[Transaction Date], 
	[Reference1], 
	[Reference2], 
	[Description], 
	[Paying In Reference], 
	[Account Code], 
	[Payee Name], 
	[Receipt Number], 
	[Header Net Value], 
	[Header VAT Code], 
	[Header VAT Value], 
	[Header Gross Value], 
	[Header Discount Value], 
	[Project Chart Code], 
	[Project Account Code], 
	[Code], 
	[GLCODE], 
	[Line Net Value], 
	[Line VAT Code], 
	[Line VAT Value], 
	[Line Gross Value], 
	[Line Discount], 
	[LastModifiedDate]
)
SELECT
	[StudentDetailID],
	[FeeContributionID],
	[RefNo], 
	[Journal Code], 
	[Ledger Code], 
	[Transaction Date], 
	[Reference1], 
	[Reference2], 
	[Description], 
	[Paying In Reference], 
	[Account Code], 
	[Payee Name], 
	[Receipt Number], 
	[Header Net Value], 
	[Header VAT Code], 
	[Header VAT Value], 
	[Header Gross Value], 
	[Header Discount Value], 
	[Project Chart Code], 
	[Project Account Code], 
	[Code], 
	[GLCODE], 
	[Line Net Value], 
	[Line VAT Code], 
	[Line VAT Value], 
	[Line Gross Value], 
	[Line Discount], 
	[LastModifiedDate]

FROM	#QLCashBookKNew

--SET IDENTITY_INSERT Extra.dbo.QLCashBookExport OFF

SELECT *

FROM	#QLCashBookKNew

DROP TABLE #QLCashBookKNew



--*******************--

--Insert Check Ends

--*******************--


/*SELECT * 
FROM Extra.dbo.QLCashBookExport
ORDER BY [Detail NUmber]
*/



END



/*
--============================================================================
IF there are records in the QLCASHBOOKExport Table, then check if there are any 
changes to them in the following query.
--============================================================================
*/



ELSE IF (SELECT MAX ( [Detail Number]) FROM Extra.[dbo].[QLCashBookExport]) IS NOT NULL	--Check Table for NULL


BEGIN



SELECT	IDENTITY(int, 1, 1) AS [Detail Number],
	
	S.StudentDetailID,

	fc.FeeContributionID,
	
	LEFT(S.RefNo, 6) AS RefNo,

	'TEFC' AS [Journal Code],

	'SL1' AS [Ledger Code],

	--REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) [Transaction Date],

	--CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3) [Transaction Date],
	
	CAST(CURRENT_TIMESTAMP AS SMALLDATETIME) [Transaction Date],

	' ' AS [Reference1],

	pm.[Description] AS [Reference2],

	S.FirstForeName + ' ' + S.Surname AS [Description],
	
	CASE when pm.PaymentMethodID IN (1, 2)
		THEN
		REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) + ISNULL(fc.TillCode, ' No Till')
		ELSE 
			REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0)) + 'CC'
		END	
	AS [Paying In Reference],
	
	CASE WHEN e.FeeSourceID = 01
		THEN CAST ((CASE when s.EmployerID IS NULL THEN fc.OrganisationID else s.EmployerID END) as varchar(4)) + 'T'
		ELSE LEFT(s.RefNo, 6)

	END

	AS [Account Code],

	S.FirstForeName + ' ' + S.Surname AS [Payee Name],
	
	CASE WHEN fc.ReceiptCode like 'Receipt%' 
		THEN RIGHT(fc.ReceiptCode, 5)
		WHEN fc.ReceiptCode like 'CHQ%'
		THEN RIGHT(fc.ReceiptCode, 6)
		WHEN fc.ReceiptCode IS NULL
		THEN ''
		ELSE fc.ReceiptCode

	END
	AS [Receipt Number],
	
	fc.Amount AS [Header Net Value],

	' ' AS [Header VAT Code],

	'0' AS [Header VAT Value],
	
	fc.Amount AS [Header Gross Value],

	'0' AS [Header Discount Value],

	' ' AS [Project Chart Code],

	' ' AS [Project Account Code],

	O.Code,

	fc.Amount AS [Line Net Value],

	' ' AS [Line VAT Code],

	'0' AS [Line VAT Value],

	fc.Amount AS [Line Gross Value],

	'0' AS [Line Discount],
	
	ISNULL ( MAX ( fc.[LastModifiedDate] ), CAST (GETDATE() AS DATETIME)) AS LastModifiedDate


INTO	#QLCashBookUpd


FROM	FeeContribution AS fc WITH (NOLOCK)

	INNER JOIN Enrolment AS e WITH (NOLOCK)
		ON e.EnrolmentID = fc.EnrolmentID

	INNER JOIN Offering AS o 
		ON e.OfferingID = o.OfferingID
	
	INNER JOIN StudentDetail AS s WITH (NOLOCK)
		ON	e.StudentDetailID = s.StudentDetailID
		and	fc.StudentDetailID = s.StudentDetailID
	
	LEFT join PaymentMethod as pm
		on pm.PaymentMethodID = fc.PaymentMethodID
		--and e.FeeSourceID = pm.PaymentMethodID

	LEFT join Organisation as org WITH (NOLOCK)
		on org.OrganisationID = s.SchoolID


	RIGHT JOIN Extra.dbo.QLCashBookExport as qlc
		on	qlc.FeeContributionID = fc.FeeContributionID
		and	fc.LastModifiedDate > qlc.LastModifiedDate
		and	qlc.RefNo	= left(S.RefNo, 6)
		and	qlc.StudentDetailID = S.StudentDetailID
		and	qlc.StudentDetailID = E.StudentDetailID


WHERE	s.AcademicYearID = '09/10'

AND	OfferingTypeID <> 3			-- Exclude units

AND 	fc.PaymentMethodID IN (1, 2, 3, 6)	--Cash, Cheque, Direct Debit, Credit Card

AND	fc.FeeContributionTypeID = 2		--Payment

and	fc.DatePaid IS NOT NULL

--ADDITIONAL CHECKS, Not tested yet.

/*
and	(
	fc.ReceiptCode NOT LIKE qlc.[Receipt Number] 

	OR	LEN(fc.ReceiptCode) <> LEN(qlc.[Receipt Number])

	OR fc.[PaymentMethodID] NOT LIKE (SELECT PaymentMethodID from PaymentMethod where [Description] like qlc.[Reference2])

	OR LEN(fc.[PaymentMethodID]) <> LEN((SELECT PaymentMethodID from PaymentMethod where [Description] like qlc.[Reference2]))

	OR (CASE when pm.PaymentMethodID IN (1, 2)	THEN	REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) + ISNULL(fc.TillCode, ' No Till')
		ELSE REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0)) + 'CC'
		END)
		NOT LIKE
		'%' + qlc.[Paying In Reference] + '%'
		

	OR LEN(
		(CASE when pm.PaymentMethodID IN (1, 2)	THEN	REPLACE(CONVERT(CHAR(8), getdate(), 3), '/', SPACE(0)) + ISNULL(fc.TillCode, ' No Till')
		ELSE	REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0)) + 'CC'
		END)
		)
		<>
		LEN(qlc.[Paying In Reference])


	OR (CASE WHEN fc.ReceiptCode like 'Receipt%' 
		THEN RIGHT(fc.ReceiptCode, 5)
		WHEN fc.ReceiptCode like 'CHQ%'
		THEN RIGHT(fc.ReceiptCode, 6)
		WHEN fc.ReceiptCode IS NULL
		THEN ''
		ELSE fc.ReceiptCode
		END)
	NOT LIKE
		'%'+ qlc.[Receipt Number] + '%'


	OR	LEN(
		(CASE WHEN fc.ReceiptCode like 'Receipt%' 
		THEN RIGHT(fc.ReceiptCode, 5)
		WHEN fc.ReceiptCode like 'CHQ%'
		THEN RIGHT(fc.ReceiptCode, 6)
		WHEN fc.ReceiptCode IS NULL
		THEN ''
		ELSE fc.ReceiptCode
		END)
		)
	<>
		len(qlc.[Receipt Number])
		

	OR	fc.Amount <> qlc.[Header Net Value]

	OR	fc.Amount <> qlc.[Header Gross Value]

	OR	fc.Amount <> qlc.[Line Net Value]

	OR	fc.Amount <> qlc.[Line Gross Value]


	)*/



GROUP BY	S.StudentDetailID,
		fc.FeeContributionID,
		s.RefNo,
		pm.Description,
		s.FirstForename,
		s.Surname,
		pm.PaymentMethodID,
		fc.OrganisationID,
		s.EmployerID,
		e.FeeSourceID,
		fc.ReceiptCode,
		fc.Amount,
		fc.TillCode,
		o.Code,
		qlc.LastModifiedDate,
		fc.StudentDetailID,
		fc.FeeContributionID

HAVING		MAX(fc.LastModifiedDate) > MAX(qlc.LastModifiedDate) --(select MAX(LastModifiedDate) from Extra.dbo.QLCashBookExport where StudentDetailID = fc.FeeContributionID)


--ORDER BY ISNULL ( MAX ( fc.[LastModifiedDate] ), CAST ( GETDATE() AS DATETIME))




--*******************--

--INSERT into a Table for Checks with NEW Records

--*******************--



INSERT INTO Extra.dbo.QLCashBookExport
(
	[StudentDetailID],
	[FeeContributionID],
	[RefNo], 
	[Journal Code], 
	[Ledger Code], 
	[Transaction Date], 
	[Reference1], 
	[Reference2], 
	[Description], 
	[Paying In Reference], 
	[Account Code], 
	[Payee Name], 
	[Receipt Number], 
	[Header Net Value], 
	[Header VAT Code], 
	[Header VAT Value], 
	[Header Gross Value], 
	[Header Discount Value], 
	[Project Chart Code], 
	[Project Account Code], 
	[Code], 
	[GLCODE], 
	[Line Net Value], 
	[Line VAT Code], 
	[Line VAT Value], 
	[Line Gross Value], 
	[Line Discount], 
	[LastModifiedDate]
)
SELECT
	[StudentDetailID],
	[FeeContributionID],
	[RefNo], 
	[Journal Code], 
	[Ledger Code], 
	[Transaction Date], 
	[Reference1], 
	[Reference2], 
	[Description], 
	[Paying In Reference], 
	[Account Code], 
	[Payee Name], 
	[Receipt Number], 
	[Header Net Value], 
	[Header VAT Code], 
	[Header VAT Value], 
	[Header Gross Value], 
	[Header Discount Value], 
	[Project Chart Code], 
	[Project Account Code], 
	[Code], 
	[GLCODE], 
	[Line Net Value], 
	[Line VAT Code], 
	[Line VAT Value], 
	[Line Gross Value], 
	[Line Discount], 
	[LastModifiedDate]

FROM	#QLCashBookUpd




--SET IDENTITY_INSERT Extra.dbo.QLCashBookExport OFF


--*******************--

--Insert Check Ends

--*******************--


SELECT *

FROM	#QLCashBookUpd

order by Refno

DROP TABLE	#QLCashBookUpd


END

5fifty5
Starting Member

35 Posts

Posted - 2009-12-17 : 04:34:53

Can someone help please?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-21 : 06:49:19

Looks to me like FeeContribution has a later date than the last date you inserted into QLCashBookExport. Not too sure on your logic here anyway, but without sample data and structures, I can only assume that you think its right.

Anyway, check the dates in FeeContribution and QLCashBookExport, check you are not comparing a date with a time part with a date without a time part as this can also trip you up.

5fifty5
Starting Member

35 Posts

Posted - 2009-12-22 : 05:02:50

Logic is to get records from FeeContribution on the first run, put them in an Excel file as CSV (that will be done through DTS). When the Stored Procedure (i.e. this query itself in SP) is executed the next time it looks for NULL in [Detail Number] column and since the records were inserted the last time, it will execute the second part of the query.

Now, in second part, it has to look for for the records which have already been inserted in CashBook table and if they were not modified, they shouldn't be put in the CSV file & the only way to check that is to look at LastModifiedDate.

However, if the date from FeeContribution is greater than CashBook table, meaning the record has been modified since the last run, should put the record in the CSV and also insert in CashBook table (that is why I have used the MAX(fc.LastModifiedDate) > MAX(qlc.LastModifiedDate) in HVING clause).

The problem is that the date in FeeContribution is exactly the same as CashBook table (Refer to my first post) in 2nd, 3rd, 4th and so forth run of query.

For instance a record modifed would keep coming back again in the query even when the date is actually equal to the date in cashbook table and NOT greater than that.

The data type is exactly the same. I have created a script for FeeContribution table from Enterprise Manager and used that same field in script for Cashbook table.

There is nothing like time part being used in the query and it has baffled me as to where & whats going wrong in the query.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-22 : 06:18:16

Ok, then you join condition:


and	fc.LastModifiedDate > qlc.LastModifiedDate

seems to be wrong for me. Without sample data, it is difficult to say whether your logic is correct though, especially as you have an ISNULL condition in the select statement.

5fifty5
Starting Member

35 Posts

Posted - 2009-12-22 : 09:37:32

quote:
Originally posted by RickD

Ok, then you join condition:

and fc.LastModifiedDate > qlc.LastModifiedDate

seems to be wrong for me. Without sample data, it is difficult to say whether your logic is correct though, especially as you have an ISNULL condition in the select statement.

Tell you what, I have tried that join condition too and it hasn't worked :).

If you refer to the first post, this is whats happening (ofcourse there are lots of columns, but I didn't want to put in more columns to complicate things even more)

quote:

However, When the query runs the next day, even if there was no change in the record, it still brings it up with the same date, so the NewTable will have the following records:

NewTable On third night
ID Name LastModifiedDate
===========================================
1 Alaska 2009-12-15 10:05:25.000
2 Seattle 2009-12-15 10:10:00.000
3 NY 2009-12-15 10:30:25.000
4 NewYork 2009-12-15 10:45:01.000
5 NewYork 2009-12-15 10:45:01.000

Say that ID = 4 was inserted on 3rd night. The same record gets inserted into the Cashbook table even when the date was equal and NOT greater.

I hope it helps. If not, I will post some sample data, with lots of columns.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-22 : 10:16:42

please do post some sample data in an easy to use format. See the FAQs if you need more info.

jjoya
Starting Member

5 Posts

Posted - 2009-12-22 : 21:54:20

SELECT TOP 100 PERCENT dbo.MDC_ProductBin.InventoryPackNo, MAX(dbo.ModuleSummary.TestDate + 2 + dbo.ModuleSummary.TestTime)
AS MaxTestDateTime
FROM dbo.MDC_ProductBin LEFT OUTER JOIN
dbo.ModuleSummary ON LEFT(LTRIM(dbo.MDC_ProductBin.InventoryPackNo), 12) = LEFT(LTRIM(dbo.ModuleSummary.Name), 12)
WHERE (dbo.ModuleSummary.VmpMOd IS NOT NULL) AND (dbo.ModuleSummary.VocMod IS NOT NULL) AND (dbo.ModuleSummary.ImpMod IS NOT NULL) AND
(dbo.ModuleSummary.IscMod IS NOT NULL) AND (dbo.ModuleSummary.FF IS NOT NULL) AND (dbo.ModuleSummary.Power > 100) AND
(dbo.ModuleSummary.Power IS NOT NULL)
GROUP BY dbo.MDC_ProductBin.InventoryPackNo
ORDER BY dbo.MDC_ProductBin.InventoryPackNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources