Want to avoid cursors ... need help with query

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

Want to avoid cursors ... need help with query

Author

Topic

LaurieCox

158 Posts

Posted - 2006-10-30 : 09:16:46

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)

17689 Posts

Posted - 2006-10-30 : 09:26:44

[code]
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
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 09:29:15

[code]2529 2006-01-13 00:00:00.000 2006-04-11 23:59:00.000 44 32[/code]

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

LaurieCox

158 Posts

Posted - 2006-10-30 : 09:36:38

Hi khtan,

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

Laurie

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-30 : 11:37:19

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 11:42:19

I know... I were paralyzed just until a few seconds ago!

Peter Larsson
Helsingborg, Sweden

YUVRAJ
Starting Member

1 Post

Posted - 2008-04-30 : 08:17:59

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 08:20:51

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 09:00:57

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

LTack
Posting Yak Master

193 Posts

Posted - 2008-05-16 : 17:40:08

You guys are just...hilarious... :P

saeangvamia
Starting Member

1 Post

Posted - 2009-04-08 : 09:14:53

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 09:31:20

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

sravani solasa
Starting Member

6 Posts

Posted - 2011-06-24 : 15:43:42

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-24 : 15:48:51

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

Rajni
Starting Member

1 Post

Posted - 2011-11-30 : 03:56:10

Hi All,

How to join 2 tables from different databases.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-30 : 06:13:27

Hi Rajni,