I have a registration plate. It has an expiry date. And if I do not renew it within the 60 days it should go inactive else it should be active.
How can I use SSIS/SQL to implement this logic.
Please let me know Il really appreciate
Thanks
Hi,
Do you have any suggestions of how to transform the below code in SSIS?
I guess we should use foreach loop container...... also i would appreciate if you can post some related articles.
Dear All,
I have a table A with a KEY column and SSN column.
KEY = 12 digits ( first 3 digits are Department Id , and last 9 digits are SSN)
I have a table B with SSN column only.
both KEY and SSN columns are Primary keys so duplicate entries must be Avoided.
Table A is intended to be popluated weekly from TXT file (SSIS package RUN). I want to achieve somethign like this..!
P-Code sample:
for each Row in TXT file
if TXTfile.KEY = TableA.KEY then
skip and Read/Go to next Row in TXT file
else
INSERT TXTfile.KEY into TABLEA.KEY
SSN_Var = EXTRACT the SSN part (SSNpart.READ)
if SSN_VAR.Exists In TableB.AnyRow then
skip
else
Insert into TableB
End If.
End If
End For Loop.
-----------------------------------------------------------------
Using SSIS controls, what will be best flow and logic to achieve this.....?
any sample scripting code ????
Many Thanks.
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.
Any thoughts on how I can do this?
tbl_one hv 8mil rows, tbl_2 have 8k rows...
if
select count(*) from tbl_one
where sub_col1 = 2
return 3mil rows
and
select count(*) from tbl_2
where ad_col1 = '000009'
return 4k rows
HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')
return more than 12 billion rowss?? helpp..
I have to write trigger to relate two table.
If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.
How this can be done, do we need to point the common key fields in both table while inserting
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
Thank-you
I have a logic problem,
I am selecting from a table
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.
Thanks in advance.
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.
--CREATE PROCEDURE rpt_siteMealList(
-- (@cmb1 AS VARCHAR(100)) WITH ENCRYPTION
--)
--
--AS
DECLARE @dtm1 AS DATETIME
DECLARE @mybit AS INTEGER
SET @mybit=10
SELECT @mybit = CASE datepart(dw,@dtm1)
WHEN 1 THEN 1 -- 'Sunday'
WHEN 2 THEN 2 -- 'Monday'
WHEN 3 THEN 4 -- 'Tuesday'
WHEN 4 THEN 8 -- 'Wednesday'
WHEN 5 THEN 16 -- 'Thursday'
WHEN 6 THEN 32 -- 'Friday'
WHEN 7 THEN 64 -- 'Saturday'
END
SELECT br FROM simplehoursassignment
IF @mybit AND br > 0
BEGIN
CASE WHEN @mybit = 1 THEN 'Sunday'
WHEN @mybit = 2 THEN 'Monday'
WHEN @mybit = 3 THEN 'Tuesday'
WHEN @mybit = 4 THEN 'Wednesday'
WHEN @mybit = 5 THEN 'Thursday'
WHEN @mybit = 6 THEN 'Friday'
WHEN @mybit = 7 THEN 'Saturday'
END
END
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
Thanks
K
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?
View 4 Replies View RelatedI'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!
Ta
I need to create a view joining 2 tables(1st with data, 2nd is a lookup) but I need to have some
logic(IF/ ELSE,ELSE and an UPDATE).
What is the best way?
-aw
okay;
i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.
tblSlots:
PKSlotINDEX
datStartTime
datEndTime
then i have a table called tblPersons
tblPersons:
PKPersonINDEX
txtLast
txtFirst
then i have a table called tblSchedule
tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX
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.
thanks!
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.
Example:
empID PlanIDgroupID
11012
PlanIDGroupIDEffectiveDate TerminationdateDeadlineDate
101208/01/200508/31/200508/15/2005
111209/01/200509/31/200509/15/2005
91208/01/200508/31/200508/15/2005
If I run it today I should not get any results back. If I run in
on 8/15/2005 I should get back data with palnID11.
Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...
Zip Code OR City State
By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.
The Psuedo code is somethinglike this...
SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @This ZipCode
OR
(In @State AND @City)
-------------------
Table Dealers
-------------------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
-------------------
-------------------
Table ZipCodes
-------------------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
-------------------
I have achieved this with Joins but the query takes 2+ minutes to execute
What am I doing wrong??? I am sure there is a better approach to this!
Thanks,
--Lito
Hi,
This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.
The table:
CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)
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?
Hi pundits,
I need a logic for solving a realtime issue.
I have series 2^0,2^1,2^2,.......2^n ( i.e 1,2,4,8,16.....2^n)
I want to write a function(algorithm) which returns all the terms used(added) to form the number
e.g fun(7) :-1,2,4
fun(5) :-1,4
fun(257):-1,256
fun(6):-2,4
Ur help is highly appreciated.
Thanks in advance.
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
tblModelPart
ModelPartId ModelId PartId
---------------------------------
1 1 1
2 2 1
3 2 3
4 3 1
5 3 2
6 3 3
... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...
1. Return all models that contain only the parts selected.
ModelId ModelName
---------------------
2 Bravo
2. Return all models that contain the parts selected, and may contain other parts.
ModelId ModelName
---------------------
2 Bravo
3 Bravo Pro
... so do you have any idea on how the SQL would look for either of these queries?
Thanks in advance,
Goran
I need to do something like the following logic: (What I am trying to do is a weight conversion statement)
select
part,
desc,
net_wt,
net_wt_um,
pack_um,
pack_wt,
@somevariable = net_wt / (if net_wt_um <> pack_um then
(if exist(select conversion)
from table1
where field2 = pack_um)
then pack_wt / conversion
else pack_wt
Any thougts? I am basically just looking for a way to do if/else in select statements.
Thanks,
Scott
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
View 1 Replies View RelatedBelow 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.
episode_key
chart_component_key
deficiency_type
deficiency_status
status_date
13789881
173398
408
C
8/4/2007
13789881
173488
409
S
8/4/2007
13789881
173703
409
S
8/6/2007
13789881
174568
1028
S
8/7/2007
13789881
176213
421
S
8/9/2007
13789881
176214
421
S
8/9/2007
13789881
176215
421
S
8/9/2007
13789881
176216
421
S
8/9/2007
13789881
176218
421
S
8/9/2007
13789881
176219
406
D
8/9/2007
Can someone help me here?
I have 2 basic requirements as below,
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.
Thanking you,
I have a Rule table that looks like this.
Rule_ID (PK)
vMin
vMax
vName
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.
This is Correct
Rule 1
vMin = 10, vMax = 20
Rule 2 Correct
vMin = 15, vMax = 18
Rule3-Incorrect
vMin = 8, vMax = 12
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.
how can we get the logic of this code to work
Please help
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.
Thanks in advance for any 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!
View 3 Replies View RelatedI have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur. I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.TIA for any opinions, options, etc. Tom
View 2 Replies View Related