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 2008 Forums
 Transact-SQL (2008)
 How do you compare two dates in a query?

Author  Topic 

BadBoy House
Starting Member

41 Posts
Posted - 2010-07-08 : 07:15:55
Hi all.

I've created the query below which displays various due dates for clients. The query itself works fine however I need to create another query based on this one.

I need to set the query to only pick out clients where the 'Send Reminder' field is the same as the 'Current Month Start Date' field.

Ordinarily I would just use a date = date code in the WHERE section however this query is slightly more complex as you will see below.

Can anyone tell me what code I need to use?

Thanks in advance.



SELECT
	tblClient.ClientRef AS 'Ref',



CASE
	WHEN ISNULL(tblClient.FirstName,'') = ''
		THEN tblClient.SearchName
		ELSE tblClient.FirstName + ' ' + tblClient.SearchName
	END AS 'Client Name',

	tblPartner.ReportField AS 'Partner',
	tblManager.ReportField AS 'Manager',
	tblClientExtraDetails."A/C_Yr_End_09" AS 'Accounts Year End 09',


/* Work out payment due dates based on the specific month */
/* ------------------------------------------------------ */

CASE

/* February Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '2'
		THEN DATEADD(DAY, 3, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))

/* April Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '4'
		THEN DATEADD(DAY, 2, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))

/* June Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '6'
		THEN DATEADD(DAY, 2, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))

/* November Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '11'
		THEN DATEADD(DAY, 2, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))

/* All other Accounts Year End Months */
		ELSE DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))

END AS 'Payment Due Date',

/* End of payment due dates calculation */


/* Send Reminders */
/* ---------------*/

CASE

/* February Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '2'
		THEN DATEADD(DAY, 3, DATEADD(MONTH, 7, tblClientExtraDetails."A/C_Yr_End_09"))

/* April Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '4'
		THEN DATEADD(DAY, 1, DATEADD(MONTH, 7, tblClientExtraDetails."A/C_Yr_End_09"))

/* June Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '6'
		THEN DATEADD(DAY, 2, DATEADD(MONTH, 7, tblClientExtraDetails."A/C_Yr_End_09"))

/* November Accounts Year Ends */
	WHEN MONTH (tblClientExtraDetails."A/C_Yr_End_09")= '11'
		THEN DATEADD(DAY, 2, DATEADD(MONTH, 7, tblClientExtraDetails."A/C_Yr_End_09"))

/* All other Accounts Year End Months */
		ELSE DATEADD(DAY, 1, DATEADD(MONTH, 7, tblClientExtraDetails."A/C_Yr_End_09"))

END AS 'Send Reminder',

/* End of Send Reminders */





	tblClientExtraDetails.CT203_Rcvd_09 AS 'CT603 Received',
	tblClientExtraDetails.CT200_STC_09 AS 'CT600 Sent To Client',
	tblClientExtraDetails.Date_Signed_09 AS 'Date Signed',
	tblClientExtraDetails.Sent_HMI_09 AS 'Sent to HMIT',
	tblClientExtraDetails.NTP_Sent_09 AS 'NTP Sent',


   DATEADD(MONTH,DATEDIFF(MONTH, 0, GetDate()),0) AS 'Current Month Start Date',

   tblStaff.Email AS 'Manager Email'



FROM
	tblManager 
   INNER JOIN ((tblClient INNER JOIN tblPartner ON tblClient.PartnerID = tblPartner.PartnerID)
   INNER JOIN tblClientExtraDetails ON tblClient.ClientID = tblClientExtraDetails.ClientID) ON tblClient.ManagerID = tblManager.ManagerID
   LEFT JOIN tblStaff ON tblManager.StaffID = tblStaff.StaffID


WHERE

	tblManager.ManagerID > 0 and
	tblClient.Suspended = 0 and
	tblClientExtraDetails.Prospective = 0 and
	tblClient.ClientTypeID = 3

  


ORDER BY

	tblManager.ManagerID,
	tblClientExtraDetails."A/C_Yr_End_09"

Kristen
Test

22859 Posts
Posted - 2010-07-08 : 07:19:50
[code]
SELECT *
FROM
(
... Your original query here ...
) AS X
WHERE [Send Reminder] = [Current Month Start Date]
[/code]

??
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-07-08 : 07:57:38
You can get some tips here
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/29/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

BadBoy House
Starting Member

41 Posts
Posted - 2010-07-08 : 08:12:06
that doesn't seem to work - I get invalid column name errors.

is this something to do with aliases? for example, the column aliases I need to compare are [Send Reminder] and [Current Month Start Date].

Can you actually compare aliases?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-07-08 : 08:53:01
You can in an outer wrapper.

I'm a bit confused by your alias names using single quotes (didn't think that was allowed) and we don't even use double-quotes here any more - all too confusing between Strings and Alias names! - but try those instead of my [...] perhaps?
Go to Top of Page

BadBoy House
Starting Member

41 Posts
Posted - 2010-07-08 : 09:13:42
do you have any recommendations or links to how to use wrappers?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-07-08 : 09:41:24
[code]
SELECT *
FROM
(
... Your original query here ...
) AS X
[/code]
That's it, basically! All columns as per the output of the INNER query are available for use in the OUTER query
Go to Top of Page

BadBoy House
Starting Member

41 Posts
Posted - 2010-07-08 : 09:54:15
that just gives syntax errors. could you adjust my original query code from above to include your code in the correct place.

bear in mind that I need to set the query to only pick out clients where the 'Send Reminder' field is the same as the 'Current Month Start Date' field.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-07-08 : 10:49:01
Sorry, move the ORDER BY out of your (now inner) query. Placing it at the bottom it will only be able to use columns SELECTED by the inner query - I ahven't checked it, but if it needs columns that are not in your original query's SELECT list you'll need to add them (and then you may want to put a list of columns in place of my outer SELECT * (in order not to output the columns you just want to sort on). You also need to take the Alias off the ORDER BY columns (everything in the inner query will become aliased as "X")

Example:

SELECT X.Col1, X.Col2, X.Col3, C.SomeColumn
FROM
(
   SELECT A.ID, A.Col1, A.Col2, B.Col3, B.Col4 AS [SomeAliasName]
   FROM TableA AS A
        JOIN TableB AS B
            ON B.ID = A.ID
   WHERE B.Col5 = 1234
) AS X
    JOIN TableC AS C
       ON C.ID = X.ID
ORDER BY X.SomeAliasName
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site