debbie790
asked on
SQL-stmt
Have a View with fields:
ID (PK)
Date_Field (Date)
Date_Month (Extracts the month from above date)
Date_Yr (Extracts the year from above date)
Amt
The data is collected on a monthly basis, also the data might miss for some months.
I want to write a sql that for a given year gives me the latest data for that year.
ie, lets say data was collected for Jan, Feb, May for 2001
If the sql was ran today the output would be May's value (ie. the data was recorded in May for yr-2001)
ID (PK)
Date_Field (Date)
Date_Month (Extracts the month from above date)
Date_Yr (Extracts the year from above date)
Amt
The data is collected on a monthly basis, also the data might miss for some months.
I want to write a sql that for a given year gives me the latest data for that year.
ie, lets say data was collected for Jan, Feb, May for 2001
If the sql was ran today the output would be May's value (ie. the data was recorded in May for yr-2001)
Select * from myview where Date_field=( select max(Date_field) from myview)
Can you have more than 1 record with the same date? If not the above will do your work.
Can you have more than 1 record with the same date? If not the above will do your work.
ASKER
Firstly, it is not possible to have > 1 record with the same date.
Also, the above solutions didnt work:
myView:
ID (PK)
DeptID
Date_Field (Date)
Date_Month (Extracts the month from above date)
Date_Yr (Extracts the year from above date)
Amt
SELECT * FROM myView
WHERE DateField = ( SELECT MAX(Date_field) FROM myview WHERE DeptID = 100)
The above gave me all records -- repeating them 3-times.
Also, the above solutions didnt work:
myView:
ID (PK)
DeptID
Date_Field (Date)
Date_Month (Extracts the month from above date)
Date_Yr (Extracts the year from above date)
Amt
SELECT * FROM myView
WHERE DateField = ( SELECT MAX(Date_field) FROM myview WHERE DeptID = 100)
The above gave me all records -- repeating them 3-times.
Hi debbie790,
As far as I understand your explanation [it is not possible to have > 1 record with the same date], it seems that data field is unique in the myView. Is this true.
If it is, then the statement will give you only one record, which will be the recent one.
There is probable something that you didnt mention...
Since the above query doesnt work for you, what is your unique value? Is it DeptID+Date_field. If it is the statement shall look like this:
SELECT * FROM myView
WHERE DepID=100 and DateField = ( SELECT MAX(Date_field) FROM myView WHERE DeptID = 100)
Btw is your SQL7 configured as case-sensitive or case-insensitive? Are "myView" and "myview" same views or different? [in your statemnt in main query is "myView" and in subquery "myview"]
As far as I understand your explanation [it is not possible to have > 1 record with the same date], it seems that data field is unique in the myView. Is this true.
If it is, then the statement will give you only one record, which will be the recent one.
There is probable something that you didnt mention...
Since the above query doesnt work for you, what is your unique value? Is it DeptID+Date_field. If it is the statement shall look like this:
SELECT * FROM myView
WHERE DepID=100 and DateField = ( SELECT MAX(Date_field) FROM myView WHERE DeptID = 100)
Btw is your SQL7 configured as case-sensitive or case-insensitive? Are "myView" and "myview" same views or different? [in your statemnt in main query is "myView" and in subquery "myview"]
What format is date_month?
Is it the numeric month value?
If so, what was the problem with my solution?
Is it the numeric month value?
If so, what was the problem with my solution?
Select *
From MyView
Where Date_Field =
(SELECT Max(Date_Field )
FROM MyView
WHERE Date_Yr = My_Year)
It will return you only records for last date for given year in that database
If You Need it For whole Month
Select *
From MyView
Where Date_Month =
(SELECT Max(Date_Month )
FROM MyView
WHERE Date_Yr = My_Year)
AND Date_Yr = My_Year
From MyView
Where Date_Field =
(SELECT Max(Date_Field )
FROM MyView
WHERE Date_Yr = My_Year)
It will return you only records for last date for given year in that database
If You Need it For whole Month
Select *
From MyView
Where Date_Month =
(SELECT Max(Date_Month )
FROM MyView
WHERE Date_Yr = My_Year)
AND Date_Yr = My_Year
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's probably not the best way to do it but it should work!
Matt.