SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Want to avoid cursors ... need help with query
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 10/30/2006 :  09:16:46  Show Profile
Hi,

I have a customer who is using vba to pull a result set from an sql server stored procedure into excel. She wants a calculated column added to the result set that gives:
The number days (datediff) between the end date (autend_dte) on one row and the begin date (autbeg_dte) on the next row for each client (clt_num). The rows are to be ordered by client and begin date. The number should be associated with the second row used to calculate the date diff. The first row for each client will have a date diff of 0.

I could do this using a cursor in the stored procedure or a loop in the vba, but I would prefer to do it with the select, but I don't even know where to start.

See expected results below.


CREATE TABLE #testit (
	clt_num int NOT NULL ,
	autbeg_dte datetime NULL ,
	autend_dte datetime NULL)
 
INSERT INTO #testit (clt_num, autbeg_dte, autend_dte)
SELECT 510, '2004-09-01 00:00:00.000', '2005-09-30 23:59:00.000' UNION ALL
SELECT 510, '2005-10-01 00:00:00.000', '2006-04-06 23:59:00.000' UNION ALL
SELECT 600,  '2006-08-01 00:00:00.000', '2006-11-06 23:59:00.000' UNION ALL
SELECT 2529, '2006-01-13 00:00:00.000', '2006-04-11 23:59:00.000' UNION ALL
SELECT 2529, '2005-11-30 00:00:00.000', '2005-12-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-03-29 00:00:00.000', '2006-05-02 23:59:00.000' UNION ALL
SELECT 2602, '2005-11-12 00:00:00.000', '2006-02-27 23:59:00.000' UNION ALL
SELECT 2602, '2006-05-26 00:00:00.000', '2006-06-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-06-18 00:00:00.000', '2006-06-28 23:59:00.000'

SELECT * FROM #testit
order by clt_num,autbeg_dte

Expected result:

clt_num	autbeg_dte	        autend_dte	        Days Diff
510	2004-09-01 00:00:00.000	2005-09-30 23:59:00.000	0	
510	2005-10-01 00:00:00.000	2006-04-06 23:59:00.000	1
600	2006-08-01 00:00:00.000 2006-11-06 23:59:00.000 0
2529	2005-11-30 00:00:00.000	2005-12-12 23:59:00.000	0	
2529	2006-01-13 00:00:00.000	2006-04-11 23:59:00.000	44
2602	2005-11-12 00:00:00.000	2006-02-27 23:59:00.000	0	
2602	2006-03-29 00:00:00.000	2006-05-02 23:59:00.000	30
2602	2006-05-26 00:00:00.000	2006-06-12 23:59:00.000	24
2602	2006-06-18 00:00:00.000	2006-06-28 23:59:00.000	6


Thanks,

Laurie

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 10/30/2006 :  09:26:44  Show Profile

SELECT 	t1.clt_num, t1.autbeg_dte, t1.autend_dte, 
	isnull(datediff(day, t2.autend_dte, t1.autbeg_dte), 0)
FROM 	#testit t1 left join #testit t2
	on	t1.clt_num	= t2.clt_num
	and	t2.autbeg_dte	= (select max(autbeg_dte) from #testit x where x.clt_num = t2.clt_num and x.autbeg_dte < t1.autbeg_dte)
order by t1.clt_num, t1.autbeg_dte



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 10/30/2006 :  09:29:15  Show Profile
2529	2006-01-13 00:00:00.000	2006-04-11 23:59:00.000	44 32


datediff(day, '2005-12-12', '2006-01-13') = 32


KH

Go to Top of Page

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 10/30/2006 :  09:36:38  Show Profile
Hi khtan,

Thank you! The 44 -- 32 thing was some bizarre typo on my part.

Laurie
Go to Top of Page

samuelclay
Yak Posting Veteran

USA
71 Posts

Posted - 10/30/2006 :  11:37:19  Show Profile  Visit samuelclay's Homepage
Wow, data structure, sample data and expected results..... why don't the people with the more difficult questions provide that
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 10/30/2006 :  11:42:19  Show Profile  Visit SwePeso's Homepage
I know... I were paralyzed just until a few seconds ago!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YUVRAJ
Starting Member

1 Posts

Posted - 04/30/2008 :  08:17:59  Show Profile
I have records in a table with field name StudentName contains last name, first name, middle name in one field. want to split it in a first name, last name, middle name and stored it as separate in another table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/30/2008 :  08:20:51  Show Profile
quote:
Originally posted by YUVRAJ

I have records in a table with field name StudentName contains last name, first name, middle name in one field. want to split it in a first name, last name, middle name and stored it as separate in another table


Please post this as a new thread
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/30/2008 :  09:00:57  Show Profile
quote:
Originally posted by samuelclay

Wow, data structure, sample data and expected results..... why don't the people with the more difficult questions provide that



I guess s/he read my blog



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

LTack
Posting Yak Master

USA
193 Posts

Posted - 05/16/2008 :  17:40:08  Show Profile
You guys are just...hilarious... :P
Go to Top of Page

saeangvamia
Starting Member

1 Posts

Posted - 04/08/2009 :  09:14:53  Show Profile
Hello,

I'm using Epicor Enterprise and I need to change a database name in SQL 2000 and was thinking if doing a backup, droping the database then restoring the backup with the different name is all that I need to do?

Would I need to run a script of some kind to change additional required areas in the database(s).

Epicor Enterprise uses a Control database and I think I also need to make changes here to point to the new database name.

Any help I can get would be greatly appriciated.

Thanks,

saeangvamia
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/08/2009 :  09:31:20  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by saeangvamia

Hello,

I'm using Epicor Enterprise and I need to change a database name in SQL 2000 and was thinking if doing a backup, droping the database then restoring the backup with the different name is all that I need to do?

Would I need to run a script of some kind to change additional required areas in the database(s).

Epicor Enterprise uses a Control database and I think I also need to make changes here to point to the new database name.

Any help I can get would be greatly appriciated.

Thanks,

saeangvamia


Post your question as a new topic

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sravani solasa
Starting Member

India
6 Posts

Posted - 06/24/2011 :  15:43:42  Show Profile
I have a problem with this query could you please help me.

The firm has a few outlets that receive items for recycling. Each of the outlets receives funds to be paid to deliverers. Information on received funds is registered in a table:
Income_o(point, date, inc)
The primary key is (point, date), thus receiption of money (inc) takes place not more than once a day (date column does not include time component of the date). Information on payments to deliverers is registered in the table:
Outcome_o(point, date, out)
In this table the primary key (point, date) also ensures bookkeeping of the funds distribution at each point not more than once a day.
In case incomes and expenses may occur more than once a day, another database schema is used. Corresponding tables include code column as primary key:
Income(code, point, date, inc)
Outcome(code, point, date, out)
In this schema date column does not also include the day time.

Under the assumption that the income (inc) and expenses (out) of the money at each outlet are written not more than once a day, get a result set with the fields: point, date, income, expense.
Use Income_o and Outcome_o tables.


Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 06/24/2011 :  15:48:51  Show Profile  Visit robvolk's Homepage
Hate to sound like a broken record (what's that?) but please post your question as a new topic.
Go to Top of Page

Rajni
Starting Member

1 Posts

Posted - 11/30/2011 :  03:56:10  Show Profile
Hi All,

How to join 2 tables from different databases.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 11/30/2011 :  06:13:27  Show Profile
Hi Rajni,

quote:
Originally posted by robvolk

Hate to sound like a broken record (what's that?) but please post your question as a new topic.



me too. So i am quoting rob


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000