| Author |
 Topic  |
|
|
LaurieCox
Posting Yak Master
USA
125 Posts |
 Posted - 10/30/2006 : 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)
Singapore
16746 Posts |
Posted - 10/30/2006 : 09:26:44
|
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
|
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/30/2006 : 09:29:15
|
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
|
|
|
|
LaurieCox
Posting Yak Master
USA
125 Posts |
Posted - 10/30/2006 : 09:36:38
|
Hi khtan,
Thank you! The 44 -- 32 thing was some bizarre typo on my part.
Laurie |
|
|
|
samuelclay
Yak Posting Veteran
USA
71 Posts |
Posted - 10/30/2006 : 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 11:42:19
|
I know... I were paralyzed just until a few seconds ago!
Peter Larsson
Helsingborg, Sweden |
|
|
|
YUVRAJ
Starting Member
1 Posts |
Posted - 04/30/2008 : 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
India
47099 Posts |
Posted - 04/30/2008 : 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 |
|
|
LTack
Posting Yak Master
USA
193 Posts |
Posted - 05/16/2008 : 17:40:08
|
| You guys are just...hilarious... :P |
|
|
|
saeangvamia
Starting Member
1 Posts |
Posted - 04/08/2009 : 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
India
22461 Posts |
Posted - 04/08/2009 : 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
India
6 Posts |
Posted - 06/24/2011 : 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
USA
15559 Posts |
Posted - 06/24/2011 : 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 Posts |
Posted - 11/30/2011 : 03:56:10
|
Hi All,
How to join 2 tables from different databases. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/30/2011 : 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
Time is always against us
|
|
|
| |
Topic |
|
Want to avoid cursors ... need help with query
Topic Locked
