How to get one date- out of several rows of data

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get one date- out of several rows of data

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-04-14 : 13:39:55

I have rows of data in a table for each day of the month. This database is switching from Oracle to SQL Server. When in oracle, I used a TRUNC function to get one row for every month like:

(select distinct sample_plot, plot_name,TRUNC(sample_start_date, 'MM') coll_date, TO_CHAR(sample_start_date,'Month'), 
TO_CHAR(sample_start_date,'YYYY'), sample_submit_date, sample_submit_date - (TRUNC(sample_start_date, 'MM') + 75)
from sample_info inner join stnplot_info
on sample_plot = plot_id and....))

Now I am trying to get the same output from the SQL Server database and getting 4/5/6 rows for each month from this query:

(SELECT DISTINCT PLOT_ID, PLOT_NAME, SAMPLE_START_DATE COLL_DATE,DATENAME(MONTH,SAMPLE_START_DATE), DATENAME(YEAR,SAMPLE_START_DATE), SAMPLE_SUBMIT_DATE,DATEDIFF(DAY,DATEADD(DAY,61,SAMPLE_START_DATE), SAMPLE_SUBMIT_DATE) FROM SAMPLE_INFO INNER JOIN STATION_INFO
ON SAMPLE_STATION = STATION_ID AND ...));
Sample Output:
-----------------------------------------------
1028 ABC 2007-03-01 March 2007	2008-09-05 493
1028 ABC 2007-03-07 March 2007	2008-09-05 487
1028 ABC 2007-03-13 March 2007	2008-09-05 481
1028 ABC 2007-03-19 March 2007	2008-09-05 475
1028 ABC 2007-03-25 March 2007	2008-09-05 469
1028 ABC 2007-03-31 March 2007	2008-09-05 463
-----------------------------------------------

Is there any way I can do this? Thanks.

jccorner
Starting Member

20 Posts

Posted - 2010-04-14 : 13:55:27

did you try:

SELECT DISTINCT PLOT_ID, PLOT_NAME, month(SAMPLE_START_DATE)

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-04-14 : 14:23:27

Well JC...month(SAMPLE_START_DATE) will give me the month num(1) instead of the 1st DATE of the month like '01-01-2007' that I want, right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 14:24:55

Check out ROW_NUMBER() in BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-04-14 : 14:41:01

Hi Tara,
sorry...but how do I do that?

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-04-14 : 14:54:00

I found ROW_COUNT() in BOL...but don't know how this helps my problem.
Can you please explain?

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-04-14 : 16:57:53

Its little complicated, but this is what I had to use to find the solution:

CAST(CAST(YEAR(SAMPLE_START_DATE) AS VARCHAR(4)) + '/' + CAST(MONTH(SAMPLE_START_DATE) AS VARCHAR(2)) + '/01' AS DATETIME) COLL_DATE

Instead of using just SAMPLE_START_DATE COLL_DATE. For the subtraction, I had to use:
DATEDIFF(DAY, DATEADD(DAY, 60, CAST(CAST(YEAR(SAMPLE_START_DATE) AS VARCHAR(4)) + '/' +
CAST(MONTH(SAMPLE_START_DATE) AS VARCHAR(2)) + '/01' AS DATETIME))

It works, but I was wondering if there is a simpler solution.
Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources