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]


KH

Go to Top of Page

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


KH

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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



Go to Top of Page

LTack
Posting Yak Master

193 Posts

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

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
Go to Top of Page

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
Go to Top of Page

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.


Go to Top of Page

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.
Go to Top of Page

Rajni
Starting Member

1 Post

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

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-30 : 06:13:27
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -