Hi, sorry for another newbie question but I was wondering if the following is possible:
I have to take some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
I have 2 tables: First table: empID,PlanID,groupID Second: PlanID,groupID,EffectiveDate,TerminationDate,DeadlineDate I need to show only employee with in spesific group who is not enroll for the current month until deadline passed.
I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...
tblModel tblPart ModelId ModelName PartId PartName ---------------------- ---------------------- 1 Alpha 1 CHOO1 Stem 2 Bravo 2 BH034 Rod 3 Bravo Pro 3 HRE Seat
I have two tables X,Y X empno....Sal.....Tax.....Returns...name 1.....4500....1050.... 750.......robert 2.....5750.....1560....900.......john 3.....4000.....900.....600.......keen 4.....6100....1200.....1000......stauton
Y empno....Sal.....Tax.....Returns...name 1.....4500....1000.... 000.......robert 2.....5750.....1200....900.......john 3.....4000.....900.....600.......keen 4.....6100....1000.....1000.......stauton
If you see the above tables I have data mismatch in X and Y tables for the same empno. I need to write a query which shows emp no and columns(name of col) where the data mismatch has occured. I came up with a query which I have to write for every individual column to get the mismatch. Since there 120 columns it is pretty hard task..i m looking for a logic where I can write a query which shows mismatched data in columns.
Expected Output table z col1..col2 1......tax 3......tax Appreciate your help.
Hi, sorry for a newbie question but I was wondering if the following is possible:
I have to select some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_idI'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.SELECT DISTINCT times.employee_id, employee.employee_nameFROM employee INNER JOIN times ON employee.employee_id = times.employee_id WHERE (times.job_id <> @job_id) Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!
Hi All I have a sqlserver database with product, catagory and sub catagory format. Before I describe my problem, let me share whats I have in db. Their are two types of sinaros, either the products are directly assigned to a catagory or a product is placed in subcatagory that is in turn have a catagory. I use the following table struct for both of the scenarios: Product>>subcat bridge>>subcatagory Product>>catbridge>>catagory Here are the queries to get them: 1. If product assinged direct in catagory then select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,cat_bridge,category where product.id=cat_bridge.pid and cat_bridge.catid=category.catid; 2. If product assinged to sub cat then select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,subcat_bridge,subcategory where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid; Now the problem is, I want to use a single query to download all the products to a CSV format and I need to combine both of the queries with a single one, probably with if else logic, but I am not getting it, I mean how to acheive. Can anyone help me sort this out? Thanks in Advance Regards Mykhan
I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.
I used an update query that looks like this:
UPDATE Acct_table Set Acct_table.Score = (Select Score_tbl.Score from Score_tbl Where Acct_table.Acctnb = Score_tbl.Acctnb
This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
Hello, I have a requirement to select millions of rows from table and need do some parsing each row. I have identity column on each table. Here is the query logic I'm following. Logic A:Uisng While loop processing data row by row Logic B: Using Cursor Processing row by row
Here is the perormance on the above ran against .5 millions rows of data.
like this i need to populate. i need to show all the groupname and belonging systemName and belonging devicename and belonging sensorname
so please give me query for this complex operation please criteria's 1.GRoup can have systems and system can have devices and device can have sensors 2.GRoup can have systems and systems can have sensors[no device] 3.GRoup can have systems and systems can have devices [no sensor] 4.GRoup can only have system [no device, no sensor] 5.GRoup can have only sensor[no system, no device] so please give me query for this. not stored procedures.i need query for this
I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.
Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.
Here is some example code from my program: sub Test{ foreach my $table(keys %$HoH){ foreach my $field(keys %{$HoH->{$table}}){ if($table eq "CPU"){ my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName) Values ('$field', '$HoH->{CPU}{$field}{Name}', '$HoH->{CPU}{$field}{MaxClockSpeed}' , '$HoH->{Host}{SystemNetName}')"; print "$CPUstatement"; if ($db->Sql($CPUstatement)) { print "Error on SQL Statement"; Win32::ODBC::DumpError(); } else { print "successful"; } } }
have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.
For a very general example: In the .NET code, the following variables are recognized: sex="M" Paid=4350.00 Outstanding=28000.50
One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..
EVAL EXPR BOOL Sex='F' and Paid/Outstanding < 27.50 BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F' INT Paid*52.33
In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?
If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....
I am a bit of a novice to SQL Server programming. So, forgive me if my question is too silly or easy.
I have an client-server application where the server connects to an SQL Server and does simple DB operations. In a particular test case, after my SQL Server is shut down and restarted when I try to execute an SQL Statement, the SQLExecute function fails. The problem is that the connection handle used to allocate and prepare the statement is invalid. So, I need to reconnect to the server.
Now my question is, how do I determine if the connection handle is valid or not ? That is, is there any API/function which when used can detect whether the connection handle is invalid or not.
I have tried calls to SQLGetConnectAttr and SQLGetInfo passing the connection handle, but these API's still return the correct value even if the handle is invalid.
I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following: WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address. The hiccup I have is with a friendly URL such as the following: /blog/2005/10/6 The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.) What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this: ~/blog.aspx?parameters=2005/10/6 While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server? My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
It is a initialization of the two dimensional array DETAILED-VOUCHER(Temporary Variable) which consists of the account number for that account number different group of people say as (teachers, state police etc) have different 12 kinds of amounts such as (DEATH-AMT, PAYEE-AMTetc ) for every account number. SUB (Temporary Variable) is used as subscript whose value will be incremented by 1 after initializing current position by zero in order to initialize all 12 positions in array.
LOP-ZERO-2. MOVE ZEROS TO CNT-V (1 SUB). MOVE ZEROS TO AMT-V (1 SUB). ADD 1 TO SUB. IF SUB NOT GREATER THAN 12 GO TO LOP-ZERO-2.
MOVE 2 TO SUB.
LOP-ZERO-3. MOVE CNTS (1) TO CNTS (sub). ADD 1 TO SUB. IF SUB NOT > 13 GO TO LOP-ZERO-3.
MOVE '401.21' TO ACC-NO-V (1). ---------- This are the hotcode values MOVE '410.01' TO ACC-NO-V (2). MOVE '410.02' TO ACC-NO-V (3). MOVE '410.03' TO ACC-NO-V (4). MOVE '410.04' TO ACC-NO-V (5). MOVE '410.06' TO ACC-NO-V (6). MOVE '410.05' TO ACC-NO-V (7). MOVE '411.01' TO ACC-NO-V (8). MOVE '411.02' TO ACC-NO-V (9). MOVE '331.01' TO ACC-NO-V (10). MOVE '212.19' TO ACC-NO-V (11). MOVE '212.22' TO ACC-NO-V (12).
MOVE ZEROS TO XYZ-S (1). MOVE ZEROS TO MNO-B (1). MOVE ALL '9' TO A-KEY MOVE 01 TO D-X. MOVE 1 TO SYS-Y
Please give suggest me how to write logic in the sql server. I want to how to write two dimensional array loop in the sql server.
I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
This is the code for calculating a formula field in Crystal Reports. I want to implement the same in Sql Server Reporting Services.. But it doesn't have the feature of For Loop.... The strings started with @ are formula fields.... Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR; for YR := 1 to {@CalcFiscalAge} step 1 do ( IF YR = {@CalcLifeCode} +1 then locFactor1 := 1; exit for; IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN HALF_YEAR := 2 ELSE HALF_YEAR := 1 ; LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4); MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4); IF MACR >= LINEAR then DEP := MACR ELSE DEP := LINEAR; locFactor1 := locFactor1 + DEP ; REM := 1 - locFactor1; locFactor := locFactor1; );
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
Pocket PC 2003, SQL Compact Edition, SQL2005, IIS6.0
I implemented a business logic handler to deal with conflicts. When I deploy it on the SQL server which is also the web replication server, the logic handler seems working fine. However, if I deploy this handler to another web server. The logic handler failed to be loaded.
My enviroment settings are desribed as below.
Machine A, distributor, with database and publication. The business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll. It's registered by using sp_registercustomresolver. The @assembly is specified as @assembly=C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll';
Machine B, IIS server. The same business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll on the Machine B itself.
When I ran the web replication, the Merge Agent reported the error as below.
Error loading custom assembly "C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll", Error: "Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll' or one of its dependencies. The given assembly name or codebase was invalid.
It seemed that the Merge Agent had trouble to find my logic handler because the path reported in the error log has two backward slashes. I have no idea where did that came from. I am not sure if that's the cause of the error. Without business logic handler. I had successfully finished web replication of Machine B to sync with Machine A. If I setup web replication directly on Machine A with business logic handler, I can successfully sync as well.
Does anyone has any idea about how to correctly deploy business logic handler on a web server?
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy, CASE WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female' WHEN titleofcourtesy = 'Mr.' THEN 'Male' ELSE 'Unknown' END AS gender FROM HR.Employees; GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender ( @male AS VARCHAR(10), @female AS VARCHAR(10), @unknown AS VARCHAR(10) ) RETURNS VARCHAR(10)
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
Can anyone see where my logic may have gone a stray? A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150. I want to then tell them they cannot do this. @AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND
Trying to bind some data to a datalist for a report.
User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.
If FIELD_ONE = "A" then I get the NAME from TABLE_ONE. If FIELD_ONE = "B" then I get the NAME from TABLE_TWO. If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.
I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.
What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.
This is what i came up with, will it evaluate the way i need it to?
case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV
Select * from TAB_A where state not in (1,4) and create_date < '2008-01-01' and ( (x != 2 and y != 1 and z != 4) or (x != 6 and y != 3 and z != 1) or (x != 8 and y != 0 and z != 9) )
then for example i am getting results where x,y and z is equal to one or more of above combinations ?
Now i vaguely remember that using or with a != messes up the logic ? if so can i use an NOR ? does it exist ?
I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.
Table 1 has columns A and B (tblemployee has location and employeenumber ) Table 2 has columns C and A (tblocation has locationID (identity) and location) Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)
I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.
