I have something like this
SportInfoTable
SportID UserID CurrentlyRegisteredOnSport
1 1 False
2 1 True
2 2 True
3 3 True
4 1 False
SportTable
ID NameOfSport
1 Football
2 Baseball
3 Tennis
4 Squash
5 Hockey
6 Basketball
Im trying to write an SP that will get all the sports where "currentlRegisteredOnSport" is false
and all the sports where the user has not registered on it. i.e userid for a particular sport is not mentioned in SportInfoTable
(I will be passing the userid as a parameter)
so for example for userID 1 i want it to return from SportTable:
I have a select statement with a where clause which needs to compare the value of a column to the result of a stored procedure (which returns an integer). Something like the following (except that works!) SELECT * FROM MyTableWHERE CASE WHEN someExpression THEN CASE WHEN MyColumn = EXEC MyStoredProcedure @MyParam THEN 1 END END=1 Thanks!
Hi everyone, I am a relative newbie to SQL and trying to do things via the self-taught method. I really have an issue that I am just unsure of what to do. I am working with a procedure that is for emailing invoices to customers. There can only be one account for a primary email address. From time to time a user will assign a second account to the email address. When the process runs, it sees the error and will not continue processing the remaining records. Any suggestions as to what I might be able to do? I need to have it so that the remaining records will process. (I think it may be important to note this is an application on a company intranet site.) Thanks for any help you can provide.
I have created a simple trigger that updates a table called documents in my database. When a row has been inserted it will change the field form_type to 'cabinet A' if a document_type is equal to 'form'.
Im fairly new to triggers and I€™m uncertain whether this trigger is updating the whole table or the new row? I only want the new row to be updated as the table is fairly large.
create trigger file_documents on documents_table after insert as begin update documents_table set cabinet = 'Cabinet A' where document_type = 'FORM' end
I am working on a coupon redemption project that appears to require dynamic data validation and need some help. For example, a coupon could require a person to buy XXX number of XXX items from XXX manufacture before the transaction is approved. The logical operator between each validation could also be “AND� and “OR�. My first attempt at this has been something along the lines of creating a coupon table with a child table of validation logic. However, being able to apply this logic (and at what layer) has turned out to be a challenge. I am very concerned about speed as all requirements have to be met before a transaction is approved. Any help with trying to figure out the best solution for this would be great@!
Coupons C_ID C_DESC MFG_ID MFG_OFFERNUM
Coupon_Validation CV_ID C_ID MFG_ID (Manufacture) MFG_PARTNUM (Manufacture Part Number) CV_QTYREQUIRED (Number to meet requirements) CV_ITEMLOGIC (AND, OR)
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths AS ( SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate FROM ( VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
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?
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.
