Updates To Multiple Records
Mar 9, 2002
Is it possible to do an update on multiple records. I have fields CusOrderID and CusCreditAmt in table CusOrder; and fields CusOrderID and CreditAmt in table CusCredits. I would like to update the value in the CusCreditAmt field in CusOrder to equal the CreditAmt field in CusCredits where CusOrderID is the same in both tables.
I tried doing this in a stored procedure based on a View with an inner join between the 2 tables as follows:
Update View1
Set CusCreditAmt = CreditAmt
I received an error that the subquery returned more than one value. Is there anyway to do something like this and make it work?
Many thanks for any advice.
View 1 Replies
ADVERTISEMENT
Mar 13, 2002
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
View 2 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Dec 28, 2012
I am trying to update a small subset of records of a given table (TRValue) using the records contained in ParcelTemp. The difficult part is getting the summation from a child file, TRGreen, for those same parcels contained in ParcelTemp. Instead of updating just a few records, all the records in TRValue are being updated, with the wrong values of course!
Basically, Update records in TRValue that are equal to:
Year = P.Year
Code = 'LG01'
Parcel = P.Parcel
with the summation of child records where the child records needed are:
Year = P.Year
Parcel = P.Parcel
Code:
UPDATE TRValue SET
Acres = SumAcres,
CurrentMarket = SumMarket,
CurrentTaxable = SumTaxable,
CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50)
FROM ParcelTemp P
[code]....
View 4 Replies
View Related
Jul 13, 2004
I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?
View 1 Replies
View Related
May 25, 2004
Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Thanks you
Jeff
View 2 Replies
View Related
Jun 22, 2015
I have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).
View 3 Replies
View Related
Jul 23, 2014
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2
[code]....
View 4 Replies
View Related
Sep 13, 2007
Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields.
How do i update the row coming from the transaction file in the tables which contain the rows from the master file .
to better explain the example is
Master File
ID Name AGE Salary Transcode TransDate
2 dev 27 2777
Transaction File indicating change
ID Name AGE Salary Transcode TransDate
2 dev 27 24444 C 08072007
the ouput should be
2 dev 27 24444 C 08072007
replacing the existing row in the table(updating the whole row)
i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row.
what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row.
Thanks...
View 1 Replies
View Related
Apr 26, 2007
Hi,
I have few tables. I want to identify the RECORDS for a table which has been created/modified for a particular date and time. I don't want to write a trigger to capture the event for add/update.
Is there any system table which track for date and time using stored procedure each individual records which has been last updated or newly created records??
Note : The application already created without lastModified date and each table... so, we don't want to modify the application or db.
Database : SQL Server 2000 sp4
thanks in advance.
View 3 Replies
View Related
May 25, 2004
I am a total newbie to SQL Server, but have a good idea about how things operate..
I have a "job" that runs multiple (10) update queries for me in different tables.
Can I create a stored procedure to do these so that I can make one call to them?
If not, how can I call the job to start external to MS SQL?
View 1 Replies
View Related
Apr 15, 2006
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example.
There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
Any help would be appreciated. Many thanks.
View 3 Replies
View Related
Jul 20, 2005
Using SQL2000, is there another way of optimizing the original query below.TIA,BobUpdate Transactionset field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder wheretblOrder.id = Transaction.id),field2 = (select avg(price) from tblOrder where tblOrder.id =Transaction.id),field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)etc..(there are six additional updates)Is this a quicker way:Update TransactionSET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,field3 = min(cost) , etc.....FROM tblOrderWHERE tblOrder.id = Transaction.id
View 1 Replies
View Related
Jul 20, 2005
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
View 6 Replies
View Related
Sep 27, 2006
I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
TIA,
Barkingdog
View 4 Replies
View Related
Aug 28, 2007
I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.
Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?
View 9 Replies
View Related
Apr 21, 2004
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.
View 7 Replies
View Related
Dec 28, 2007
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
Following is the table structure:
Customer_Master(custmastercode, customer_company_name,updated_by)
Following is the trigger:
ALTER TRIGGER [TR_UPDATE_CUST]
ON [dbo].[CUSTOMER_MASTER]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted)
BEGIN
declare @custcode int
Declare @message varchar(5000)
Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int
Declare @CUSTOMER_COMPANY_NAME varchar(50)
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
IF(update([CUSTOMER_COMPANY_NAME]))
Begin
select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted
select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted
Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'
Set @message = @message + @custommessage
End
Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'
INSERT INTO [CHANGE_HISTORY]
([CUSTMASTERCODE]
,[CHANGE_DETAILS])
VALUES (@custcode, @message)
END
END
View 7 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Nov 8, 2006
Hello,
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
Here is the stored procedure:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- File: sp_GetProgramsWatchedByDateRange.sql
-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.
-- Note that some of that data used by this procedure are obtained from the RMS_EPG database
-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.
-- Auth: H Hunsaker
-- Date: 11/07/2006
-- Example invocation
-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description
-- 3. StartDate datetime First date of reporting period
-- 4. EndDate datetime Last date of reporting period
-- TerseMode bit Return all columns? (1 = no, 0 = yes)
-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)
-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL
DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@TerseMode bit = 0,
@AsXML bit = 0,
@Debug bit = 0
AS
-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.
-- So I am going to the RMS_EPG database to obtain that information.
-- We will have to ensure that the 2 databases are generated at the same or a matching time
-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing
-- DECLARE @StartDate datetime
-- DECLARE @EndDate datetime
-- DECLARE @TerseMode bit
--SET @StartDate = NULL
--SET @EndDate = NULL
--SET @TerseMode = 1
SET NOCOUNT ON
CREATE TABLE #programWatched
(
--IPTV device ID
tdeviceId uniqueidentifier NULL,
taccountId uniqueidentifier NULL,
-- Basic program information
tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.
tprogramId uniqueidentifier NULL, -- programID generated by IPTV
tprogramTitle varchar(150) NULL,
tprogramEpisodeTitle varchar(100) NULL,
tprogramDescription varchar(500) NULL,
toriginDateTime datetime NULL,
tduration bigint NULL,
tprogramType nvarchar(100) NULL,
tchannelCallName nvarchar(20) NULL,
--Rating
programMPAARating varchar(50) NULL,
programVCHIPRating varchar(50) NULL,
programMPAARatingVal smallint NULL,
programVChipRatingVal smallint NULL,
-- Categories
programGenre varchar(50) NULL,
programCategory1 varchar(50) NULL,
programCategory2 varchar(50) NULL,
programCategory3 varchar(50) NULL,
programCategory4 varchar(50) NULL,
-- Roles
programActor1FirstName varchar(50) NULL,
programActor1LastName varchar(50) NULL,
programActor1 varchar(100) NULL,
programActor2FirstName varchar(50) NULL,
programActor2LastName varchar(50) NULL,
programActor2 varchar(100) NULL,
programActor3FirstName varchar(50) NULL,
programActor3LastName varchar(50) NULL,
programActor3 varchar(100) NULL,
programActor4FirstName varchar(50) NULL,
programActor4LastName varchar(50) NULL,
programActor4 varchar(100) NULL,
programActor5FirstName varchar(50) NULL,
programActor5LastName varchar(50) NULL,
programActor5 varchar(100) NULL,
programActor6FirstName varchar(50) NULL,
programActor6LastName varchar(50) NULL,
programActor6 varchar(100) NULL,
programActor7FirstName varchar(50) NULL,
programActor7LastName varchar(50) NULL,
programActor7 varchar(100) NULL,
programActor8FirstName varchar(50) NULL,
programActor8LastName varchar(50) NULL,
programActor8 varchar(100) NULL,
programDirectorFirstName varchar(50) NULL,
programDirectorLastName varchar(50) NULL,
programDirector varchar(100) NULL,
programWriterFirstName varchar(50) NULL,
programWriterLastName varchar(50) NULL,
programWriter varchar(100) NULL,
programProducerFirstName varchar(50) NULL,
programProducerLastName varchar(50) NULL,
programProducer varchar(100) NULL,
-- Flags
ClosedCaption bit NULL,
InStereo bit NULL,
Repeats bit NULL,
New bit NULL,
Live bit NULL,
Taped bit NULL,
Subtitled bit NULL,
SAP bit NULL,
ThreeD bit NULL,
Letterbox bit NULL,
HDTV bit NULL,
Dolby bit NULL,
DVS bit NULL,
FlagOrdinalValue smallint NULL,
-- Channel
tchannelId int NULL,
callLetters varchar(20) NULL,
displayName varchar(50) NULL,
type varchar(50) NULL,
networkAffiliation varchar(50) NULL
)
-- I store the program watching data in a temp table because
-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.
-- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
AND originTime BETWEEN @StartDate AND @EndDate
ELSE
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId
UPDATE #programWatched
SET taccountId = (SELECT accountId
FROM DW_BRDB_bm_device d
WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)
-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs
UPDATE #programWatched
SET tchannelCallName = (SELECT TOP 1 channelCallName
FROM DW_EPG EPG
WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched
SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags)
UPDATE #programWatched
SET programMPAARating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched
SET programMPAARatingVal = CASE programMPAARating
WHEN 'G' THEN 10
WHEN 'PG' THEN 25
WHEN 'PG-13' THEN 30
WHEN 'R' THEN 35
WHEN 'NC-17' THEN 50
WHEN 'NRAO' THEN 60
WHEN 'NR' THEN 0
ELSE 0
END
UPDATE #programWatched
SET programVChipRating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched
SET programVChipRatingVal = CASE programVChipRating
WHEN 'TV-Y' THEN 10
WHEN 'TV-Y7' THEN 20
WHEN 'TV-G' THEN 35
WHEN 'TV-PG' THEN 40
WHEN 'TV-14' THEN 45
WHEN 'TV-MA' THEN 60
ELSE 0
END
-- Genre
UPDATE #programWatched
SET programGenre = (SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId
INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId
WHERE tprogram = pc.programID)
-- Categories
UPDATE #programWatched
SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID)
UPDATE #programWatched
SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched
SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched
SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles
UPDATE #programWatched
SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirectorLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName
UPDATE #programWatched
SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriterLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriter = programWriterLastName + ' , ' + programWriterFirstName
UPDATE #programWatched
SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducerLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducer = programProducerLastName + ' , ' + programProducerFirstName
UPDATE #programWatched
SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName
UPDATE #programWatched
SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName
UPDATE #programWatched
SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName
UPDATE #programWatched
SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName
UPDATE #programWatched
SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName
UPDATE #programWatched
SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName
UPDATE #programWatched
SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName
UPDATE #programWatched
SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName
-- Channel (provider) Call Letters, Display Name and Type
-- Is this correct? Should we get the channelId from the schedule table?
-- Is this efficient? View execution plan
UPDATE #programWatched
SET tchannelId = (SELECT TOP 1 c.channelId
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram)
UPDATE #programWatched
SET callLetters = (SELECT TOP 1 c.channelCallLetters
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET displayName = (SELECT TOP 1 c.channelDisplayName
FROM RMS_EPG..channel c
JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET type = (SELECT TOP 1 c.channelType
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0
SELECT *
FROM #programWatched
ORDER BY toriginDateTime
ELSE
-- Get only Genre, title, show date/time, rating, call letters
SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName
FROM #programWatched
ORDER BY toriginDateTime
DROP TABLE #programWatched
SET NOCOUNT OFF
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer,
meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT PW.DeviceID,
PW.originTime AS 'When Watched',
PW.programID,
PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime
AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND
DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN
DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime
BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW
ORDER BY DeviceId, programId, originTime
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Any help is appreciated
View 3 Replies
View Related
Aug 13, 2006
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
View 2 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Jan 20, 2006
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View 1 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Apr 15, 2003
I'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(
I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.
I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.
Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?
Thank you very much for any help,
Mischa
View 9 Replies
View Related
Jul 7, 1999
I am trying to join seperate records into one return set.
IE. select accountNum, flag1, flag2, flag3 from MyDB where flag1='A' or flag2='B' or Flag3='C'
The problem is that there could be 2 records with the same account number but flag1 ='A'
Flag2='B' ect.
This would return to lines. Is there a way that I can have all return sets show up on one line only.
Eliminating duplicate return sets???
View 1 Replies
View Related
Jun 24, 2004
Hi there...
I am new at sql programming and I need all the help i can possibly get..Im using SQL Server 2000 and I have been given an assignment which follows:
- i have to add 100 new records at once to an existing table.
I have to do it through SQL Server only( query analizer,i suppose) and I am not allowed to use any other languages to achieve this. Now, my question is how can i do it through sql only? What is the code i have to write to make this thing work? I simply dont know where and how to start...
thanx in advance and sorry for my bad english
View 10 Replies
View Related
May 17, 2008
Dear ALL,
I have two tables with named Reports and Backup_Reports with same fields. Id(numeric), MobileNumber(varchar), SmsBody(varchar),date(datetime),Status(char).
Here what I want is, if I am searching some records by entering the mobile number then the sql query first will search on the first table Reports then it will show the related records and again the query will search on the second table Backup_Reports then will show related records from this table also.
What I mean is, if I am searching some records by mobilenumber first will search on the Reports and then will go to second table Backup_Reports and will show all the related records together.
How can I handle this?
regards
Shaji
View 9 Replies
View Related
Jul 20, 2005
I have a query that returns multiple identical records, however it shouldonly return one. Indeed there is only one record for the OrderActionTypecodeof 'P' yet there are two orderactions so it returnd both.Oddly the second query returns only the desired single record but wihtoutall the additional fields I need. Does the Inner join somehow mess with thequery?ThanksQuery 1SELECT Orders.OrderID, Orders.TicketID, A.AttemptID, E.EventID,E.AssetCode AS Asset, Orders.FK_Prod_Alias AS Alias, Orders.ContractCode,L.OrderLegCode AS OrderLeg, M.MarketActionName ASAction, Orders.OrderVolume AS Volume, Orders.OrderPrice AS Price,T.OrderTypeName AS OrderType, S.StatusName AS Status,Orders.FilledVolume AS Filled, Orders.OriginalOrderDateTime,Orders.PlaceOrderDateTime,Orders.MonitorFlagFROM Orders INNER JOINOrderAction OA ON Orders.OrderID = OA.OrderID INNERJOINAttempt A ON Orders.AttemptID = A.AttemptID INNER JOINEvent E ON A.EventID = E.EventID INNER JOINMarketAction M ON Orders.MarketActionCode =M.MarketActionCode INNER JOINOrderLegs L ON Orders.OrderLegCode = L.OrderLegCodeINNER JOINOrderType T ON Orders.OrderTypeCode = T.OrderTypeCodeINNER JOINOrderState S ON Orders.Status = S.Status ANDOrders.OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P'))Query 2SELECT OrderID, TicketIDFROM OrdersWHERE (OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P')))
View 3 Replies
View Related
Feb 23, 2007
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section.
I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck.
Regards!
Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open()
For Each ..... Problems here also
Next
cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
View 7 Replies
View Related
Jun 28, 2007
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 Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
View 7 Replies
View Related
