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
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
This concerns eligibility healthcare information. A member can have multiple rows in the table showing they are eligible for different date ranges with different health plans. eff_date and term_date are the fields in this table. Term_date can be NULL.
I need a WHERE statement that shows members 1) eligible between 1/1/2004 and 8/15/2004
(term_date is null or term_date >= '8/15/2004') and eff_date <='1/1/2004'
2) members are allowed only one gap in this timeframe of up to 45 days. *NEED HELP
3) a gap of 1 day should not be counted as a gap in enrollment. *NEED HELP
I have a sql table, over 30 milion recs, with the following fields: (id1 int, id2 int, itemsCollection varchar(100), myText TEXT) I have also sql table, with the following fields: (item varchar(10), rate int)
I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText The output need to be ordered as: - Get id1 & id2 with the bigest rate - output all the recs for the id1 & 2, ordered by rate (sub order) eg Main table: id1 id2 itemsCollection myText 1 1 'a,b' 'count-11-a,B - max = 15 additional txt' 1 1 'a,b' 'count-11-a-B - max = 15' 1 1 '' 'count-11' 1 1 'a,c' 'count-11-a,C - max = 20' 2 8 'c,d' 'count-28-C-d - max = 20 additional txt' 2 8 'c,d' 'count-28-C-d - max = 20' 2 8 'd' 'count-28-D - max = 5' 3 2 'a,d' 'count-32-A-d - max = 10' 3 2 '' 'count-32'
Rates table: item rate a 10 b 15 c 20 d 5 '' 0
RequestedOutput: itemsCollection item rate id1 id2 myText a,c c 20 1 1 count-11-a,C - max = 20 a,b b 15 1 1 count-11-a,B - max = 15 additional txy a,b b 15 1 1 count-11-a,B - max = 15 0 1 1 count-11 c,d c 20 2 8 count-28-C-d - max = 20 additional txt c,d c 20 2 8 count-28-C-d - max = 20 d d 5 2 8 count-28-D - max = 5 a,d a 10 3 2 count-32-A-d - max = 10 0 3 2 count-32 Thanks!
I have been working with SQL for a while...but I am stumped. I can notseem to get my arms around this query....can anyone help...Here it is:Table = 12 rows, 4 columns (id, name, amount, date)row1 = 771, "steve", $50.00, "01/01/2005"row2 = 772, "steve", $100.00, "01/11/2005"row3 = 773, "steve", $200.00, "01/11/2005"row4 = 774, "dave", $300.00, "01/01/2005"row5 = 775, "dave", $400.00, "01/12/2005"row6 = 776, "dave", $500.00, "01/12/2005"row7 = 777, "mike", $600.00, "01/01/2005"row8 = 778, "mike", $700.00, "01/13/2005"row9 = 789, "mike", $800.00, "01/13/2005"row10 = 790, "chuck", $900.00, "01/01/2005"row11 = 791, "chuck", $950.00, "01/14/2005"row12 = 792, "chuck", $975.00, "01/14/2005"I need a query that returns (1) ONE ROW PER NAME based on the MOSTRECENT DATE and returns the correct corresponding information. Thekeys to this question are the following:1. The query needs to return ONE ROW PER NAME2. I do not want to use a First() function (in MS Access)3. Even though (2) two DATE for each NAME are the same, i want thequery to return one record and whatever record it returns, i have to beable to have all the corresponding records (id, name, amount, anddate). I recorgnize that the DATE is ambiguous and that SQL may returnone or the other...but that is ok.4. The return set should include (4) four rowsAny help with this would be thoroughly appreciated...
Hello!I'm seeking advice on a rather complex type of query I need to buildin an Access ADP (SQL-Server 7). There are four tables:tblPeopleID(PK)PRENAME---------------1Thomas2Frank3ChristblInventoryClassesID(PK)INVENTORYCLASS----------------------1Car2PhonetblInventoryItemsID(PK)relInvClass(FK)ITEM-----------------------------------11Dodge Viper21Chrysler32Nokia42SamsungtblPeopleInventoryID(PK)relPeople(FK)relInvItem(FK)--------------------------------------112213321423534In this example the last table tells me thatThomas owns a Chrysler (class Car) and a Nokia (class Phone).Can someone tell me how to write a query or a stored procedure whichproduces a resultset like this:qryOwnersPeopleCarPhone-----------------------------ThomasChryslerNokiaFrankDodge ViperNokiaChris[NULL]SamsungThe main idea is that I need to be able to iterate such a collection.It is guranteed that one "People" only owns one or zero "Car" and oneor zero "Phone".I guess that it might be impossible to design a stored procedure withsuch a variable amount of columns (in this case, each item fromtblInventoryClasses would mean another column).Ary there any possibilities in accomplishing this without creatingtemporary tables?Any help would be really appreciated ;-)Greetings,Christoph Bisping
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.
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
Hi, I am trying to replicate the row level security rules in reporting service and realize the "security filter" property is a pure client side filter expression.
In our old system, we are using a set of rules to decide the row level security, something like:
if user is owner of this file or ( if user's department matches this file and the user is the department manager ) or if this user has unfinished task associated with this file or ...
So obviously it need more information than the just GetUserId(). What we have done is creating a CLR UDF as GetUserCaseList( int uid ) and returns a two column table ( case_id, case_right ).
We want to somehow inject these logics into the reporting while the user is using reportBuilder.exe, but we cannot find a way to put it into the data source view and/or the semantic data model.
I am pretty sure I am not the only guy who's having this problem, but I cannot find any hint....
i need to update for example the eployee 1111 with employee 3333 but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 in field- fld2,fld22,fld222 )
Code Snippet ---update eployee 1111 with employee 3333 -so if i put the value 2 than ------------------ swap value between 2 employee set empid1= 1111 set empid2=3333 value_swap=2 if value_swap=2 than update fld2,fld22,fld222 with fld2,fld22,fld222 ------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 --------------------in field- fld2,fld22,fld222
Code Snippet ---update eployee 2222 with employee 1111 -so if i put the value 5 than ------------------ swap value between 2 employees set empid1= 1111 set empid2=2222 value_swap=5 if value_swap=5 than update fld5,fld55,fld555 with fld5,fld55,fld555 ------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333 --------------------in field- fld5,fld55,fld555
I am new to SSIS, so I am hoping there is an easier way to do this...
I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:
IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)
Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:
here's my stored proc and i will tell you what I'm trying to do. I'm passing in a form value. It should handle anything. But it doesn't.... I have tried: ="", ='', <>"", <>'', isNull, IS NULL, IS NOT NULL and now the isNumeric() function. Can anyone help me please?!?!
DECLARE @ROWCOUNT as int select @ROWCOUNT = count(*) from tPostageProduct where productid=@ProductID
if @ROWCOUNT > 0 if isNumeric(@ScaleID) <> 1 delete from tPostageProduct where productid=@ProductID else update tPostageProduct set scaleID = @scaleID where productid=@ProductID
else if isNumeric(@ScaleID) = 1 INSERT INTO tPostageProduct (productId,scaleID) VALUES (@ProductID,@ScaleID) GO
My organization is required to ETL millions of rows of medical insurance claims data per month into our data structures from many different external systems. The data structures vary tremendously between the different source systems. We need to develop an efficient "upsert" algorithm: e.g. when a new row arrives from a particular source system determine: a: does a row with an identical unique natural key (often a composite) already exist in our system? b: if "a" above is true, is any non-key data in the newly arrived row different from the existing row with the same key? c: depending on the results of test a and b above, perform either an insert or update.
So we need to develop an internal "upsert" logic solution. We also want to have a solution that will support a generalized solution as early as possible in the data processing. Therefore, we are considering applying two identical MD5 hashing algorithms (probably C or C# extended stored procs) to each incoming source row (and a one time generation of the two hashes for all legacy loaded rows). One hash will be created from the unique natural key for each row, and one will be created from all the remaining columns in the row. Together these two MD5 hashes should allow us to perform comparisons on all newly arrived vs. previously loaded data, and reliably determine results of tests "a" and "b" above.
This approach also supports the requirement that the code that performs the upsert logic be generalized as early as possible for all different source systems, as the actual test logic will be identical for all source data and independant of the custom logic used to generate the MD5 hashes for each unique source structure.
Is there a better way? How do others approach this problem? What kind of performance impact can be expected from the xp calls?
Anyway to figure this out? This is what I have so far. Sorry, but im in access 03.
SELECT tblCAESchedule.Name, tblCAESchedule.Title, tblCAESchedule.SunStart, tblCAESchedule.SunEnd, IIf(DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])>=1,DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend]),DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])+1440)/60 AS SunHours, tblSupSchedule.Name, tblSupSchedule.SunStart, tblSupSchedule.SunEnd, Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60) AS StartDif, Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS EndDif, Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60)+Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS ShiftDif FROM tblCAESchedule INNER JOIN tblSupSchedule ON tblCAESchedule.Skill = tblSupSchedule.Skill WHERE tblCAESchedule.Skill = "Sales"
I am having difficulty to find the right SQL query to create a View as i illustrate below.
Senario:
Criteria Table
Idn
Key1
Key2
Key3
TagId
1
A
C
B
100
2
A
NULL
B
200
3
B
D
NULL
300
Data Table
DataId
Key1
1
A
2
B
SubData Table
SubDataId
DataId
Key2
Key3
1
1
C
B
2
1
Z
B
3
1
X
B
4
2
D
Z
And below is my expected View:
TagId
Key1
Key2
Key3
100
A
C
B
200
A
Z
B
200
A
X
B
300
B
D
Z
I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.
I would really appreciate if anyone could help me to make if faster or point me where i did wrong.
Below is sample T-SQL that which i can illustrate my situation:
set nocount on create table #Criteria ( idn int ,Key1 char(1) ,Key2 char(1) ,Key3 char(1) ,TagId int )
create table #Data ( DataId int ,Key1 char(1) )
create table #SubData ( SubDataId int ,DataId int ,Key2 char(1) ,Key3 char(1) )
select #Data.Key1 ,#SubData.Key2 ,#SubData.Key3 from #Data join #SubData on #Data.DataId = #SubData.DataId
/** here is the query logic i used in the view **/ select min(#Criteria.TagId) ,ConsolidatedData.Key1 ,ConsolidatedData.Key2 ,ConsolidatedData.Key3 from #Criteria left join ( select #Data.Key1 ,#SubData.Key2 ,#SubData.Key3 from #Data join #SubData on #Data.DataId = #SubData.DataId ) as ConsolidatedData on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL group by ConsolidatedData.Key1 ,ConsolidatedData.Key2 ,ConsolidatedData.Key3
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.
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'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.
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 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'
