I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
TIA
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing
code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f
I wanted to update it to
code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y
how to do it?
use of Row_Number() over ( partition... but I dont understand how.
Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1
But I wanted to return
CustomerID, PartNum, MaxQtyOrderedNotShipped
That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0
If I use this:
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1
there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0
I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc.
The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'.
I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that:
<FilterParameters>
<asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" />
</FilterParameters>
But I don't know what to do for the FilterExpressions. currently I just have:
FilterExpression="DestinationName LIKE '{0}%'"
i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
Thanks
How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.
So is there any other way to handle multiple criteria in T-SQL ?
Im sorry if this has been covered, I tried a search but I couldnt seem to find what I was after.
Anyway, I need an Update procedure which is actually several bunched into one. Ive had a stab at it myself, and perhaps my pseudo SQL might explain what I need..
Code Block
UPDATE [TW].[dbo].[TBLSalesPart]
SET
CASE WHEN [Part] = 'MONTV-' AND [YN] = 'False' THEN [SubCategory] = 20440 END
CASE WHEN [Part] = 'TC-' AND [YN] = 'False' THEN [SubCategory] = 20444 END
Hopefully this makes sense, cheers..
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
View 2 Replies View RelatedHi.
I have this method in a class, it produces a string value based on the value of another property in the object (which represents a field in the database). I would like to turn this into a computed column in SQL server... but need help converting the formula if this is even possible. Thanks in advance.public string GetVendorEvalRating(int vendorevaltotal)
{
string vendorevalrating = "";if (vendorevaltotal >= 26)
{vendorevalrating = "Critical";
}else if ((vendorevaltotal >= 10) && (vendorevaltotal <= 25))
{vendorevalrating = "Material";
}else if ((vendorevaltotal >= 0) && (vendorevaltotal <= 9))
{vendorevalrating = "Minor";
}return vendorevalrating;
}
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0
[Code] ....
and here is table b:
naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
CREATE TABLE #A(
naics INT,
ust_code INT,
port INT,
all_qty_1_yr FLOAT,
[Code] ....
And here is table b:
CREATE TABLE #B(
naics INT,
ust_code INT,
port INT,
stat_month INT,
Cum_qty_1_mo FLOAT,
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem diwhich contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.
This is my basic code, but I keep ending up with duplicate work order lines in my result set.
Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg,
wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost]
From worksorderhdr wh Left Join
inventory iv On iv.item = wh.itemcode inner Join
models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract
where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1
order by wh.date_created
Consider the following dataset:
ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B
I need to count the number of IDs for each MD and each TYPE like this:
MD|A|B
JOHN|1|2
BOB|2|3
I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD
The query above results in:
MD|TOTAL
JOHN|3
BOB|5
My selection criteria is as follows:
where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF
Any ideas?
Hi
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage
adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL
FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE
PTFF.TRANSDATE BETWEEN 130501 AND 130831
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Samples of the result:-
IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
I have an 'update' query that looks like this:
update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
Let me start with saying thanks to all of you who have helped me (I'm a SQL newbee after doing OO for the past 12+ years)
I need to do several aggregates on multiple columns, with each column having different SELECT Criteria.
Sample Data:
Dept Project Cost CostFlag Schedule ScheduleFlag
D1 D1P1 495 1 135 3
D1 D1P2 960 2 70 2
D1 D1P3 1375 3 105 2
D1 D1P4 1050 2 160 3
D1 D1P5 1890 3 40 1
D2 D2P1 650 1 155 3
D2 D2P2 890 2 125 2
D2 D2P3 1235 3 85 1
D2 D2P4 430 1 140 3
D3 D3P1 1960 3 45 1
D3 D3P2 1490 3 85 1
D3 D3P3 1025 2 135 3
D3 D3P4 615 1 100 2
D3 D3P5 270 1 70 1
D3 D3P6 815 2 155 3
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range
D1 D1P1 495 135
D1 D1P2 960 70
D1 D1P3 1375 105
I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range
D1 D1P1 495 135 100 28
D1 D1P2 960 70 42 12
D1 D1P3 1375 105 91 38
I also have working code calculate the MEDIAN (what a pain that was, thank god I found a code example to get me going on the MEDIAN)
Thanks!
I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.
I always submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup
So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.
I cant figure out how to create the SQl script for this SELECT?
My dataset
TyreIdCarIdRegionIdCarAgeGroup
NULLNULL1084 2
65351084 1
5351084 1
NULL411085 NULL
120NULLNULL NULL
NULLNULL1084 2
65NULL1084 NULL
I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.
--Group all scores from same student and average them together
with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM LDCScores
WHERE schoolYear='2014-2015' AND term = 3
GROUP BY StudentID
[code].....
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
Hi! I'm new to SQL and have a question...
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress
If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If
RecordSet.Update
RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well.
Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe.
In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind.....
1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data.
2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access?
OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods?
I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this.
Thanks in advance.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I need to delete multiple sql logins on one of my database servers that are no longer needed. Most of these logins are not attached to any database on the server because the database has been deleted from this server. Is there any way to delete logins that are no longer needed without having to review every login to verify it is not attached to a database before deleting?
View 2 Replies View RelatedHi,
How do you delete multiple packages at once ?
We have a folder in SSIS called ETL and there are about 25 SSIS packages in it.
Now we need to update it.
I tried to delete the folder but you get the message that the folder is not empty.
So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work.
Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes.
But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
Any ideas ?
Constantijn Enders
want to delete rows from two tables after a join.
in Access, here's something we can do:
delete table_A.*, table_B.*
from table_A left join table_B on ...
but in ms sql, it appears (to me) that you can only delete from one table at a time. how would i accomplish what i want to do?
also, is there a distinctrow equivalent in ms sql server? thanks
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user.
DELETE FROM T1,T2,T3 WHERE column1='1'
how do i do it.
Hey all,
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=-
Matt Torbin
President
Center City Philadelphia Macintosh Users Group
http://www.ccpmug.org/