Transact SQL :: How To Do Procedural Updates Based On Record String
Aug 16, 2015
How to do some procedural updates based on a record string. What I wish to do.
If the value (of type string) in my column contains the ‘/’ character I wish to ‘do something’
If the value (of my string) in my column contains the ‘-‘ character I wish to ‘do something else’.
Assume my column contains both ‘/’ and ‘-‘ in the same table.column, but not within the same record.
The data in question is in
tbl_Quotes.tLastDateValue
I’ve found I can use something like this to search my string.
SELECT
Count(CHARINDEX('/',tbl_Quotes.tLastDateValue))
FROM tbl_Quotes
WHERE
CHARINDEX('/',tbl_Quotes.tLastDateValue)>0
And similar for ‘-‘ records.
I’m thinking something like this could be used as the test.
How do I put this into play, I’m thinking of an IF or CASE but not sure how to best test and which program flow to use in SQL.
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Hellow Folks. Here is the Original Data in my single SQL 2005 Table: Department: Sells: 1 Meat 1 Rice 1 Orange 2 Orange 2 Apple 3 Pears The Data I would like read separated by Semi-colon: Department: Sells: 1 Meat;Rice;Orange 2 Orange;Apple 3 Pears I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange ON dbo.T_PP_Table_2 FOR update AS IF (UPDATE([Start Dt]) OR Update(shift) or Update (comments) or Update(status) or Update (line)) BEGIN
SELECT ins.[PP Ord No], del.line,ins.line, del.shift,ins.shift, del.comments,ins.comments, del.status, ins.status, getdate(), suser_sname(suser_sid()), del.[Start Dt], ins.[Start Dt] FROM inserted ins, deleted del WHERE ins.[PP Ord No]=del.[PP Ord No] AND ins.autonumber=del.autonumber AND (ins.status <> del.status OR ins.[Start Dt] <> del.[Start Dt] or ins.status <> del.status or ins.comments <> del.comments or ins.line <> del.line ) END
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
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.
Approximately once a month, a client of ours sends us a bunch ofcomma-delimited text files which I have to clean up and then importinto their MS SQL database. All last week, I was using a Cold Fusionscript to upload the cleaned up files and then import the records theycontained into the database, though obviously, the process tookfriggin' forever, and could have been done 500x quicker had I done itdirectly on the server. My SQL knowledge is somewhat limited, however,so I had no choice but to stick to what I know, which is Cold Fusionprogramming.In the process of cleaning up some of these comma-delimited text files,I inadvertently messed up some of the 10-digit zip codes, by applyingthe wrong Excel formula to the ZIP columns. These records were importedinto the database with obviously incorrect zip codes (ie: singledigit). So now, I have to find the best and quickest way possible tocompare these records in the database (that have the single digit zipcodes) with the unmodified data, and to update the zip codes with thecorrect data.I've had no luck setting up a TEXT file as an ODBC datasource, -- soI've ruled that out completely. I've also managed to import theunmodified data into an Access database, and to set it up as a ColdFusion datasource. But it seems this 2nd road I've been traveling downis not the ideal approach either.My question is, -- assuming that I'll be able to import the recordsfrom the Access database into their own table on the SQL server, -- howshould I go about the process of updating these records that have theincorrect zip codes?Here is the specific logic I would need to employ:* Here is a list of records, each of which contains an incorrect1-digit zip code (Database A / Table A)* Here is a much longer list of records (which contains all of therecords from Database A / Table A + thousands more), each of whichcontains a correct 5-digit zip code (Database B / Table B)* Compare both lists of records and run the following query/update:When a record in Database A / Table A has matching "name", "address1",and "address2" values as a record in Database B / Table B -- update therecord in Database B / Table B with the zip code from the matchingrecord in Database A / Table A.Would anyone care to write a sample query for me that I could rundirectly on the SQL server, or at least give me some pointers?The specific field names are as follows:name,address1,address2,city,state,zipThanks in advance!- yvan
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
No error is thrown, but the update is not made? Possibly due to the dreaded inline sql being used? The data structure for these 2 servers is horrific (but that is a story in itself). The current structure (which needs immediate change) is each employee all 10 of them, have their own database. If the userid is 6 or higher they are on server2 if the userid is 5 or below they are on server1. Then they each have their own table that stores the data. (A story for another day, but def needs overhaul). However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed? Or we can scratch the update statements entirely if we can get the correct counts needed.
I hope you can help me. I posted this in the microsoft sql server newsgroupa few days ago and got no response so I thought I'd try here. If I canprovide any clarification I'll be glad to do so.I'm trying to calculate a column based on the value of the previous record.I'm not very experienced with SQL-Server.I'm using the following table:CREATE TABLE tblPayment([PaymentID] [int] IDENTITY (1, 1) NOT NULL ,[LoanID] [int] NULL ,[PaymentPeriod] [int] NULL ,[PaymentRecDate] [datetime] NULL ,[PaymentAMT] [money] NULL)I have a view based on this table. That view has the following calculatedcolumnsBeginningBalance: For the first record, this is equal to the loan amountfrom the loan table. For each additional record this is equal to the endingbalance from the previous payment record.Interest: BeginningBalance * the monthly interest rate from the loantablePrincipal: PaymentAMT - InterestEndingBalance: BeginningBalance - PrincipalIt might seem I could use a subquery to calculate the Beginning Balance asin:SELECT LoanID, PaymentPeriod, PaymentAMT,(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <tblPayment.PaymentPeriod) AS BeginBalanceFROM tblPaymentWHERE (LoanID = @LoanID)But this will not work, because the interest is calculated on the previousmonth's balance. I need to find a way to loop through the recordset. Isthis possible?Thank you,--Derek CooperDatabase9www.database9.com
Hello Everyone. I'm trying to set a procedural referential integrity on a table, files, which references to table users (a file is created by a user but one user can have more than one file). Here, given that I can't create a referential integrity ON DELETE SET NULL (the reason il long to explain so just don't care about it), I would like to emulate the RI with a trigger.
On deleting a user the files associated to him have their IDUser set to null. I have a problem, this is the trigger:
CREATE TRIGGER PRI_FilesOnUsers
ON Files
INSTEAD OF DELETE
AS
-- When a user is deleted the files must have no user associated, set IDUser to null
UPDATE Files
SET IDUser = NULL
WHERE IDUser = ?????????????????????????????????????????????????????
GO
WELL what do I heve to put there, how can i retrieve the values of the fields of the record that the user tried to delete?????Help me thanks
When i am trying to update the data inside the ' sys.sql_modules' view, i am facing the following error:- 'Ad hoc updates to system catalogs are not allowed.' Is there any alternate way to update the data inside the ' sys.sql_modules' view?
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
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?
-- 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
-- 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,
-- 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,
-- 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,
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,
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 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 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 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 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 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 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 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 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 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 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))
-- 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
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
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
We are developing a database in SQL and we are trialing some of our typical analysis undertaken on out dataset.
I have a problem with a update function. ID direction Holiday Lat Long Speed obstime - Datestamp LicenseID - varchar(7) status - int (0 or 1) O-Unoccipied, 1-occupied Pickup - Boolean Dropoff - Boolean
I am trying to update the 'Pickup' or 'Dropoff' when the status changes from 0 to 1 or from 1 to 0 if the difference in the datestamp is less than 2 minutes. Pickup is when the status goes from 0 to 1 Drop off is when the status goes from 1 to 0
We are currently developing a crm that has the following main tables: client, sales, actions, contacts and employees with up to 4 million records. The db contains several other tables with extra information but these aren't relevant for this problem. Clients have sales, contacts and actions. sales and actions can have seperate contacts as well, and contacts can be related to employees in a way.
Offcourse a security model was implemented but this only provides the option to hide or show all the records of these main tables. Now people noticed that it is a must to let people see for example only companies they created or do sales with. In other words access must be given at a record level.
The first thing that pops in my head is to create a client_security, sales_security, ... table that contains the employee_id and client_id or sales_id, or ... And add a join with one of these security tables for each select that is executed.
I wonder (because of the huge amount of data) if there isn't a better way to get this done?
Another possible issue that came to mind was, that when some data doesn't need protection or some people have the rigth to see everything, we would have to create the records in the security table anyways, resulting in probably hundreds of thousands of records.
Feedback on our solution (if we can call it that allready), or even keywords to find more about this issue via google (record based security really isn't the best description I guess) are most appreciated.
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
Hi all - this one has me stumped... PLEASE HELP!!!
I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER).
<cfquery name="gallHomePic1st" datasource="id" maxrows="1"> SELECT id FROM gall_home ORDER BY gall_order asc </cfquery>
For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc.
Technology Type Size XYZ A 200 XYZ1 A 200 XYZ2 A 300 XYZ3 A 300 ABC1 X 238 ABC2 X 238 PQR B 320 MNO C 330
I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.
Now I need to concatenate the Technology based on Type and size.
As you can see in Type A we have two sizes 200 and 300.
We need to group the Technology of type A with same size together.
So the output of the procedure should be
XYZ + XYZ1 XYZ2+ XYZ3 ABC1 + ABC2 etc.
We need to concatenate the Technology string with the next technology if they have the same type and size.
Hi People, hope someone can help me out here with a little problem. Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58" Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control Dim except_time As DateTime except_time = DropDownList1.SelectedValue The expect_time is then passed to store procedure along with some other vars, which looks like this -- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match. can anyone see any silly mistakes i'm doing here ????
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id Having sr.spec_checklist_id = 30 and count(*)>1 order by sr.linked_to_worknumber
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours], wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields: OrderID (FK, not unique) InstallationDate (Datetime) CreateDtTm (Datetime)
There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.
For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:
SELECT a.OrderID, InstallationDate
FROM ScheduleDateLog a
INNER JOIN
(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord
FROM ScheduleDateLog GROUP BY OrderID) as b
ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord
