How to apply For loop here

Problem Description:

I have 5 fields in my report.
4 string type and 1 Date.
4 String type are : Unit , Periods , Grade and Demand
and 1 is Date

I would like to apply a loop which eventually sum up the value of the last field.
Before I explain it furthur I would like to add that all the string type has
multiple value in it.

Description :

I would like sum up Demand for each Grade per period per Unit.

For Example

Unit 1
Date ( monthly)1
Grade 1
Period 1 Total Demand
Period 2 Total Demand
Period 3 Total Demand

Date2
Grade 2
Period 1 Total Demand
Period 2 Total Demand
Period 3 Total Demand

I think this involves couple of For loops , Sorry but not familier with
Syntax that much.

Thanks

AbhisarDo you want to sum the field Demand which is of String type?|||Yes ,
I want to sum field demand. of string type.
so it looks like.

For loop unit ...select one unit in that string array.
then Start for loop to select the date from the Date array ( which in monthly format)
Then for loop to select the grade (string array)....
and then for each period( 3 in it ) it will show me total demand..

The actual data looks like...

Unit date grade period demand
------------------
ew 333 rrr Day 44
ew 333 rrr Eve 55
ew 333 rrr Night 22
...
...
...
ew 645 rrr Day 43
ew 645 rrr Eve 33
ew 645 rrr Night 21

Like wise
diff date .....

I would like to see

for
unit (ew) date(333) for grade(rrr)
on period (Day) total demand
(Eve) total demand
(Night) Total demand

because there are lot of person works on the same date, same unit, same grade
same three period but different demand.
I want to sum up all the demand and write a summary.

I hope I am able to explain my problem to all of u this time.

Thanks|||You can try this in the formula

numbervar eveCount;
numbervar DayCount;
whilePringtingRecords;
if {period}="Day"
DayCount:=DayCount+tonumber({Demand})
else if {period}="Eve"
EveCount:=EveCount+tonumber({Demand});|||Sorry Guys bot not working at all.

Ok , Let me reframe it to one more example.

there are 5 fields , the values in 4 of the fields are not changing at all , only in the 5th field is changing.
For example

1st field 2nd Field 3rd Field 4rth Field 5th Field

xxx rrrr tttt cccc 33
xxx rrrr tttt cccc 44
xxx rrrr tttt cccc 55

I just want to see

xxx rrrr tttt cccc 132 (total)

just in one line.

any ideas now.

Thanks|||If so, then why dont you try to write a query

Select field1,field2,field3,field4,sum(convert(numeric,field5)) from table group by
field1,field2,field3,field4
and design the report using this query

how to add more than one recored to sql table in the same time

i want to add more than one row in the same time i tried to use for loop but didnt work any ideas please

Please post exactly what you want to do with table names.

|||

Can you show us your code and maybe someone can help you figure out why its not working

How to add a do loop in sql? Thanks a lot!

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_balRather 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
> --

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing