Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL-stmt

Posted on 2001-07-18
7
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6296109
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
ID: 6297141
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
ID: 6298270
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 3

Expert Comment

by:ibro
ID: 6298357
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
ID: 6299148
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
ID: 6324182
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 200 total points
ID: 6324184
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 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