What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !
Here is my fonction:
DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;I don't quite understand your problem. The CASE statement works OK:
SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;
P T
- -
t P
f A
-
x -|||Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:
SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;
P T
- -
t P
f A
-
x -
If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Help with Select Minimum Value
I need help in doing a select statment with a minimum
here is the statement
CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)
I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
I get 2 values
ProtScr ProtRank
1 3
1 5
if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. MiyasatoHi
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Thanks
> Stephen K. Miyasato
>|||Thanks that helped
I get
protScr ProtRank
1 3
The subquery works but I could not get the main query to work
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here
( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. Miyasato
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8bbKynTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Hi
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>
here is the statement
CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)
I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
I get 2 values
ProtScr ProtRank
1 3
1 5
if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. MiyasatoHi
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Thanks
> Stephen K. Miyasato
>|||Thanks that helped
I get
protScr ProtRank
1 3
The subquery works but I could not get the main query to work
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here
( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. Miyasato
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8bbKynTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Hi
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>
HELP with Running value totals
HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
Wednesday, March 21, 2012
Help with Report Filter on bit field
I have this filter for my report table:
expression operator Value
=Cstr(Fields!work.Value) = 'True'
My report's table isn't returning data, but in preview but if I run the dataset, there is clearly some valid records tha contain 'true' for the field Work. The field work in my SQL Server table is type bit
additional screenshot here: http:\\www.webfound.net\no_data_filter_on_bit_field.jpg
I would try the following filter:
Expression:
=CBool(Fields!work.Value)
Operator:
=
Value:
=True
Note that the filter value is an expression (=True).
-- Robert
|||Robert, thanks very much, it works. Now let me ask you this. I had a total textbox that just did a COUNT(number). But I need to do a COUNT on number if CBool(Fields!work.Value) = True. I was wondering how to form an if statement behind my text field to do this. number is just the identity field in which I can count on.
|||I tried this but it's malformed:
=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")) == True, COUNT(Fields!number.Value, "Mismatch_Data"), 0)
|||Do you really want to make the decision for the count based on the first data row value of the "home" field? If yes, then this expression should work (note - since RDL expressions are VB.NET based the comparison only needs one '='; in this particular case you can also omit it):
=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")), COUNT(Fields!number.Value, "Mismatch_Data"), 0)
Also, are you really looking for the Count or for a Sum aggregate?
If you want to sum individual rows based the value of the "home" field in that particular row (rather than just looking at the first row), you would use conditional aggregation and the following expression would need to be put e.g. into a table header/footer bound to the Mismatch_Data dataset):
=Sum( iif(CBool(Fields!home.Value), Fields!number.Value, 0)
-- Robert
|||Thanks, Robert. I was looking for the count of how many records were found. I have 2 tables....so I needed a count of records using the number field which was a unique field.
That should work...
Help with Recursive Function
I am writing a function which I hope to use as a column value in a
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.
When I test the funciton, it says 'commands completed successfully',
but nothing is returned. This is in SQL2000, runing on Windows2000.
The table the function acts on is:
CREATE TABLE [dbo].[tblbenthictaxa] (
[tsn] [int] IDENTITY (1, 1) NOT NULL ,
[rank_id] [int] NOT NULL ,
[dir_parent_tsn] [int] NULL ,
[req_parent_tsn] [int] NOT NULL ,
[taxa_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ReqParentTSN is the recursive link to rows in the table;
Level is the taxonomic level the user requested (an integer
representing Order, Family, Genus or Species).
CREATE FUNCTION dbo.CBN_RecursTaxa (
@.ReqParentTSN int,
@.Level int
)
RETURNS varchar(100) AS
BEGIN
Declare @.Rank int,
@.taxaname varchar(100)
SELECT @.ReqParentTSN = tblbenthictaxa.req_parent_tsn,
@.TaxaName = tblbenthictaxa.taxa_name,
@.Rank = tblbenthictaxa.rank_id
FROM tblbenthictaxa
WHERE tblbenthictaxa.TSN=@.ReqParentTSN
if @.Rank > @.Level
**1 --exec CBN_RecursTaxa @.ReqParentTSN, @.Level
RETURN @.TaxaName
END
Thanks in advance for any help,
TimTim Pascoe (tim.pascoe@.cciw.ca) writes:
> I am writing a function which I hope to use as a column value in a
> select query. The function recursively walks a taxonomic heirarchy,
> extracting the name for an organism at the taxonomic level requested
> by the user. I'm having trouble figuring out the syntax to call the
> function from itself (see **1), and the value returned.
> When I test the funciton, it says 'commands completed successfully',
> but nothing is returned. This is in SQL2000, runing on Windows2000.
> The table the function acts on is:
There are two ways to run a scalar UDF, and I don't know which you are
using. But I think this example, gives you the answer to both of your
questions:
CREATE FUNCTION nisse (@.i int) returns varchar(200) AS
BEGIN
DECLARE @.ret varchar(200),
@.tmp varchar(200)
SELECT @.i = @.i - 1, @.ret = 'nisse '
IF @.i > 0
BEGIN
EXEC @.tmp = dbo.nisse @.i
SELECT @.ret = @.tmp + @.ret
END
RETURN @.ret
END
go
SELECT dbo.nisse(4)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
The example was perfect. I knew it was something small, but the simple
things are sometimes the hardest to track down when you are learning.
The function works perfectly, and is much faster than the original ASP
script approach I had.
Thanks again,
Tim
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Help with reading datetime with DATEPART
I was hoping someone could help me with the sql syntax in trying to return the date from a datetime value. I'm trying to get the month and day and year from a datetime value in the database but I keep getting a token error. This is the code I'm using to try to read the date, from everything I've read for sql, it should work but it doesn't.
Thanks.
Dim sql As String = "SELECT * FROM People WHERE DATEPART(month, dtime) = '" & _
DateTime.Month & "' & DATEPART(year, dtime) = '" & DateTime.Year & '"
Dim Sqlreader As SqlCeDataReader = cmd.ExecuteReader
The error I get is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 82,Token in error = = ]
It doesn't seem to recognize the second DATEPART search and the = sign is a syntax error.
What am I doing wrong here?
in the "& DATEPART(year, dtime)" part, replace "&" with "and"
regards
|||Hmmm, I tried the '&' symbol and also tried 'AND' but not a lowercase 'and'.Thanks.
crt
Monday, March 19, 2012
Help with query - selecting min value
I have a table (wfm_astat) that looks like this:
auftrag | af
---+--
1311703 | 30
1311703 | 40
1400065 | 20
1400065 | 30
1400065 | 40
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af
---+--
1311703 | 30
1400065 | 20
Can anybody give me some ideas on how to select the record with the smallest 'af' value??
Any help would be appreciated.
Thanks, stephen.In case anybody else is looking for an answer to this problem, here it is:
SELECT wfm_astat.auftrag, wfm_astat.af
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
AND wfm_astat.af = (
SELECT min(af)
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag)|||Self-answering : good job !
This would also work :
SELECT wfm_astat.auftrag, min(wfm_astat.af)
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag;
Regards,
RBARAER|||I have a table (wfm_astat) that looks like this:
auftrag | af | abt |
---+--+--+
1311703 | 30 | D00 |
1311703 | 40 | F00 |
1400065 | 20 | C12 |
1400065 | 30 | C23 |
1400065 | 40 | F00 |
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af | abt
---+--+--
1311703 | 30 | D00
1400065 | 20 | C12
The query from RBARAER worked fine when I only had the two columns in the wfm_astat table, but now that there is the extra 'abt' field, I no longer get only the minimum 'af' value per 'auftrag'. Here is my query that returns the wrong results:
SELECT wfm_astat.auftrag, min(wfm_astat.af), wfm_astat.abt
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag, wfm_astat.abt;
Can anybody tell me what I am doing wrong here?|||Try removing wfm_astat.abt from the group by clause.|||urquel, that would require removing it from the SELECT list as well (so as not to cause a syntax error), but then the query would no longer return the right number of columns
steve, try this:select wfm_astat.auftrag
, wfm_astat.af
, wfm_astat.abt
from wfm_astat as x
inner
join wfm_auftrag
on wfm_astat.auftrag
= wfm_auftrag.auftrag
where wfm_astat.af
= ( select min(af)
from wfm_astat
where auftrag = x.auftrag )
auftrag | af
---+--
1311703 | 30
1311703 | 40
1400065 | 20
1400065 | 30
1400065 | 40
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af
---+--
1311703 | 30
1400065 | 20
Can anybody give me some ideas on how to select the record with the smallest 'af' value??
Any help would be appreciated.
Thanks, stephen.In case anybody else is looking for an answer to this problem, here it is:
SELECT wfm_astat.auftrag, wfm_astat.af
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
AND wfm_astat.af = (
SELECT min(af)
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag)|||Self-answering : good job !
This would also work :
SELECT wfm_astat.auftrag, min(wfm_astat.af)
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag;
Regards,
RBARAER|||I have a table (wfm_astat) that looks like this:
auftrag | af | abt |
---+--+--+
1311703 | 30 | D00 |
1311703 | 40 | F00 |
1400065 | 20 | C12 |
1400065 | 30 | C23 |
1400065 | 40 | F00 |
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af | abt
---+--+--
1311703 | 30 | D00
1400065 | 20 | C12
The query from RBARAER worked fine when I only had the two columns in the wfm_astat table, but now that there is the extra 'abt' field, I no longer get only the minimum 'af' value per 'auftrag'. Here is my query that returns the wrong results:
SELECT wfm_astat.auftrag, min(wfm_astat.af), wfm_astat.abt
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag, wfm_astat.abt;
Can anybody tell me what I am doing wrong here?|||Try removing wfm_astat.abt from the group by clause.|||urquel, that would require removing it from the SELECT list as well (so as not to cause a syntax error), but then the query would no longer return the right number of columns
steve, try this:select wfm_astat.auftrag
, wfm_astat.af
, wfm_astat.abt
from wfm_astat as x
inner
join wfm_auftrag
on wfm_astat.auftrag
= wfm_auftrag.auftrag
where wfm_astat.af
= ( select min(af)
from wfm_astat
where auftrag = x.auftrag )
Wednesday, March 7, 2012
Help with one-to-many relation join
I have two tables defined below. I would like to join them but choose
only the record from tblb which has the highest value of BID.
I know the following is wrong and I need help. Thanks, larzeb.
SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
FROM tbla A
JOIN tblb B
ON A.AID = B.AID
GROUP BY A.AID
CREATE TABLE tbla (
AID int IDENTITY(1,1) NOT NULL ,
A char (10),
B char (10),
C char (10),
D char (10),
CONSTRAINT PK_ID PRIMARY KEY (AID)
)
CREATE TABLE tblb (
BID int IDENTITY(1, 1) NOT NULL ,
AID int NOT NULL ,
Y char (10),
Z char (10) ,
CONSTRAINT PK_B PRIMARY KEY (BID),
CONSTRAINT FK_B_A FOREIGN KEY (AID)
REFERENCES tbla (AID)
)Here are a few possibilities:
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where not exists (
select * from tblb as B2
where B2.AID = B.AID
and B2.BID < B.BID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID in (
select max(BID) from tblb as B2
group by B2.AID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID = (
select max(BID)
from tblb as B2
where B2.AID = B.AID
)
Steve Kass
Drew University
larzeb wrote:
>I have two tables defined below. I would like to join them but choose
>only the record from tblb which has the highest value of BID.
>I know the following is wrong and I need help. Thanks, larzeb.
>SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
>FROM tbla A
>JOIN tblb B
> ON A.AID = B.AID
>GROUP BY A.AID
>CREATE TABLE tbla (
> AID int IDENTITY(1,1) NOT NULL ,
> A char (10),
> B char (10),
> C char (10),
> D char (10),
> CONSTRAINT PK_ID PRIMARY KEY (AID)
> )
>CREATE TABLE tblb (
> BID int IDENTITY(1, 1) NOT NULL ,
> AID int NOT NULL ,
> Y char (10),
> Z char (10) ,
> CONSTRAINT PK_B PRIMARY KEY (BID),
> CONSTRAINT FK_B_A FOREIGN KEY (AID)
> REFERENCES tbla (AID)
> )
>
>
only the record from tblb which has the highest value of BID.
I know the following is wrong and I need help. Thanks, larzeb.
SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
FROM tbla A
JOIN tblb B
ON A.AID = B.AID
GROUP BY A.AID
CREATE TABLE tbla (
AID int IDENTITY(1,1) NOT NULL ,
A char (10),
B char (10),
C char (10),
D char (10),
CONSTRAINT PK_ID PRIMARY KEY (AID)
)
CREATE TABLE tblb (
BID int IDENTITY(1, 1) NOT NULL ,
AID int NOT NULL ,
Y char (10),
Z char (10) ,
CONSTRAINT PK_B PRIMARY KEY (BID),
CONSTRAINT FK_B_A FOREIGN KEY (AID)
REFERENCES tbla (AID)
)Here are a few possibilities:
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where not exists (
select * from tblb as B2
where B2.AID = B.AID
and B2.BID < B.BID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID in (
select max(BID) from tblb as B2
group by B2.AID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID = (
select max(BID)
from tblb as B2
where B2.AID = B.AID
)
Steve Kass
Drew University
larzeb wrote:
>I have two tables defined below. I would like to join them but choose
>only the record from tblb which has the highest value of BID.
>I know the following is wrong and I need help. Thanks, larzeb.
>SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
>FROM tbla A
>JOIN tblb B
> ON A.AID = B.AID
>GROUP BY A.AID
>CREATE TABLE tbla (
> AID int IDENTITY(1,1) NOT NULL ,
> A char (10),
> B char (10),
> C char (10),
> D char (10),
> CONSTRAINT PK_ID PRIMARY KEY (AID)
> )
>CREATE TABLE tblb (
> BID int IDENTITY(1, 1) NOT NULL ,
> AID int NOT NULL ,
> Y char (10),
> Z char (10) ,
> CONSTRAINT PK_B PRIMARY KEY (BID),
> CONSTRAINT FK_B_A FOREIGN KEY (AID)
> REFERENCES tbla (AID)
> )
>
>
Help with One-to-Many query
Given the following two tables:
Parent Child
-- --
ParentID ParentID
ChildID
how can I retrieve the Child row which has the highest value of
ChildID within any single Parent row?
The following does not work.
SELECT * FROM Parent p
INNER JOIN Child c ON p.ParentID = c.ParentID
WHERE c.ChildID = (SELECT MAX(c.ChildID))
Thanks LarsI think you want:
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Hit send too fast...
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
GROUP BY ParentId
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||SELECT *
FROM Parent p
INNER JOIN (select child.parentId, max(child.childId) as
childId, <other columns>
from child
group by child.parentId) as c
ON p.ParentID = c.ParentID
and p.childId = c.ChildId
This will work fine if you actually want all rows from parent matched with a
child. We might have to optimize some if you only want a small percentage
of the rows in parent.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Try,
use northwind
go
select
oh.orderid,
oh.orderdate,
od.max_productid
from
orders as oh
inner join
(
select
orderid,
max(productid) as max_productid
from
[order details]
group by
orderid
) as od
on oh.orderid = od.orderid
-- or
select
oh.orderid,
oh.orderdate,
od.productid
from
orders as oh
inner join
[order details] as od
on oh.orderid = od.orderid
where
od.productid = (select max(a.productid) from [order details] as a where
a.orderid = oh.orderid)
AMB
"larzeb" wrote:
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
>|||serveral ways to do it I guess, but you can use a sub-query for instance
SELECT
c.*
FROM
child c
INNER JOIN
(SELECT
parentid
,MAX(childID) ChildID
FROM
parent
GROUP BY
parentid) vt
ON c.childid = vt.childid
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
Parent Child
-- --
ParentID ParentID
ChildID
how can I retrieve the Child row which has the highest value of
ChildID within any single Parent row?
The following does not work.
SELECT * FROM Parent p
INNER JOIN Child c ON p.ParentID = c.ParentID
WHERE c.ChildID = (SELECT MAX(c.ChildID))
Thanks LarsI think you want:
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Hit send too fast...
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
GROUP BY ParentId
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||SELECT *
FROM Parent p
INNER JOIN (select child.parentId, max(child.childId) as
childId, <other columns>
from child
group by child.parentId) as c
ON p.ParentID = c.ParentID
and p.childId = c.ChildId
This will work fine if you actually want all rows from parent matched with a
child. We might have to optimize some if you only want a small percentage
of the rows in parent.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Try,
use northwind
go
select
oh.orderid,
oh.orderdate,
od.max_productid
from
orders as oh
inner join
(
select
orderid,
max(productid) as max_productid
from
[order details]
group by
orderid
) as od
on oh.orderid = od.orderid
-- or
select
oh.orderid,
oh.orderdate,
od.productid
from
orders as oh
inner join
[order details] as od
on oh.orderid = od.orderid
where
od.productid = (select max(a.productid) from [order details] as a where
a.orderid = oh.orderid)
AMB
"larzeb" wrote:
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
>|||serveral ways to do it I guess, but you can use a sub-query for instance
SELECT
c.*
FROM
child c
INNER JOIN
(SELECT
parentid
,MAX(childID) ChildID
FROM
parent
GROUP BY
parentid) vt
ON c.childid = vt.childid
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
Monday, February 27, 2012
Help with multiple IIFs, or need suggestion of better solution.
I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.
So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)
I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")
Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?
Much appreciated.
Use the "And" operator. It would look like this:
iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")
|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.This would be the ideal situation:
=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.
Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &
I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||
One question is what is the datatype on the database field? If it is boolean then you should be able to do:
=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...
The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.
=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...
|||
Use the Report Properties.Code.Custom Code feature.
1.Create a function in the CODE section
2. Pass all your field values to the function
3. The return value is used in the textbox.
You have a lot more coding power in the CODE section than you do with expressions.
Hope this helps.
|||Can you do it in SQL using case statement ?
|||=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")
Thanks, Donovan.
Friday, February 24, 2012
help with loop to find Dates
Hey all, I have this query that finds the 'next date' after a user inputs a date (and a another value). It works fine, except it only returns ONE row for the Date. Several dates have many rows, and I guess I need to have this loop somehow so it will return all rows? make sense? Can anyone help me with this?
SELECT top 1 Hours.Datewrk, Employee.Lastname, Employee.Firstname, Employee.EmployNo, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, PurchaseOrder.Purchord, Hours.TicketNo
FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.EmployNo = Employee.EmployNo
WHERE Hours.Datewrk is not null and Hours.Datewrk > '" & txtDate1.Text & "' And PurchaseOrder.JobNo = '" & cboJobNo1.Value & "'
ORDER BY Employee.Lastname, Employee.Firstnameremove top 1
SELECT top 1 Hours.Datewrk, Employee.Lastname, Employee.Firstname, Employee.EmployNo, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, PurchaseOrder.Purchord, Hours.TicketNo
FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.EmployNo = Employee.EmployNo
WHERE Hours.Datewrk is not null and Hours.Datewrk > '" & txtDate1.Text & "' And PurchaseOrder.JobNo = '" & cboJobNo1.Value & "'
ORDER BY Employee.Lastname, Employee.Firstnameremove top 1
Sunday, February 19, 2012
Help with Joins
Hi All,
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/
help with Interactive Sort on a column...
I have been selecting the column name textbox at the top of a column
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--
Help with inserting multiple records using a CSV value.
I have the Temporary table:
ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))
in one instance Value might equal: "1, 2, 3, 4"
This only happens when FieldTypeID = 5.
So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])
I have created a function to split the [Value] into a table of INTs
Any Advice?
If your function returns a table type data, loop through the table and do an INSERT for each row.
|||I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\
Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(
You could do an :
(1) Declare a table variable with an additional column Processed tinyint.
(2) INSERT INTO @.table
SELECT dbo.someFunction
(3) Loop through the table.
WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)
Begin
Get the values from the @.table
Insert into the Original table
update @.table set processed = 1 Where Condition
End
|||
I think I understand...
While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN
END
It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?
RTernier:
I think I understand...
While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGINEND
It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?
Yes.
|||That would work. Now another question (Yea, I'm not that strong in SQL :P )
While I go through the WHILE loop,
Is there a way I can grab the values of the loop I'm going through?
Example:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN
END
====
I could do this right:
WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN
PRINT T.MyColumn
END
===
if not, how can I directly access the values from T?
If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.
Decare @.rowid int
WHILE ...
Begin
SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0
INSERT INTO ...original table
Update @.t Set Processed = 1 Where Id = @.Rowid
End
Subscribe to:
Posts (Atom)