|
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).
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 
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.
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;
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 
|
|
|
|
|
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! 
I really do appreciate the help. 
|
|
|
|
|
My pleasure, and you're welcome 
|
|
|
|
|
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???
|
|
|
|
|
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).
|
|
|
|
|
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.
|
|
|
|
|
Could you check that you wrote the query correctly. It seems incorrect.
|
|
|
|
|
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. 
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]
|
|
|
|
|
|
Oracle is so great! They even fix bugs which caused such queries to be possible.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
|
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.
|
|
|
|
|
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 
|
|
|
|
|
Thanks I think you undrestood I mean may I ask more questions do you know any programs of Hierarchical,network and flat(data models)
|
|
|
|
|
When designing a program and you want it to store its memory content on a hard disk (database) so that one can restore it's memory at a later time, what is the best way to go about this, do i have to design my own storage API's or there is something available? please need some help.
|
|
|
|
|
This question is so basic and so all encompassing it cannot be answered in a forum post. You need to get a very basic book on computer systems and database. If you don't have the conceptual idea of how a database works then you are not going to understand most of the information on the web, it assumes a reasonable level of knowledge.
Try browsing SQLServerCentral.com, you may find some early instruction articles there.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Persisting your app's state (like Window state, position, etc.) and persisting the data your app is working with (business data like Customer Info, Orders, etc.) are two different things.
For persisting app state, you can use XML, plain text files, etc.
For persisting business data, you can use a database. If the size of the data you're talking about is very small, you can use XML for that too.
|
|
|
|
|
Shameel wrote: If the size of the data you're talking about is very small, you can use XML for that too.
Hey i thought XML was a standard, OpenCV 2.2 uses mostly XML files for storage, so i was actually considering XML, I want to know why i should not use XML for all storage purposes?XML is cool & easy to work with
I actually want to increase the database size of an image indexing software that indexes images based on content for a Content-based Image Retrieval System i have developed so that a huge image database can be indexed, i have been using OpenCV 2.2 storage system for now, I don't know if you have used OpenCV 2.2 storage system, if you have,is it wise to keep using OpenCV 2.2 storage system even for a large data set?
|
|
|
|
|
Yes, XML is a standard and can be helpful for storing relatively small data without the overhead of using a database. But if the size of the data is very huge, performance will suffer. A properly designed RDBMS database will be really fast even if the data is very huge.
|
|
|
|
|
Thanks for the answers,i really appreciate that.
|
|
|
|
|
As a caveat on Shameels comment, if you have a small number of records and each record is large (image file) then xml may be valid. You imply you have a huge image set to manage. You really should look into strategies of image storage there are quite a number of options.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
BupeChombaDerrick wrote: store its memory content on a hard disk
I recommend writing it to a binary file.
|
|
|
|
|
Hi,
we are having a problem with an SQL Server 2005.
We are running multiple scripts to send emails on the server but from time to time the server stops sending emails and we have to do a restart to ensure functionalitsy again. We have already taken care to close all opened connections in the scripts.
Any thoughts?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
