Database

17-Feb-12 5:47

I am having such a hard time writing a SQL query. Can someone please look over this query and see where am I going wrong?

I will be using the XML elements to describe the table even though I am using SQL Server 2008 R2..

HTML

<vPSR>
 <ProjectID/>
 <ReportMonth Comment="2 digit Month. (MM)" />
 <ReportYear Comment="4 digit Year. (YYYY)" />
</vPSR>

Here is an example of the data that is listed.
ProjectID ReportMonth ReportYear
1000700 05 2008
1000700 06 2008
1000700 07 2008
1000700 12 2008
1000700 01 2009 <-- I want the query to find this! Even though the Month is not the max value, but the Year is the Max value.

Here is what I have so far. Where am I going wrong?

SQL

SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
    FROM vPSR
    WHERE vPSR.ReportMonth =
        (SELECT MAX(ReportMonth)
            FROM vPSR AS f
            WHERE f.ProjectID = ProjectID
            AND ReportYear =
                (SELECT MAX(ReportYear)
                FROM vPSR AS k
                WHERE k.ProjectID = vPSR.ProjectID))
    ORDER BY vPSR.ProjectID ASC

I got the idea on how to write the above query here.[^] I don't understand it that well.

If you need me to clarify anything please do not hesitate to ask.

scottgp17-Feb-12 8:19

scottgp

17-Feb-12 8:19

I'm thinking that ReportMonth and ReportYear should instead have been a ReportDate with some sort of Date datatype. Since they're not, concatenating the two columns seems to work:

SQL

SELECT     ProjectID, reportMonth, reportYear
FROM         test
WHERE     (reportYear + reportMonth =
                          (SELECT     MAX(reportYear + reportMonth) AS Expr1
                            FROM          test AS b
                            WHERE      (ProjectID = ProjectID)))

Scott

Corporal Agarn17-Feb-12 8:36

Corporal Agarn

17-Feb-12 8:36

You could try something like

SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
FROM vPSR 
INNER JOIN (
    SELECT ProjectID, MAX(reportYear + reportMonth) AS Expr1
    FROM vPSR 
    GROUP by ProjectID 
) AS v2 
ON vPSR.ProjectID = v2.ProjectID
    AND (vPSR.ReportYear + vPSR.ReportMonth) = v2.Expr1

Clark Kent12321-Feb-12 2:58

21-Feb-12 2:58

I whole heartedly agree about concatenating the ReportMonth + ReportYear columns together. That was another idea that I have been thinking about.

Thanks!

Eddy Vluggen17-Feb-12 8:47

17-Feb-12 8:47

Can you extend the sample population to give some more detail?

In the meantime, here's something to play with;

SQL

SET NOCOUNT ON;
BEGIN TRANSACTION

IF OBJECT_ID('vPSR') IS NOT NULL DROP TABLE vPSR

SELECT 1000700 as ProjectID, 
       05 as ReportMonth,
       2008 as ReportYear
  INTO vPSR
UNION SELECT 1000700, 06, 2008
UNION SELECT 1000700, 07, 2008
UNION SELECT 1000700, 12, 2008
UNION SELECT 1000700, 01, 2009
UNION SELECT 1000701, 11, 2011
UNION SELECT 1000701, 12, 2009

-- If you insist on using a max
SELECT TOP 1 *
  FROM vPSR
 WHERE ReportYear = (SELECT MAX(ReportYear) FROM vPSR)
 ORDER BY ReportMonth DESC

-- this would do too;
SELECT TOP 1 *
  FROM vPSR
 ORDER BY ReportYear DESC, ReportMonth DESC

-- per project?
SELECT DISTINCT ProjectID
     , (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID)
     , (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
			SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
  FROM vPSR A

ROLLBACK

My results;

txt

ProjectID   ReportMonth ReportYear
----------- ----------- -----------
1000701     11          2011

ProjectID   ReportMonth ReportYear
----------- ----------- -----------
1000701     11          2011

ProjectID               
----------- ----------- -----------
1000700     2009        1
1000701     2011        11

Bastard Programmer from Hell Suspicious | :suss:

Clark Kent12321-Feb-12 2:55

21-Feb-12 2:55

Quote:
Can you extend the sample population to give some more detail?

Yes, I sure can!

What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID. Therefore, here is a sample listing of the data that I need to organize.

ProjectID   ReportYear  ReportMonth
----------- ----------- -----------
100005000   2008	5
100005000   2008	6
100005000   2008	7
100006600   2008	8
100006600   2008	9
100006600   2008	10
100006600   2008	11
100006600   2008	12
100006600   2009	1
100006800   2008	8
100006800   2008	9
100006800   2009	1
100006800   2009	2
100006800   2009	3
100006800   2009	4

Note: I trimmed down the data so as not to overwhelm this post, but still relay the idea of what I am dealing with.

There is also one more thing I would like to ask your advice. It's about an idea that I have been thinking about. What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008?

One last question about something that I don't understand and have been searching MSDN and Google to no avail. It's this whole alias thing ("AS" keyword). You can place use this in for the column name and/or the table name. But I don't understand how I can do something to affect of ...

SELECT MAX(ReportYear) FROM vPSR AS f WHERE f.ProjectID = vPSR.ProjectID

I don't understand how f.ProjectID can equal vPSR.ProjectID. This seems very circular to me. This is a technique that I don't use often. If you have any info/advice about this way of writing a query can you link me?

Thanks!

Eddy Vluggen21-Feb-12 7:35

21-Feb-12 7:35

Clark Kent123 wrote:
What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID.

The last sql-statement is almost a literal translation of that request; it fetches a unique list of all projectid's (distinct), and then the maximum year for that projectid. Lastly, it fetches the maximum month for that projectid in the max(year).

SQL

SET NOCOUNT ON;
BEGIN TRANSACTION 
SELECT 100005000 as ProjectID, 
       2008 as ReportYear,
       05 as ReportMonth
       INTO vPSR
UNION SELECT 100005000,   2008,	6
UNION SELECT 100005000,   2008,	7
UNION SELECT 100006600,   2008,	8
UNION SELECT 100006600,   2008,	9
UNION SELECT 100006600,   2008,	10
UNION SELECT 100006600,   2008,	11
UNION SELECT 100006600,   2008,	12
UNION SELECT 100006600,   2009,	1
UNION SELECT 100006800,   2008,	8
UNION SELECT 100006800,   2008,	9
UNION SELECT 100006800,   2009,	1
UNION SELECT 100006800,   2009,	2
UNION SELECT 100006800,   2009,	3
UNION SELECT 100006800,   2009,	4
 
SELECT DISTINCT ProjectID
     , (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) AS MaxReportYear
     , (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
			SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
       AS MaxReportMonth
  FROM vPSR AS A 
ROLLBACK

ProjectID   MaxReportYear MaxReportMonth
----------- ------------- --------------
100005000   2008          7
100006600   2009          1
100006800   2009          4

Clark Kent123 wrote:
What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008?

There's two ways of declaring a temp table in Sql Server, and there's a CodeProject article[^] that compares them Smile | :)

Clark Kent123 wrote:
I don't understand how f.ProjectID can equal vPSR.ProjectID. This seems very circular to me. This is a technique that I don't use often. If you have any info/advice about this way of writing a query can you link me?

It's a subquery; we introduced the table under a new name, being called ~~"F"~~ "A". Let's simply walk through the query; it first fetches all projectid's from vPSR (which we'll call "table A"). For each record, it tries to display the values in the columns of our select-statement, and our second column is a new query. So, it executes that query.

SQL

SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID

Now, the table in the query was called "A", so for the first record this subquery would read as below;

SQL

SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = 100005000

Then it returns that value instead that results from that query.

The technique is called "correlation", which sounds fancy, but really merely means that you're referring to a field in the outer query from a subquery. You could substitute the "A" in the example with any string you'd like, and Sql Server will treat it as if it were a new table with the same contents under that name.

Bastard Programmer from Hell Suspicious | :suss:

Clark Kent12322-Feb-12 2:21

22-Feb-12 2:21

Wow. You have given me much food for thought. Let me digest all this info. If I have any questions I will bug you again! Laugh | :laugh:

I really do appreciate the help. Thumbs Up | :thumbsup:

Eddy Vluggen23-Feb-12 0:05

SSRS 2005 verses OUTLOOK 2007

23-Feb-12 0:05

My pleasure, and you're welcome Smile | :)

SQL Ed17-Feb-12 1:00

SQL Ed

17-Feb-12 1:00

SQL

--------------------------------------------------------------------------------

I have spent weeks on a SSRS reporting services 2005 report. I set up a subscription to email automatically. Managers with Outlook 2003 can view the report perfectly. Managers with Outlook 2007 get a truncated and garbled email report from Report Manager. Has anyone come across this before???

Re: SSRS 2005 verses OUTLOOK 2007

cjb1105-Apr-12 1:24

cjb110

5-Apr-12 1:24

Well I'm guessing that the emailed report is identical, so it must be something changed in the client config or the Exchange server.

Have you been through the various Outlook options relating to encoding?

Also it seems like your mid upgrade too, maybe somebody changed something on the Exchange side?

As a test could you send an report to a completely different address (i.e. gmail).

oracle 11g alias to query

hadad14-Feb-12 22:28

hadad

14-Feb-12 22:28

SQL

When migrating from oracle 10g to 11g, I've a stored procedure with a syntax like:

select * from table1
union
select t,y from
(select * from table2)aliasQuery
where (select max(t)from (select t from table 3 where table3. t=aliasQuery.t)

query works well with 10g but 11g returns error that aliasQuery not defined

may be this syntax no longer supported in 11g or here is some database missing configurations

Thanks

Dad

modified 15-Feb-12 5:57am.

Jörgen Andersson15-Feb-12 1:37

Jörgen Andersson

15-Feb-12 1:37

Could you check that you wrote the query correctly. It seems incorrect.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Chris Meech15-Feb-12 3:00

Chris Meech

15-Feb-12 3:00

Logically speaking

select ..
  from ( select * from table ) aliasquery
 where ...

is no different from

select ..
  from table aliasquery
 where ...

Please verify the SQL you have posted. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

David Skelly15-Feb-12 6:24

David Skelly

15-Feb-12 6:24

The question has been answered on stackoverflow. For anyone who wants to know, the answer is No - this does not work on 11g. For more details why, go here:

http://stackoverflow.com/questions/9290653/oracle-11g-alias-to-query[^]

Bernhard Hiller15-Feb-12 21:04

Bernhard Hiller

15-Feb-12 21:04

Oracle is so great! They even fix bugs which caused such queries to be possible.

David Skelly15-Feb-12 22:05

David Skelly

15-Feb-12 22:05

I know, I had to think very carefully about that one. This query used to work but only because it was a bug (in other words you thought it was good, but in fact we knew it was bad), so we fixed the bug and now your query doesn't work anymore (which you may think is bad, but really it's not, because we know it's good).

If I follow the argument correctly, it has something to do with ANSI standards: you should not be able to do this in ANSI-standard SQL. So in order for Oracle to be ANSI-standard, they had to stop you from doing this. I think that's the reason.

concept of database

messages13-Feb-12 22:56

messages

13-Feb-12 22:56

Hi everyone,
I have a question about databases.are these correct?
A database is a structured collection of data.
A DBMS is a software package,it creates and management databases,sql server,access and oracle are examples of DBMS.
Is RDBMS a subroutine of DBMS or no?
I want to know what are Flat ,Hierarchical,Relational,Object-oriented databases? they are relative to DBMS or database?
Thanks

Simon_Whale14-Feb-12 0:16

Simon_Whale

14-Feb-12 0:16

RDMS[^]
DBMS[^]
Flat File Databases[^]
Hierarchical Database model[^]
Relational Database[^]
Object Orientated Database[^]

Have a read of the above links from WIKI

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

uspatel17-Feb-12 23:54

uspatel

17-Feb-12 23:54

I like to add with Simon_Whale answer.
See
E. F. Codd Rules
If a dbms follows more than 7 codes rules than it become rdbms.oracle follows 10 rules which is considered as ideal rdbms.no rdbms exist till now which follows all the 12 codes rules.

Quote:
Excel is an example of a DBMS.
and Access,SQL Server ,Oracle are RDBMS.
Every RDBMS is DBMS but reverse is not true.

we categorize dbms and rdbms by E.F. Codd rules.

Eddy Vluggen18-Feb-12 2:52

18-Feb-12 2:52

Last time I checked, there were seven, and most are normalized to BNF. Humor me, introduce me to the five that I don't know.

And no, it's not the database-software that needs to implement the rules, it's the database that needs to be designed according to those rules. I've seen databases in Oracle that would be well in 1NF.

--edit;
You're right on the distinction between DBMS and RDBMS. Codds rules determine whether it's a relational database.

Bastard Programmer from Hell Suspicious | :suss: