Create view to calculate days until expiration

[Lost500]

Hi all,

I'll start with i have no idea what i'm doing.

I have a table that records the expiration date for each record. what i am looking for is a table or view or maybe something else that will calculate the days until the expiration date ( Date()-[ExpirationDate]) I can do this in access easy but i'm new to the SQL Server management studio and views and all that jazz.I don't know the correct functions in SQL and my VB know how is getting me confused.

will someone help me out in setting up a view that will when opened have the records and their days until expiration?

I have created a new view and added my tables but i'm stuck there will someone help? thanks

 

[markros]

select cast(datediff(day,ExpirationDate, getdate()) as int) as Days, ExpirationDate, IdField from myTable

 

[gmmastros]

If I recall correctly, Access has a Date function that will return the current date. SQL Server does not have a Date function, but you can still accomplish your goal.

Instead, there is a GetDate() function. This function returns the same information as Access's NOW function because it returns the current date and time. To get just the date portion, there are a couple tricks you can use. The fastest to execute is...

DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

So... you could use something like....

[tt][blue]
DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) - [ExpirationDate][/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Sorry, it is even simpler

select datediff(day, getdate(),ExpirationDate) as DaysBeforeExpiration

 

[Lost500]

Thanks a lot so if i'm trying to create a view so that another program can link to it where would i put that code in my view builder? sorry i hope yall have used SQL management studio Or if there is a better way to create the view i do take direction

 

[markros]

In the new query window put

CREATE VIEW [dbo].[v_ExpirationDatesInfo]
AS
SELECT     datediff(day, getdate(),ExpirationDate) as DaysBeforeExpiration, ExpirationDate, OtherFields 
FROM        myTable

and run it.

 

[Lost500]

sweet. thanks ok you're one question away from infinite stars (in my mind)

I have a one to many relationship to another table. How do i get the lowest (which i hope would be the earliest date to expiration) for each of the records in the one table.

TableA has leases and their expiration. TableB Has the Unit that many leases are in. So i need the earliest lease expiration for each unit. in access i did a totals query and selected min and it would give me the minimum value for each unit.

so...

TableA
LeaseNumber ExpirationDate UnitName
Lease1 1/1/2020 Unit1
Lease2 1/1/2010 Unit1
Lease3 1/20/2010 Unit2

The view if it worked would return

Veiw
UnitName DaysUntilExpiration
Unit1 87
Unit2 106

This shows the number of days to the Earliest expiration for each unit even though there are other leases in that unit, the view shows the earliest record next to the unit.

This might be a little more involved than the last question but I've done this in access with a totals query so i think its doable. Thanks for the help!

 

[markros]

Here is a comprehensive link on this topic

http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=71

However, to answer your question simply, it would be

select T.*, D.ExpirationDate from TableB T inner join (select UnitName, Min(ExpirationDate) as ExpirationDate from TableA group by UnitName) D on T.UnitName = D.UnitName

If you need to also get extra fields from TableA, then I suggest you to review closely the above mentioned link and play with its samples.

 

[Lost500]

ok maybe this is simplier:
I tried adding MIN() and GROUP BY to this and it didn't work. is this different for SQL?

SELECT     MIN(DATEDIFF(day, GETDATE(), dbo.tblLeaseInfo.ExpirationDate)) AS DaysBeforeExpiration, dbo.tblUnitInfo.UnitName
FROM         dbo.tblLeaseInfo INNER JOIN
                      dbo.tblTractLeaseAcres ON dbo.tblLeaseInfo.LeaseNumber = dbo.tblTractLeaseAcres.LeaseNumber INNER JOIN
                      dbo.tblTractInfo ON dbo.tblTractLeaseAcres.TractName = dbo.tblTractInfo.TractName INNER JOIN
                      dbo.tblUnitInfo ON dbo.tblTractInfo.UnitName = dbo.tblUnitInfo.UnitName
GROUP BY dbo.UnitInfo.UnitName

as you can see there are actually two tables to get through in order to have the DaysUntilExpiration record next to the unit name record

I am getting the error 'the multi-part identifier "dbo.UnitInfo.UnitName" could not be bound' Please Help!

 

[Lost500]

ok I see your post and i need to ask... what is T and D?

 

[markros]

You have just a typo in your last statement - it should be Group by tblUnitInfo.UnitName

In my case I used T and D as aliases. T for the main table and D for the derived table.

 

[Lost500]

Thanks markros its awesome

 

[markros]

No problem.