Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rahamanf
Starting Member
15 Posts |
 Posted - 2008-05-13 : 13:16:16
|
| I have a Oracle table with columns like:zone, plot, collection_start_date, collection_end_date, submit_date etc.Each zone has multiple plots. Data collection is monthly but the start date can vary, depending on some factors.For each zone and plot, there are multiple entries for the same month.Here's what I need to do -1) I need to comeup with one row for each zone, plot, month combination.2) THe row should have the zone, plot, TO_CHAR(collection_start_date, 'MM') MONTH, TO_CHAR(collection_start_date, 'YYYY') YEAR, submit date, The difference between the submit_date and collection_start_dateThe problem is multiple rows for the same month for a zone, plot and the collection_start_date varies for each row and i need to get the row based on this varied date field.Any idea?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-13 : 13:17:01
|
| You should post your question in an Oracle forum. This site is for Microsoft SQL Server.There's an Oracle forum over at dbforums.com.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
rahamanf
Starting Member
15 Posts |
Posted - 2008-05-13 : 13:34:14
|
| I'd be greatly helped if I get the SQL Server SQL (Tsql) for my problem.Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 13:41:46
|
| If you want one row per zone, what should you display as submitdate & other fields? What are your business rules to determine them. When we group several rows for zone we will have different values for submit_date. |
|
|
|
rahamanf
Starting Member
15 Posts |
Posted - 2008-05-13 : 14:06:24
|
| I want distinct zone, plot and submit_date. This part itself is not a problem because remember - they are for the same month, and therefore, they have the same submit_date (all the data submitted as a single file for a zone,plot on monthly basis).The problem is - the collection_start_date are different for the rows - even though they are for the same zone, plot, and have the same submit_date. Thats why we have multiple rows. Now I want to see if data is submitted for a zone/plot for a month. I need to use the collection_start_date because it's month value is the month for which data is submitted. So, I need to get a single collection_start_date value for my distinct zone, plot and submit_date.See the problem?Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 14:09:17
|
| [code]select DISTINCT zone, plot, MONTH(collection_start_date) as Month, YEAR(collection_start_date) as Year, submit_date from YourTable[/code] |
|
|
|
rahamanf
Starting Member
15 Posts |
Posted - 2008-05-13 : 16:08:36
|
| Hi Visakh,Thanks for your idea.Here're more clear explanation of what I'm trying: 1) A table contains columns like - zone, plot, collection_start_date, submit_date etc. Data is submitted for each month as a single file that is loaded in the database, however, one zone/plot combination can have multiple records in the table each of which has different collection_start_date.2) I want to create a table from the above that will have zone, plot, report_month(same month as collection_start_date's month), submit_date, ontime (yes/no or 1/0). Goal: to see whether data submitted was on time or not. 2) I want to create another table (Summary for zones). This table should have the following columns - zone, num_submitted_ontime, num_late, num_not_submitted.Definition of ontime: 45 days from the 1st day of the collection_start_date's month Any idea?Thanks. |
|
|
|
|
|
|
|
|
Getting one row from multiple rows