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
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.
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’);
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 ()
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.
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�
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.