Sum Conditional Fields
Mar 24, 2008i have 4 column in my query , and arranged in matrix report
but i want to add some cells according if one column is true or false
i hope that i clear the problem
ADVERTISEMENT
Conditional Fields Based On Export Type
Aug 27, 2007I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
Any help would be appreciated.
Jerry
Conditional Subscription / Conditional Execution Of Report
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
Conditional Formatting - Not So Conditional??
Dec 15, 2006I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined
Feb 20, 2008How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Public Overridable ReadOnly Default Property Fields() As ADODB.Fields
Jan 26, 2008sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Update Fields With Searched First Date Record Fields
Jul 23, 2005Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedTransact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?
Jul 2, 2015I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Search Multipe Fields, Compounding Fields, Like, Contains...?
Jul 20, 2005I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedUpdate Fields With Data From Other Fields In Same Row
Jun 30, 2000Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
Using LIKE To Find Any Of Fields In One Table In Fields Of Another
Jul 31, 2013 I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?
Jan 21, 2008Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
Conditional FROM/WHERE
Apr 13, 2001SQL 7
Hi All !!
I want to know how I can create conditional FROM WHERE clauses like below ..
SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
Thoughts ?
Thanks
Conditional When
Jun 23, 2008Is anything similar to this possible without writing whole query in every begin block or without dynamic sql:
proc(@arg1 bit)
select * from table where x=y
if @arg1 = 1 begin
and when a=b
end
order by date
Conditional -- ?
Jul 23, 2005What does "conditional" mean as a command? sp_who2 reports this forsome sessions, along with "insert" etc.Thanks,Jim Geissman
View 2 Replies View RelatedConditional Sum?
Apr 23, 2007Hi,
Is it possible to have a conditional sum based on an item type existance in a set of values?
Example if i have the following set:
A
A
A
A
B
I just wanna sum B else if B doens't exist sum A
Best Regards,
Luis Simoes
Conditional Inner Join?
Jan 30, 2007Im faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
If Conditional Problem In T-Sql
Jan 31, 2007I encounter a T-Sql problem related to if conditional processing:The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.Could anyone offer some advice? IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0) BeginINSERT INTO dbo.DefaultsSELECT FinancialPlannerID,ReportTitleFROM dbo.ReportPreferencesendGO
View 6 Replies View RelatedConditional Where Statement
Jul 23, 2007I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View 6 Replies View RelatedSQL Conditional WHERE Statement
Feb 4, 2008Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
(
@startRef int,
@endRef int
)
AS
BEGIN
SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
END
RETURN
Conditional SQL Question
Feb 23, 2008I have an SqlDataSource that uses a value from the query string in the WHERE clause of the sql statement.
The sql is something like this:
SELECT * FROM myTable WHERE myfield = @myfield
and I have the QueryStringParameter setup like this:
<asp:QueryStringParameter Name="myfield" QueryStringField="myfield" />
What I need is for the sql statement to return all records in the case that "myfield" is not defined in the query string.
How would I implement this?
Thanks,
Joshua Foulk
Conditional Update
Feb 21, 2006Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished?
Here is my sproc:CREATE PROCEDURE [webuser].[cssp_removeItem]
@lblID int
AS
Update cstb_inventoryset qtyonhand = qtyonhand -1where Id = @lblIDGO
Here is my app code:
Try
Dim cmd As SqlCommand = cn.CreateCommand
cmd = New SqlCommand("cssp_removeItem", cn)
cmd.CommandType = CommandType.StoredProcedure
With cmd
cmd.Parameters.Add("@lblId", SqlDbType.Int).Value = lblId.Text
End With
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
cmd.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
If Not cn.State = ConnectionState.Closed Then
cn.Close()
cn = Nothing
End If
Conditional WHERE Clause
May 8, 2006Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID) AND (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT AdviserID, AdviserName FROM Advisers WHERE IF @CompanyID = 2 THEN BEGIN (CompanyID = @CompanyID) END ELSE BEGIN (CompanyID = @CompanyID) AND (TypeID = 3) END
I've also tried:
SELECT AdviserID, AdviserName FROM Advisers WHERE CASE @CompanyID WHEN 2 THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END
and
SELECT AdviserID, AdviserName FROM Advisers WHERE CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary
Conditional Foreign Key - Can It Be Done???
May 17, 2001Hello Folks,
Well I've hit the wall on this one:
I'm wondering if one can designate a conditional foreign key that relates to one of many different tables depending on the "type" column in the foreign key's. My goal is to come up with some SQL code that will allow for this in a Create table statement. (By the way I'm using MS SQL-7 but I'm wondering if this can be done in general.)
I have two simple cases below that show illustrate what I'm trying to do:
Thanks in advance, -JerryZZ
---------------------------------------------------------
Case 1: The foreign key relation is to a primary key
Table: Invoices
-Fields:
---InvoiceID ..... (primary key)
---BilleeID ...... (fkey to Manfacturers.ManfID IF BilleeType=M)
...................(fkey to Distibutors.DistID IF BilleeType=D)
---BilleeType .....(constraint = M or D)
Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---CompanyName.. (not null)
Table: Distributors
-Fields:
---DistID ........ (primary key)
---CompanyName .. (not null)
---------------------------------------------------------
Case 2: The foreign key relation is to a unique "not null" non-primary key
Table: InvoicesEmail
-Fields:
---InvoiceID ..... (primary key)
---EmailAddress .. (fkey to Manfacturers.EmailAddress IF BilleeType=M)
...................(fkey to Distibutors.EmailAddress IF BilleeType=D)
---BilleeType .....(constraint = M or D)
Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---EmailAddress .. (unique, not null)
Table: Distributors
-Fields:
---DistID ........ (primary key)
---EmailAddress .. (unique, not null)
---------------End-O-Message-------------------------------------
Conditional Delete
Nov 15, 2000Hi, ladies and gentelmen!
Can you help me with following trouble:
I got a table (let it be called SomeTable) in which there's one nullable field (let's call it SomeField) among many others. PRIMARY KEY for SomeTable is of INT IDENTITY type.
Business rules are following: only records that have SomeField IS NULL can be deleted, so I need to perform conditional delete (for cases like DELETE SomeTable). I don't like idea about using SP here, so I tried to solve the task by means of trigger. However, when DELETE clause is used within a transaction and there're records affected by the trigger that don't match business rule, trigger uses ROLLBACK TRAN and then deletes only matching rows from target table. Everything works fine with our SomeTable, but not so fine with the transaction, because due to ROLLBACK TRAN statement in trigger body this transaction is rolled back (as it is described in documentation). But I don't wanna ALL my transaction rolled back! So, is there in SQL Server 7.0 any way to rollback only changes that caused trigger to fire? Something like ROLLBACK TRIGGER?
Help With IF THEN - Conditional ORDER BY
Dec 2, 1999I would like to write the following (pseudo) stored procedure, but am having problem with the syntax:
Create Procedure spSort
@IDContract nvarchar(10)
@SortOrder int
SELECT * FROM Contracts
WHERE IDContract = @IDContract
IF @SortOrder = 0
BEGIN
ORDER BY ContractDate
END
IF @SortOrder = 1 THEN
BEGIN
ORDER BY ShippingPeriod
END
The problem is in conditionally setting the sort order. The actual sp is quite complex and I don't really want to have to use two procedures (one for ordering by ShippingPeriod and one for ordering by ContractDate
Any suggestions please?
Many thanks
Jeremy Holt
Conditional Insert
Oct 29, 2006Hi,
Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into ssotarget:
INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)
SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS
FROM
FULLSOURCE
GROUP BY PIN
Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:
SELECT [PIN]
,[SYSTEM]
,[Header]
,[Property]
,[Value]
,[Status]
,[ID_Header]
,[ID_Property]
FROM [fullsource]
Conditional Select
Nov 8, 2007I am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.
Code:
SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END
FROM HEAT.dbo.CallLog C,
(SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution
FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P
WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta')
GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution
If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.
Thank you
Court
Conditional SQL Count
Feb 13, 2006Is there a way to have a conditonal count within a stored procedure
ie
Code:
Select field, field2, Count(if Field3 > 0)
(i know this code will not work)
The idea is to only count records if they have a value greater than zero
Conditional Trigger
Jul 17, 2006I have a trigger that updates values in a table when col2 is updated by a stored procedure.
I'd like to make this a conditonal trigger to prevent negative values from being inserted in the table. I'm not familiar with the syntax of triggers enough to get this to work and I've search for a reference with no luck.
Here's the current trigger code:
Code:
FOR INSERT, UPDATE
AS
UPDATE table1
SET col4= col1 - col2 - col3
I need to evaulate (col1 - col2 - col3) to see if it's less than zero. If it is, I want to set col4=0.
This is what I've tried, but it doesn't work. Any help is appreciated:
Code:
FOR INSERT, UPDATE
AS
select col1, col2, col3 from table1
if (col1 - col2 - col3) < 0
UPDATE table1
SET col4 = 0
else
UPDATE table1
SET col4= col1 - col2 - col3
Conditional Insert
Oct 22, 2004I need to do a conditional insert. This is what I have tried, it does not work.
What am I doing incorrectly?
IF (SELECT COUNT(*) FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0
INSERT INTO TBL5
SELECT TBL1.PRO_SITE, TBL1.MODEL_ID,
TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1,
TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL
AS Expr5, 0 AS RAMP
FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC
UPDATE TBL5
SET TEAMS=0
GO
UPDATE TBL5
SET TEAMS = (SELECT NUM_SLOTS
FROM TBL6 R1
WHERE (R1.TEAM_ID = TBL5.TEAM_ID)
)
WHERE (TEAM_ID =
(SELECT TEAM_ID
FROM TBL6 R3
WHERE (R3.TEAM_ID = TBL5.TEAM_ID)))
GO
UPDATE TBL5
SET TOTALTEAMS = TEAMS + RAMP
GO
I need to do a conditional because sometimes the select that returns data contains no records.
Thanks...
Conditional SQL Triggers
Oct 29, 2004Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.
I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.
For instance, say I have the following table:
CREATE TABLE source
(
ID tinyint NOT NULL,
contacttype tinyint NOT NULL,
)
If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.
How can this be performed?