SQL Query Usings Dates In A VBA Sub

Jul 19, 2007

Hi My first post :-)

I am trying to build a VBA Sub that uses a MS SQL query, this is fine it is just I am having trouble building the query to do what I need.

I need to be able to do a between month and years, so if I have a VBA routine that constructs a query from variables. Those variable will be a month and a year.

so I can return a count of all records that were logged between the month and year.

My poorly constructed query is below.

SELECT [DateRaised]
FROM HD_Call
WHERE DateRaised BETWEEN DATEADD([MONTH], -1, GETDATE()) AND DATEADD([MONTH],0, GETDATE())
AND DateRaised BETWEEN DATEADD([YEAR], -2, GETDATE()) AND DATEADD([YEAR],0, GETDATE())
AND [completed] = 1
AND [Assignee] LIKE '%'
AND [CallType] LIKE '%'
Order By 1

Thanks in advance

View 4 Replies

Strange Exceptions With Views Usings Casts On 2005 (worked Fine On Sql2k)

Apr 3, 2007

I have views that cast the data into data types.

On 2005 when retrieving the views using a where clause i get errors. I have put together an example below that illustrates the error.

CREATE TABLE TestData ( Id VARCHAR(10), [Description] VARCHAR(20), [Type] INT)

CREATE TABLE TestTypeLookup( [Type] INT, [Description] VARCHAR(20))

GO

INSERT INTO TestTypeLookup VALUES (1, 'Type 1')

INSERT INTO TestTypeLookup VALUES (2, 'Type 2')

INSERT INTO TestData VALUES ('AA', 'Description for AA', 1)

INSERT INTO TestData VALUES ('10', 'Description for 10', 2)

INSERT INTO TestData VALUES ('20', 'Description for 20', 2)

GO

CREATE VIEW TestView AS

SELECT TOP 100 PERCENT CAST(Id AS INT) Id, [Description]

FROM TestData

WHERE [Type] = (SELECT TOP 1 [Type] FROM TestTypeLookup WHERE [Description] = 'Type 2')

ORDER BY CAST(Id AS INT)

GO

SELECT * FROM TestView WHERE Id = 10

GO

DROP VIEW TestView

DROP TABLE TestData

DROP TABLE TestTypeLookup

GO

View 11 Replies View Related

Between Dates Query - HELP!!!

Oct 10, 2006

Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint

View 8 Replies View Related

How To Query Dates

Mar 16, 2004

How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.

WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()

Thanks,

View 1 Replies View Related

SQL Query And Dates

Aug 3, 2004

Hi, im trying to write a sql query to check if dates are in the db,
ias it is it is telling me the dates are there and their not. any help would be great.
George

here's what i have...

SQL2 = "SELECT * FROM reservations WHERE" _
& "(arvdate >=#" & request("arvdate") & "# AND endate <= #" & request("endate") & "#) " _
& "OR " _
& "(arvdate >=#" & request("arvdate") & "# AND endate >= #" & request("endate") & "#) " _
& " AND idrent = " & idrent _
& " AND confirmation = 1" _
& " ORDER BY arvdate"

View 3 Replies View Related

Query Between Dates

Oct 23, 2007

I have two tables: Sales, Consigners

The Sales table includes: date, description, price, consigner
The Consignment table includes: lastday, consigner

I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.

I have this and it isn't working:

SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1
FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));

This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all

View 7 Replies View Related

How To Do This Between Dates Query?

Sep 6, 2006

Hi,

I have a query as follows:

SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)

the results look like this

WeekRetailTotal
MonthRetailOrderNo
SalesWeek
SalesYear

�397.55
8
3
2002

etc etc for each week in a year and then it goes onto the next year.

What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.

I've tried to do a WHERE DATEPART(wk, OrderDate) > @StartDate AND DATEPART(wk, OrderDate)
< @EndDate AND YEar(OrderDate) > @StartYear AND YEAR(OrderDate) < @EndYear

But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.

I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.

Any ideas?

Thanks

View 4 Replies View Related

Query Question/help With Dates

Sep 30, 2007

Hello,
I have a sql query that I am requireing to get records on date parameters :
SELECT * FROM table WHERE StartDate >= 2007/09/30 07:00:00  AND EndDate <= 2007/09/30 09:00:00
I have records with StartDate 2007/09/30 08:00:00,   2007/09/30 08:30:00 which this query does not select.
I am trying to select all StartDate which fall between 2007/09/30 07:00:00  AND   2007/09/30 09:00:00.
Any suggestions on the query design

Thanks
Peter

View 6 Replies View Related

Need Help With Query That Compares Dates

Dec 11, 2007

I have figured out how to work with dates in SQL, but don't know how to put it all together to completea sql query I am working on. Here are some examples of the basic MS SQL date routines I know:
SELECT DATEDIFF(day, '11-2-2007 11:11:11', '12-4-2007 11:11:11') -- Difference between two dates.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] -- Get's today's date
Now back to my problem....I have the following Query that must be modified so that the filter criteria is based upon the"StartDate" being greater than today's date. Can someone please help me out.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable
Note that the date passed in for Start date will be in a format like the following '11-2-2007 09:1:11'.
So if I were to pseudo code out the query it would be something like the following, where the pseudo codeis in parenthesis.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable (WHERE StartDate > Todays Date)

View 1 Replies View Related

SQL Query For Data Between 2 Dates

May 17, 2005

Hi.,
Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period.
Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria
This results in the follwoing SQL Statement:
strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005
Regards.
Peter

View 14 Replies View Related

SQL QUERY Help Please.. (Comparing Dates)

Dec 1, 1998

hi,

i would like to create an SQL query, part of which involves comparing dates.

i have something like

Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....

how can i compare the StartDate to something i have in the form of a string? for example, i have
Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.

i'd appreciate all the help i can get.

thanks,
sb

View 1 Replies View Related

SQL Query Or SP? Order Value Between Two Dates

Feb 7, 2008

Hi all,

Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!

Table Structure (abridged, relevant columns):

Orders:

Code:

[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]

OrderItems:

Code:

[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]

Products:

Code:

[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)

Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.

I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.

So far I have this:

Code:

SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle

Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again

I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!

Thanks in advance to anyone that looks at this for me.

Also, any recommended books/sites to learn this kind of query?

Richard

View 4 Replies View Related

Query For Distinct Dates

Sep 4, 2014

I have a need to only find distinct dates in which a worker worked in the factory using TSQL

Code:
RowNumber workerstartDate workerenddate
1 2012-08-08 2012-10-10
2 2012-08-10 2012-08-31
3 2012-09-05 2012-09-15
4 2012-10-15 2012-12-19
5 2013-01-02 2013-03-14
62013-03-15 2013-05-23

Basically, I am looking for the above to look like this

Code:
rownumberworkerstartDate workerenddate
12012-08-08 2012-10-10
42012-10-15 2012-12-19
52013-01-02 2013-03-14
62013-03-15 2013-05-23

View 2 Replies View Related

Query Which Should Return All The Dates Between 2

Jul 20, 2006

i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this
2-1-2006
3-1-2006
4-1-2006
.
.
.
16-03-2006
17-03-2006
18-03-2006

View 1 Replies View Related

Query - Between To Dates And Between To Times

Jul 24, 2001

Hi there, I'm a little bit stumped on this one.

I have a column in a table that records when the date and time of an event took place.

Table Name: Chronicle
Column Name: Created (of type DateTime)

I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)

Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?

cheers,
Hamish

Hamish Norton
hamish@liftrider.com

View 1 Replies View Related

Query Help Please - Consecutive Dates

Jul 23, 2005

Hello,Can someone please help me with a query?The table looks like this:BookedRooms===========CustomerID RoomID BookDateID1 1 200507011 1 200507021 1 200507031 1 200507091 1 200507101 1 200507111 1 20050712Desired result:CUSTOMER STAYS==============CustomerID RoomID ArriveDateID DepartDateID1 1 20050701 200507031 1 20050709 20050712Basically, this is for a hotel reservation system. Charges varynightly, and customer changes (shortening/extending stay, changingrooms, etc) happen quite often. Therefore, the entire stay is bookedas a series of nights.The length of the stay is never known, so it needs to be derived viathe Arrive and Depart Dates, based on the entries in the table.Notice, customers often stay in the same room, but with gaps between,so a simple MIN and MAX doesn't work. The output needs to showconsecutive nights grouped together, only.I've researched this quite a bit, but I just can't seem to make itwork.Any help would greatly be appreciated.Thanks!

View 15 Replies View Related

Query For Getting Missing Dates In Months

Feb 5, 2008

I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this

View 2 Replies View Related

Searching Dates In Query Analyzer

Dec 7, 2007

View 3 Replies View Related

Comparing Dates In MS SQL 2000 Query

Feb 8, 2008

Hi,
I have a table that temporarily stores information. Before I insert new data in the table, I want to delete the previous day's data.

This is the query I'm using
Code:

q="DELETE FROM SearchTemp WHERE theDate < "&Date()
set RS2 = DB.Execute(q, ra, adCmdText)

I should say I'm using ASP & VBScript.

Problem is, this isn't deleting the previous day's dates.

The dates are stored in this format: m/d/yyyy.

I've searched through old posts but none of the solutions offered have worked for me so far. Anyone have any suggestions?

Thanks!

View 4 Replies View Related

Query To Get Recent Date From The Last 30 Dates

Feb 8, 2006

Hi,

I have the following table:

Table name Employee
===============
emp_id,
emp_name,
emp_city

Table name EmpStatusReport
===================
emp_id,
action
date

I need to write a sql query to get the emp_name, emp_city and the recent date when the user has sent status report over last 30 days. The user has sent a status report if action field in the empStatusReport is set to 'reported'. This table gets filled everytime user sends report.

I tried to the do the following:

select e.emp_name, e.emp_city, esr.date from employee e, EmpStatusReport esr where e.emp_id and esr.emp_id and
/* esr.date = max(esr.date) and esr.date > currentDate - 30 and esr <= currentDate */

I am not able to write a correct login for date part. Any help in this will be highly appreciated.

Thanks!

View 4 Replies View Related

Formatting Dates As YYYY/MM/DD For A Particular Query

Oct 3, 2005

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2)).... Which seems so rediculous. There's gotta be a better way!

View 3 Replies View Related

Searching Dates In Query Analyzer

Dec 11, 2007

Hello Gang,

I have a strange problem that I haven't dealt with before.

I need to execute a piece of code based on date ranges. If the date range is:

Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y

I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.

[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or

[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>

In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.

How do I achieve both # 1 & 2 above in a code? Does this explain better.

View 4 Replies View Related

Dates In Query Analyser - More Info

Apr 21, 2006

I am trying to test sp in Query Analyser as below but get the error

Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.

How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?

EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''

View 20 Replies View Related

Query Between Dates But Group By Month

May 22, 2008

hello, how can I query dates using "between" function but grouped by months? for example:

QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008

RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345

Thanks in advance-!

View 4 Replies View Related

Problem Using Query To Get Records Between Two Dates

Feb 10, 2008

I�m trying to select records which are between two dates. I use the following statement.

qry = System:tring::Format("SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr="+tbStudentId->Text+" and dateofmeal >=#"+dt->ToString("dd/MM/yyyy 00:00:00" )+"# and dateofmeal <=#"+dt2->ToString("dd/MM/yyyy 00:00:00" )+"#" );

Although the records exists the query does not get these records. If I go to the Query Design and use the same query it works but only if I enter the dates manually (dateofmel >=?).

Can anybody help me to solve thih /

Thanks

View 3 Replies View Related

Query In Reference To Dates Not Working Properly

Sep 14, 2006

Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!

View 2 Replies View Related

SQL Query - DateTime - Percentage Between Two Dates For A Year (Jan. 01 - Dec. 31)

Jan 14, 2008

Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008 but if you wanted to find the percentage for year 2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples: start: 01/01/2007 end: 05/01/2007 if for year: 2007; this is 4 months @33.33% start: 05/01/ 2006 end: 06/01/2007 if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006) start: 01/01/2000 end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you!

View 4 Replies View Related

Query Earliest Dates Of Each Year In A Table

Nov 30, 2011

I have to find the earliest date for any existing calendar year in a table...

For instance.....

ClientID Year
1 1/1/2000
1 2/1/2000
2 3/1/2000
2 4/1/2001
2 5/1/2001

The results I need are....

ClientID Year
1 1/1/2000
2 3/1/2000
2 4/1/2001

It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.

Here is the first join I have....

SELECT DT.ClientID, Year1 FROM
(
SELECT ClientID, YEAR(MyDate) as Year1
FROM MyTable
) AS DT
GROUP BY ClientID, Year1
ORDER BY ClientID, Year1

I want to use that as my derived table and then join back to it... but it's not working...

View 1 Replies View Related

Order Converted Dates In Union Query

Jun 15, 2006

I have the following as part of a union query:

CONVERT(CHAR(8), r.RRDate, 1) AS [Date]

I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.

Thanks,

Dave

View 1 Replies View Related

Stuck On Select Query Involving Dates

May 11, 2007

Table 1

ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E

Result

ID PID Date
4 1 26/02/07
7 2 04/03/07

I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next ��from record�� should be the same date as the ��to date��. If the code is E then the next ��to�� date can be anytime after the previous ��to�� date.
I want to be able to report on all record where there is a day difference between the previous ��to�� date. I.e. ID 4 and 7 �� the previous records both have an X and there is at least a days difference between the dates.

View 4 Replies View Related

Transact SQL :: Overlapping Dates For Same Member Query

Oct 18, 2015

IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
DROP TABLE #OverLappingDates
CREATE TABLE #OverLappingDates
(
MemberID Varchar (50),
ClaimID Varchar(50),
StartDate DateTime,
EndDate DateTime
)

[Code] ....

I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).

View 2 Replies View Related

Transact SQL :: Query To Get Date Span Between Dates

May 23, 2015

I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.

SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN
, CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit
, pv.TicketNumber
, vstatus.Description AS VisitStatus
, doc.ListName AS Doctor

[Code] ....

View 3 Replies View Related

SQL Query - Duplicate Records - Different Dates - How To Get Only Latest Information?

Mar 17, 2006

I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

View 24 Replies View Related