If Then Statments
May 24, 2007
I am new to reporter server and I am much more familar with Crystal. I am trying to write a formula/expression that is easy in crystal but can't seem to write it in Report Server.
It is a basic If Then statement that returns a value if 2 conditions are true and another value if they are false.
So
If State = 'TX'
and
City = 'Dallas'
then 1
else
0
I have been able to use the IIF expression but I can only get it to work with one variable. So, I can do
=IIF(State = 'TX, 1,0)
but can't add the other criteria.
Any help is appreciated
Jeff
View 4 Replies
ADVERTISEMENT
Feb 10, 2008
I'm in a Database class and am finding it very difficult to find any outside help. I'm sure this will appear to be very basic to those of you who work in the Database field, but your help will be greatly appreciated.
INVENTORY( SKU, Description, QUANTITYOnHand, QuantityOnOrder, Warehouse)
WAREHOUSE( Warehouse, Manager, SquareFeet)
From the two tables above we're suppose to:
1.Write an SQL statement to show the Warehouse and average QunatityOnHand of all items stored in a warehouse managed by 'Smith'. Use a subquery.
2.Write an SQL statement to show the Warehouse and average QunatityOnHand of all items stored in a warehouse managed by 'Smith'. Use a join.
This is what I came up with. Please give me some feedback:
SELECT Avg(QuantityOnHand)
FROM INVENTORY
WHERE WAREHOUSE IN
(SELECT WAREHOUSE
FROM MANAGER
WHERE Manager = ‘Smith’);
2.44)SELECTAvg(QuantityOnHand)
FROMINVENTORY, WAREHOUSE
WHEREManager = ‘Smith’
View 3 Replies
View Related
Mar 3, 2005
What I have is a small DTS package that truncates a table then loads it from a text file. I want to enhance it by sending an e-mail with record counts to our client.
The load is pretty straight forward
delete from marketing..solicit_consumer
from marketing..solicit_consumer sc
join dsi_use..dnc_tmp dt
on sc.consumer_no = dt.consumer_no
and sc.solicit_cd = dt.solicit_cd
go
insert marketing..solicit_consumer
select * from dsi_use..dnc_tmp
go
After I have an Active X script to format an e-mail but I need the counts from the SQL statement. I have tried to use the following with no luck.
Option Explicit
Function Main()
Dim oPkg, oDataPump, sSQLStatement
' Build new SQL Statement
sSQLStatement = "SELECT count (*) FROM dsi_use..dnc_tmp " & _
DTSGlobalVariables("DNC_Count").Value & "'"
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
if oDataPump.SourceSQLStatement <> 0 then
FrmtEmail ()
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
End Function
The actualy format of the e-mail I think will work if ony I can get the main function to work.
Right now it gives me a VB script runtime error.
Type Mismatch:'[string: "SELECT count(*) FR"]'
error on line 19
Line 19 is somewhere within these lines
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
if oDataPump.SourceSQLStatement <> 0 then
FrmtEmail ()
help...
View 1 Replies
View Related
Mar 18, 2004
i'm passing 4 paramaters to a stored proc. based on the values of the paramaters i add conditions to my select. can som one please reviwe the proc below and tell me if my syntax is wrong or if there is another way of doing this.
Thank You,
Thomas
CREATE PROCEDURE [Multi_Picking_Slip_FillListview1]
@str_Division nvarchar(50), @str_Season nvarchar(50), @str_Cust nvarchar(50), @str_ShipTo nvarchar(50) AS
SELECT * from tblDistribution WHERE PikingNo = 'NO'
If @str_Division <> ''
AND Division =@str_Division
If @str_Season <> ''
AND Season = @str_Season
If @str_Cust <> ''
AND cusNumber = @str_Cust
If @str_ShipTo <> ''
AND shpStoreNo = @str_ShipTo
GO
View 7 Replies
View Related
Oct 30, 2007
I am not sure how to do this. I need to run 3 sql statements against a table with a variable created in one of them.
Here is the first statement
Select ID from table1 where value = 1
Need to store that value in a variable
Update table1
Set value = 0
Where ID = variable
Update table1
Set value = 1
Where ID = variable + 1
ID is a incremental identity field, so it is numeric. Basically I need to change the value of one record to 0, and make the next records value = one.
Any help is appreciated.
View 1 Replies
View Related
Nov 2, 2007
Hope someone can help;
I have a table with a list of payment information i have three other tables that store different types of commission rates that were active at a particular time.
Payments table – holds all payments received by customers
DirectRate table – holds the Direct rate active between start and end dates
ComRate table – holds the Commission rate active between start and end dates
FieldRate table – holds the Field rate active between start and end dates
Basically I am trying to get the total value of commission on all payments for all the different rates. To give you an example one payment can be of type Direct which would have to have the correct payment rate applied from the DirectRate table for the correct date range, this also applies for payments that are of type ComRate & FieldRate.
So I have the following SQL
SELECT CASE
WHEN dp.ReceivedByID = 1
THEN
dp.Amount * ((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)))/100)
WHEN dp.ReceivedByID = 2
THEN
dp.Amount * ((Select tD.Rate From dbo.mTrackerDirectChange tD where tD.ClientID=d.ClientID and tD.ContractID=d.ContractID AND ((dp.PaymentOn >= tD.StartDate AND dp.PaymentOn <= tD.EndDate) or (dp.PaymentOn >= tD.StartDate AND tD.EndDate IS NULL)))/100)
ELSE
dp.Amount * (((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL))) + (Select tFe.Rate From dbo.mTrackerFieldChange tFe where tFe.ClientID=d.ClientID and tFe.ContractID=d.ContractID AND((dp.PaymentOn >= tFe.StartDate AND dp.PaymentOn <= tFe.EndDate) or (dp.PaymentOn >= tFe.StartDate AND tFe.EndDate IS NULL))))/100)
END
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d
where
d.DebtID=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=2
I am using dp.ReceivedByID to assertain the payment type then depending upon that using the case statement to multiply the amount by the correct rate for the correct date range in the correct table. This sql works fine but it gives me a list of commision values, one for each payment. My problem is when I try to do a sum on this case statement I get an error
“Cannot perform an aggregate function on an expression containing an aggregate or a subquery�
any help most appriciated
p
View 7 Replies
View Related
Nov 12, 2007
How do i add multiple case statements
CREATE procedure rpt_blankregistrationquestions
@cmb1 as varchar(100),
@cmb2 as varchar(100) WITH ENCRYPTION
AS
BEGIN
SELECT DISTINCT
Child.surname + ', ' + Child.forename AS ChildName,
permissionRequired.description,
healthitems.description,
dietaryneeds.description,
CASE WHEN permissionRequired.active = 1 THEN 'YES'
WHEN permissionRequired.active = 0 THEN 'NO'
END AS Child_Permission
CASE WHEN healthitems.description.active = 1 THEN 'YES'
WHEN healthitems.description.active = 0 THEN 'NO'
END AS Health_Permission
CASE WHEN dietaryneeds.description.active = 1 THEN 'YES'
WHEN dietaryneeds.description.active = 0 THEN 'NO'
END AS Dietaryneeds_Permission
FROM healthItems CROSS JOIN
DietaryNeeds CROSS JOIN
permissionRequired CROSS JOIN
Child
ORDER BY ChildName
END
View 1 Replies
View Related
Apr 26, 2008
Lets say that Dealers have ZipCodes, and that a Dealer can have more than one zipCode, and we want the list of dealers that have both 90210 and 90211 zip codes. BUT we don't want any dealers that have only one of the two ZipCodes in question
What I want to do is something like this
Select DealerID from DealerZips where Zip = '90210'
intersection
Select DealerID from DealerZips where Zip = '90211'
but I get this error msg:
Line 2: Incorrect syntax near 'intersection'
The following sql is silly, but it does run without error
Select DealerID from DealerZips
intersection
Select DealerID from DealerZips
So I am pretty sure my problem is with the Where clauses.
help!
View 3 Replies
View Related
Sep 24, 2007
Hi,
I have a stored proc and using transactions as foolows
(not coplete Sp)
Begin Transaction TransName
Select @vsSql = Create a temp table (dynamically)
Exec( @vsSql )
Select @vsSql = dynamic insert statement
Exec( @vsSql )
and executing couple of dynamic statements using Exec
And @ the end of SP
if @@error <>0
rollback transaction TransName
else
commit transaction TransName
and when i execute the stored proc i am getting the following error
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
And also my sql server management studio hogs up
Can any one please help me on this
~Mohan
View 9 Replies
View Related
