Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql

Refreshing

I have report that has a grouping with a +/- for one of the groups to expand. Everytime I expand or collapse the group it seems the page refreshes and resizes. Is there a way to open an close without posting back or at least giving the appearance that the form posts back?

Thanks,

Hi Raj,

I have the same question as yours. Can you tell me your current way to work on +/- by posting back?

Many thanks,
cukhoai|||

I am not Raj, so I am not sure who you are referring to.

Right now I just group one of the items and allow another field to control the toggling. I am still looking for a way to eliminate the refresh or make it so there is no resizing.

Referential Integrity practices for complex Database

I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>|||bluefish wrote:
> I am a database Engineer for a company that has relatively large and complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity? If
> so how do you deal with the heightened complexity of back end updates when
> necessary?
The purpose of referential integrity is to implement business rules and
ensure that your database accurately and consistently models the real
world. The fact that your engineers have to ask this question suggests
that either they don't understand your business or that they don't care
about data integrity.
> On the other hand if you did not, how do we enforce accuracy?
Better ask the people who designed your systems without any
integrity...
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Referential Integrity practices for complex Database

I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>sql

referencing fields in code

I'd like the user to decide how to group the report. For this I have the
parameter KStar and a piece of custom code.
Under 'Edit group' I use the expression:
=Code.GetGroups(Parameters!KStar.Value )
In the code window I have a function that starts like this:
Public Shared Function GetGroups (Byref KStar As integer)
Select (KStar)
Case 1
Return(Fields!K1.Value)
Case 2
Return(Fields!K2.Value)
It gives me the error:
[BC30469] Reference to a non-shared member requires an object reference.
How do I solve this? I am obviously very new to Reporting Services and I
would appreciate any help.
Thanks!First try replacing Fields!KStar.Value by Report.Fields!KStar.Value, if that
does not work you will have to pass the field values in as a parameter
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maple" <maple@.discussions.microsoft.com> wrote in message
news:E87A2353-646F-4FA9-AA2D-367BCB91348D@.microsoft.com...
> I'd like the user to decide how to group the report. For this I have the
> parameter KStar and a piece of custom code.
> Under 'Edit group' I use the expression:
> =Code.GetGroups(Parameters!KStar.Value )
> In the code window I have a function that starts like this:
> Public Shared Function GetGroups (Byref KStar As integer)
> Select (KStar)
> Case 1
> Return(Fields!K1.Value)
> Case 2
> Return(Fields!K2.Value)
> It gives me the error:
> [BC30469] Reference to a non-shared member requires an object reference.
> How do I solve this? I am obviously very new to Reporting Services and I
> would appreciate any help.
> Thanks!
>

Referencing a total for a group inside that group?

Is there any way to do this' I am trying to display percentages of the total
along with the number of items. What I want to do would look something like
this.
Group header:
Detail X X/Sum of all X's
Group footer Sum of all X's
I can't seem to find a way to refer to the item in the footer inside the
group. Can this be done? If so how?
Thanks in advance.I found it!
What I had to do was:
Group header:
Detail X X/Sum(X,"Groupname")
Group footer Sum of all X's
In case anyone has the same problem.
"Doug" wrote:
> Is there any way to do this' I am trying to display percentages of the total
> along with the number of items. What I want to do would look something like
> this.
> Group header:
> Detail X X/Sum of all X's
> Group footer Sum of all X's
> I can't seem to find a way to refer to the item in the footer inside the
> group. Can this be done? If so how?
> Thanks in advance.
>

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>

Re-Execution of For each Loop Container

I have a For each loop container and inside the same a group of text files are loading to a table using bulk insert task.If there are duplicate files i dont wish to load it into the table. Duplicate check can be done by script task but how do i skip the files and repeat the for loop for the next files.

That means i want to skip some files and load the other files in a for loop.
How do i do that.

Can anybody please help me.

So here is what you do

Loop container has inside a Script task that checks for a duplicate file

and a BULK INSERT task that does what it says on the tin.

The script task and the BI task are joined by workflow.

Declare a variable say something like FileAlreadyThere of type DT_BOOL

(boolean)

In the script task if the file is already there set the value of this

variable to TRUE and if not then FALSE.

The workflow now between the two tasks should be based on an expression

as well as outcome so have a look at this article and you want to set

your expression to something like

@.FileAlreadyThere == FALSE

http://wiki.sqlis.com/default.aspx/SQLISWiki/LogicalOrExample.html

Allan

"Raj Amb@.discussions.microsoft.com"

wrote in message

news:1b962d72-8a6d-4b3c-bdab-e86bfda3270b@.discussions.microsoft.com:

> I have a For each loop container and inside the same a group of text

> files are loading to a table using bulk insert task.If there are

> duplicate files i dont wish to load it into the table. Duplicate check

> can be done by script task but how do i skip the files and repeat the

> for loop for the next files.

>

> That means i want to skip some files and load the other files in a for

> loop.

> How do i do that.

>

> Can anybody please help me.|||Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.

but i want to continue/iterate the for loop till the last file , how do i do that.|||

Raj Amb wrote:

Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.

but i want to continue/iterate the for loop till the last file , how do i do that.

This is controlled by two properties:
1) FailPackageOnFailure on each task or container - set it to false to ignore errors.
2) Also, each task and container have MaxErrorCount - increase it to desired value.