Between two dates

[garyhyd]

Hi

I am new to the forum and request help.


I have a table with the following columns,

TransdateCustomer NameLocationUnit PriceWk Start DateWk end DateLicence Start DateLicence End Date

I need a SQL Select statement to return location and unit price for a customer where the transaction date is between the Wk Start date and Wk end Date and transaction date is between Licence Start date and Licence end date.

Pls help me

Thanks Gary

 

[jzwp22]

I'm a little confused by the WkStart and WkEnd being in the same table as the transdate. What type of data is in WkStart and WkEnd? Is it related already to the transdate? The same questions apply for license start and end?

 

[garyhyd]

Hi

Thanks,

The transaction date is the date on which a purchase transaction is recorded

Wk st date and end date refers to a customer being active with in specified range of week dates

While License st date and end date refers to the dates within which the customer could avail the license.

each of the dates are independent

I would like to get the customer, location and unit price for purchase date between the week dates where the customer is active and that the license is live


Regards
Gary

 

[jzwp22]

Before we get into the query issue, I don't think your table is correct. If the Wkstart/end and the license date refer to the customer, they should not be in the same table as the transactions. For example, I would structure my tables like this:

tblCustomers
-pkCustID primary key, autonumber
-txtCustomerName
other customer related fields

tblTransactions
-pkTransID primary key, autonumber
-fkCustID foreign key to tblCustomers
-dteTrans (transaction date)

What does a customer being active mean? What determines whether a customer is active or not? Can a customer be active for periods of time and then become inactive and back to active again?

I don't know what the license information is about. Does a customer have to have a license to buy from you? Is this license renewable?