1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
FOR XML Statement I should use to convert data in the below table to generate xml in the given format? Can it be done with a single statement?
Event | Condition | Group |Item |Answer ======================================= New New GroupA Item1 Y New New GroupA Item2 Y New New GroupA Item3 N New New GroupB Item6 N New New GroupB Item10 Y New New GroupB Item30 N
I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.
Code:
DECLARE @myval varchar(50),@username varchar(50) DECLARE @rolelist varchar(2000) SET @rolelist = '' SET @myval = 'user a,user b' select @myval = ''''+ replace(@myval,',',''',''') + '''' print @myval
DECLARE User_Cursor CURSOR FOR select distinct eusername from euser where eusername in (@myval)
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @username
SET @myval = @username SET @rolelist = @username WHILE @@FETCH_STATUS = 0 BEGIN SET @rolelist =+ @rolelist + ',' + @username
FETCH NEXT FROM User_Cursor INTO @username END CLOSE User_Cursor DEALLOCATE User_Cursor print @myval print 'rolelist' + @rolelist GO
I am at a loss any suggestions would be greatly appreciated.
SELECT Production_Date, Production_Order, LogicalVat KPI_Category, 'Probiotic Amt Consumed' KPI_Data1_Name,'RC Amt Consumed' KPI_Data2_Name FROM ( SELECT Production_Date , NULL Production_Order, LogicalVat, ReportValue ReportValue FROM BIReports.dbo.r_VatMake WHERE Production_Date between '10/27/2015' and '10/27/2015'
[code].....
Now the attributes changed into like below where the number after RC is dynamic and I can't use the query above anymore
Example: RC Amt Consumed - RC 6 RC Amt Consumed - RC 7 RC Amt Consumed - RC 8
Probiotic attribute changed into like below where number after PROB is dynamic
Probiotic Amt Consumed - PROB 15 Probiotic Amt Consumed - PROB 16 Probiotic Amt Consumed - PROB 17
I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
I have a stored procedure that runs (SQL Server 2012 (SP1) Standard Ed) daily and I never had any problem with this stored procedure. However, there is MERGE statement on the stored procedure and I see an error saying that the MERGE statement failed..Here are the stored procedure and error message:
-- FlushQueue CREATE PROCEDURE [dbo].[FlushQueue] (@RowCount as int = 10000) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON;
[code]....
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672). The step failed.
Table definition:---CREATE TABLE [dbo].[ImportDefinitions] ( [NodeName] [varchar](20) NOT NULL, [ProcedureName] [varchar](100) NOT NULL, [FilePrefix] [varchar](20) NOT NULL, [ImportDelay] [int] NOT NULL CONSTRAINT [DF_ImportDefinitions_ImportDelay] DEFAULT ((0)),
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Display customized data based on customized where statement from UDT.
The UDT is a parameter inside of a stored procedure.
Problem:
A parameter from a stored procedure is @communication communications readonly
This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.
Four example of filtration critera based on four end-users' selection that is located inside of a table below.
Each UDT table contains different criteria:
Number Criteria ------ -------- 1 Phone 3 Email
Number Criteria ------ -------- 1 Phone 2 Cellphone 3 email
Number Criteria ------ -------- 4 None
Number Criteria ------ -------- 2 Cellphone 1 Phone
Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.
I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below
select * from datatable where id = 1 and name = 'Cost'
How should I do it?
[URL] .....
create table datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) );
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276 Incorrect syntax near ';'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 351 Incorrect syntax near the keyword 'as'.
How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.
Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10)) insert into #seq values (1,1002,'10/10/2015','SEDT') insert into #seq values (2,1005,'12/10/2015','PEFT') insert into #seq values (3,1004,'1/1/2015','QEGT') insert into #seq values (4,1008,'1/2/2015','TEWT') insert into #seq values (5,1007,'3/10/2015','REDT')
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select case when @include_browser = 1 then browser_name end as browser_name, case when @include_browser_version = 1 then browser_version end as browser_version,
I want to run an update command where the user types in a CSV value and the query runs. If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail).
declare @SITE_ID int declare @txtSchedule varchar (500) set @SITE_ID=1 set @txtSchedule='5,6' select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule .
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST PH00059 PH02775 PH03051 PH03305 PH03336 PH03342 PH03371 PH03992 PH03993 PH03994
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided. The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access. So I need to divide them out, and put them in one column for AccessLevel.
JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.
Case JobCode (they all have same job code, but everyone in this job code should not have same access) When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors) When '25' Then '2' (this could be teacher, etc. ) Case JobTitle (this is how access should be) When '12345' Then '1' (this is Administration only)
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm Set to: Post Action: results.asp Text Field: Keyword Drop Down: CategoryTable Drop Down: Location UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType FROM Query1 WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail' ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults idSearch (AutoNumber) location (Text) "Want to insert the 'Location' result here" category (Text) "Want to insert the 'CategoryTable' result here" user (Text) "Want to insert the UserName Session Variable result here" result (Text) "Want to insert the 'Keyword' result here"
I am trying to run the below but I get an error of 'Incorrect syntax ')'' --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg SET [Date] = GETDate(), [readytogo] = ( CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view]) THEN 'Yes' ELSE 'Needs Verification'
Im working on Partition purge process, where I need to specify following statement:
SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)' EXEC (@cmd); SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file EXEC (cmd1);
I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed. "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure.
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS Select M.CUSTNMBR, M.ADRSCode, M.Contract_number, M.MaxWSCONTSQ, M.Equipment_id,
In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:
Merge TST.dbo.LockCombination AS LKC1 USING (select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?
I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...
Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....
MERGE INTO [dbo].[TargetCustomer] AS TRG USING [dbo].[MyCustomers] AS SRC ON TRG.[CustomerID] = SRC.[CustomerID] AND TRG.[CustomerName]=SRC.[CustomerName] AND TRG.[City]=SRC.[City]
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
SET NOCOUNT ON DECLARE @RowCount int; SET @RowCount = 0; Begin Try Begin Transaction -------------------------------------------------------- -----Table Name: AlertsStaticRecord_Archive -----Column Name: AlertID -------------------------------------------------------- ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11 Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'. Msg 1750, Level 16, State 0, Line 11 Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume) WHEN c.TYPE = 'DEF' THEN (c.volume) WHEN c.Type = 'GHI' THEN (c.volume) Else 0 END), CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31' WHEN c.Type = 'ABC' THEN '123.1' WHEN c.Type = 'DEF' THEN '234.2' WHEN c.Type = 'GHI' THEN '567.1' END from table1 a join table2 b on a.Cust = b.Customer join table3 c on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?
Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,
