What I am trying to do is to get balances at each month-end from Jan to
Dec 2004. Now I am doing it by manually changing the date for each
month, but I want to do all the months at one time. Is there a way to
add something like a do loop to achieve that goal? Please see my query
below. Thanks so much!
declare @month_date_b smalldatetime
--B month beginning date
declare @month_date_e smalldatetime
--E month ending date
select @month_date_b='9/1/2004'
select @month_date_e='9/30/2004'
select a.person_id, a.fn_accno, a.fn_bal, b.mm_open
from fn_mm_fnbal as a
join fn_mm_list as b
on a.person_id=b.person_id
and b.mm_open < @month_date_e
where a.bal_date between @month_date_b and @month_date_e
group by a.person_id, a.fn_accno, a.fn_bal, b.mm_open
order by a.fn_accno, a.fn_bal 2 3524
Rather than a loop, you can use a join to a set of dates. Or you could
just query the dates out of a Calendar table if you have one. Calendar
tables are very useful for this sort of thing:
SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
FROM fn_mm_fnbal AS A
JOIN fn_mm_list AS B
ON A.person_id = B.person_id
JOIN
(SELECT CAST('20040101' AS DATETIME) UNION ALL
SELECT '20040201' UNION ALL
SELECT '20040301' UNION ALL
SELECT '20040401' UNION ALL
SELECT '20040501' UNION ALL
SELECT '20040601' UNION ALL
SELECT '20040701' UNION ALL
SELECT '20040801' UNION ALL
SELECT '20040901' UNION ALL
SELECT '20041001' UNION ALL
SELECT '20041101' UNION ALL
SELECT '20041201') AS C(cal_date)
ON A.bal_date >= C.cal_date
AND A.bal_date < DATEADD(M,1,C.cal_date)
AND B.mm_open < DATEADD(M,1,C.cal_date)
GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
ORDER BY A.fn_accno, A.fn_bal
--
David Portas
SQL Server MVP
--
Thanks David! It is a great idea, I forgot to use my calendar table.
David Portas wrote: Rather than a loop, you can use a join to a set of dates. Or you
could just query the dates out of a Calendar table if you have one.
Calendar tables are very useful for this sort of thing:
SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open FROM fn_mm_fnbal AS A JOIN fn_mm_list AS B ON A.person_id = B.person_id JOIN (SELECT CAST('20040101' AS DATETIME) UNION ALL SELECT '20040201' UNION ALL SELECT '20040301' UNION ALL SELECT '20040401' UNION ALL SELECT '20040501' UNION ALL SELECT '20040601' UNION ALL SELECT '20040701' UNION ALL SELECT '20040801' UNION ALL SELECT '20040901' UNION ALL SELECT '20041001' UNION ALL SELECT '20041101' UNION ALL SELECT '20041201') AS C(cal_date) ON A.bal_date >= C.cal_date AND A.bal_date < DATEADD(M,1,C.cal_date) AND B.mm_open < DATEADD(M,1,C.cal_date) GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open ORDER BY A.fn_accno, A.fn_bal
-- David Portas SQL Server MVP -- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ravi |
last post by:
Hi All,
I am trying to execute a select statement using the DBI module
of perl in a for loop. I am getting a strange behaviour, the select
statement is excuting correctly only for the last element...
|
by: Aggelos |
last post by:
Hi Guys!!!
Thanks in advance for any kind of help about this problem.
The problem:
Acces Database
One Table 'Orders' and second 'OrderItems'
I want to export in txt file using FSO and...
|
by: ben |
last post by:
i have a bit of code, that works absolutely fine as is, but seems over
complicated/long winded. is there anyway to shorten/simplify it?
the code is below. description of it: it's like strcpy in...
|
by: hufaunder |
last post by:
I have 16-bit data that I want to display. In order to display it I
compress a certain range of the input data into 8 bit (I need control
over this). All seems to work ok except that it is dead...
|
by: cberthu |
last post by:
I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
So i found a way to get this...
|
by: fig000 |
last post by:
HI,
I'm new to generics. I've written a simple class to which I'm
passing a generic list. I'm able to pass the list and even pass the
type of the list so I can use it to traverse it. It's a...
|
by: yeohyc |
last post by:
Hi All,
I have a problem here. I have done a request for move order it was stored into a custom table with the format:
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500...
|
by: saytri |
last post by:
I have a problem with this code. I'm, doing a quiz, and the questions are stored in a binary file. My problem is how am i going to compare the answers entered by the user to the correct answers. The...
|
by: malkarouri |
last post by:
Hi everyone,
I have an algorithm in which I need to use a loop over a queue on
which I push values within the loop, sort of:
while not(q.empty()):
x = q.get()
#process x to get zero or more...
|
by: Alex Snast |
last post by:
Hello
I'm new to python and i can't figure out how to write a reverse for
loop in python
e.g. the python equivalent to the c++ loop
for (i = 10; i >= 0; --i)
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |