Solved

SQL-stmt

Posted on 2001-07-18
7
263 Views
Last Modified: 2008-03-06
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)
0
Comment
Question by:debbie790
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 2

Expert Comment

by:UncleMatt
Comment Utility
Select * from view where Date_yr = Year(getdate()) and date_month = (Select max(date_month) from view where Date_yr = Year(getdate()))

It's probably not the best way to do it but it should work!

Matt.
0
 
LVL 3

Expert Comment

by:ibro
Comment Utility
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.
0
 

Author Comment

by:debbie790
Comment Utility
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.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:ibro
Comment Utility
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"]


0
 
LVL 2

Expert Comment

by:UncleMatt
Comment Utility
What format is date_month?

Is it the numeric month value?

If so, what was the problem with my solution?
0
 

Expert Comment

by:arak_69
Comment Utility
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


0
 

Accepted Solution

by:
arak_69 earned 50 total points
Comment Utility
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now