I am just getting started with the integration between SQL Server and ASP.NET 2. I am at a loss of how to move data in and out.
I need to track a vehicle inventory, properties to be track are, year, make, and model, color and so on. These have not presented me with much of a problem; however the vehicle options have left me totally confused.
I have an Options table and a Vehicle table. The Options table has a numeric primary key OptionsID, and a field for the option. The Vehicle table has a numeric primary key, VehicleID. I have tried using a join table named VehicleOptions with two fields, VehicleID and OptionsID. I have tried a join table (VehicleOptions) with a field for the VehicleID and separate fields for every OptionsID, inputting true or false, as needed.
I have tried passing a string with each OptionID separated with a comma, to a stored procedure, parsing the string, looping through setting the value to true or false as needed.
I have also tried matching the VehicleID with the OptionsID in the code for the web page then passing this to a separate stored procedure.
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
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 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.
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
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.
I am having a little problem with my logic. i have a table simplehoursassignment that has a field named br. i need to get the value from simplehoursassigment & say if @mybit AND br > 0 set each day.
I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote. Does anyone know what the problem is?
Code Snippet SELECT s.* FROM Quote q INNER JOIN SubQuote s ON q.id = s.quoteID INNER JOIN TakeOffSheetItem t ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid INNER JOIN PipeGroup p ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID WHERE (q.id = 1 AND q.deleted = 0 AND s.deleted = 0 AND t.deleted_takeoffitem = 0 AND p.deleted_PipeGroup = 0) ORDER BY s.subquoteid
I am trying to figure out how to set up this database. Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory) Thanks,Mick
Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?
I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number. Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a trivial task.
Hi there! Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic. Overview: I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well. Layout for a magazine will look something like this: Size Trim Type Bleed Full Page 280x440 270x430 290x450 Half Page 140x220 130x210 150x230 etc... Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed. Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.
Break it down! Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220). I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!
The Problem. The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys. I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.
Any ideas on how i could manage this?
If you made it tis far through my question then thanks anyways! I hope you can help me out!
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
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.
The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".
I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then: if [q5]= 'Happy' then change it to 'Unhappy' if [q5]= 'Unhappy' then change it to 'Happy'
The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?
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.
Declare @mx intSet @mx = (select max(score)from ,,,where ,,,,andsubstring(city,1,1)='g')select score,@mxfrom ,,,,where ,,,, andsubstring(city,1,1)='g')group by scoregothis seems to work for small tablesbut when table is big and substring(city,1,1)='g')condition is not met it takes aweful time like goesin an infinit loop.--Sent by 3 from yahoo part of comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com
Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.
1) Comparing 2 versions of same database. 2) Keeping track of schema versions of Tables & Stored Procedures so that I can rollback to previous schema.
I have started with Sys.Objects, Sys.Tables & Sys.Columns and I kept things rely on Object_ID but the problem started whenever I change some part of Table. It assigns new Object_ID to the table whenever I change it; moreover creationdate of object is also changed. So I assume that it drops and creates new table.
Please throw some light on following questions, 1) When and How Object_ID Changes? 2) Is there any other thing in database which is Unique & Not Changing with alteration of object? 3) What should be the preferred way to Compare 2 Database?
I hate readymade tools as they charge per user and my requirements are very small.
I would be recieving the vName as an input. I need to go through the table to make sure that the rules (Min and Max) are not intermingeled. Rules can be within another rule but can not cross.
Every time the SP is fired I need to be able to check if this rule is valid or not by comparing it to all the rules in the table. if it is not valid I need to fire an error back.
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?
