CREATE TABLE [dbo].[Speech]
(
[SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY,
[UniqueName] [varchar](52) NOT NULL,
[NativeName] [nvarchar](52) NOT NULL,
[Place] [nvarchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
[LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE,
[SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE,
[IsFavorite] [bit] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDate] [datetime] NULL
)
Now I want to search the Table Speech
Sometimes by : SpeechId
Sometimes by : SpeakerId
Sometimes by : LanguageId
Sometimes by : SpeechId And LanguageId
Sometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches with
any particular SpeakerId and LanguageId
with IsVisible equals to true
and IsFvaorite No Matter
and Type equals to Audio
For these type of queries I think the solution is
GO
CREATE PROCEDURE [dbo].[sprocGetSpeech]
@speechId int = NULL,
@uniqueName varchar(52) = NULL,
@nativeName nvarchar(52) = NULL,
@place nvarchar(52) = NULL,
@type smallint = NULL,
@languageId char(2) = NULL,
@speakerId int = NULL,
@isFavorite bit = NULL,
@isVisible bit = NULL
AS
SELECT
SpeechId,
UniqueName,
NativeName,
Place,
Type,
LanguageId,
SpeakerId,
IsFavorite,
IsVisible,
CreatedDate,
ModifiedDate
FROM
Speech
WHERE
SpeechId = @speechId
AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END
AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END
AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END
AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END
AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END
AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END
AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END
AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?
Do you have any better solution?
If my solution is better then Is there any performance loss with that query?
hi all... how do i write my where clause if i wanna search BETWEEN something to something, but at the same time, find ALL if user send nothing (''), NOT searching for '' column... and also find date if they send a date, and if they dont send date, do not consider date at all(find all at any dates).. is this possible to in one where clause without any IF statement... thanks..
WHERE d.Ownership LIKE '%' + @ClientID +'%' AND d.WhsID LIKE '%' + @WhsFrom + '%' AND d.CustomLotNo LIKE '%' + @CustomlotnoFrom+ '%' AND d.LocID BETWEEN @LocFrom AND @LocTo AND d.ItemID LIKE '%'+ @ItemFrom + '%' AND substring(d.LocID,1,1) LIKE '%' + @ZoneFrom AND d.RecvDate <= @Date
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
i.e i wanted to represent data by quarter and year and group by quarter and year for interest_payment column but for balance i need to pick up the value from the first reporting date in that quarter ,so as you can see q1-2004 has 10,15 and 20 but only 10 is accounted as that was the first reporting date in that quarter
I have my query working for interest payment but i am not sure how do i pickup the first reporting value for balance in a quarter
SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment" FROM (SELECT *, (reporting_date%100 - 1)/3 as report_quarter, reporting_date/100 as report_year FROM employee) a GROUP by report_year, report_quarter order by report_year, report_quarter
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
While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause: e.g. select * from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1
Is logically the same as: select * from Table1 a inner join Table2 b ON a.key = b.key where a.a = 1
jiang writes "Apologies in advance for my inexperience.
I have a SQL table to hold my product information: prods(prodnum(char(10), prodname(char20), quantity(int))
The values in prodname column are like: ABCDEF ADCDEF BCDEFG CDEFGH
For those products that sold out, I made a mark in the front of prodname, like *ABCDEF
Then in my query, I want to sort the product name in alphabetic order, in addition, I also want to put prodname start with * at the end of the result list, like:
ADCDEF BCDEFG CDEFGH *ABCDEF
I tried to use: select prodname from prods order by prodname
this query shows *ABCDEF is on the top of the result, then I tried:
select prodname from prods order by charindex('*', namecode)
this query does put *ABCDEF at the bottom, but other records are not in alphabetic order.
Could you please help me? Many many thanks! Jiang"
I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.
WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) and Type IN ('Employee', 'Refinance')
I am just learning SQL server 2005 and I am having trouble with the sql statement of my sqlcommand. I am just trying to query for any ticket that was open yesterday. I need this to run daily If I run the following it works SELECT Assigned_Group, Assigned_Technician, Date_Created From "Support Center Ticket" where "Date_Created" > '08/30/2007 00:00:00' and division = 'Northern'
however when I change it SELECT Assigned_Group, Assigned_Technician, Date_Created From "Support Center Ticket" where "Date_Created" > convert(varchar, getdate()-1, 101) + ' 00:00:00' and division = 'Northern'
SSIS package "Package_test.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, DataReader Source [46]: System.Data.Odbc.OdbcException: ERROR [420] Driver]Unexpected extra token: (
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (46) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (6856)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package_test: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
I have a query that searches through a 4 million record table. The data is fed from the UNIX flat file systems so the data is not in optimal search format. So I created some views that massaged the data and then index them. I select and join the original table with the view, with NOEXPAND hint on the view. My question is this theory right: If I put the criteria in the FROM join part then it will make the join easier than if I put it in the where clause?
Example (any difference) SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON m.RecNumber LIKE '112%' AND u.ID = m.ID versus SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON u.ID = m.ID WHERE m.RecNumber LIKE '112%'
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.
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg
The symbols are stored in the tables as: A B C D The query string criteria would look like: IN('A', 'B', 'C', 'D')
The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC
SELECT a_Name_Symbol.Symbol, a_Financials.Revenue FROM a_Financials INNER JOIN a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol WHERE (a_Name_Symbol.Symbol @Symbol) ORDER BY a_Name_Symbol.Symbol
Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
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....
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 ?
I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here. No code has to be inserted here.
No code has to be inserted here. No code has to be inserted here.
Right now my SQL is this:
Code: SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum] FROM PR04PTF
INNER JOIN PR11ERF_History ON PR11ERF_History.EmployeeID = PR04PTF.EmployeeID AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
I am trying to increase the price of an product by a user entered % for items with Dishwasher in the itemdesc.
Below is the procedure I have which doesn't appear to show any errors in the SQL Developer.
CREATE OR REPLACE PROCEDURE AdjustPrice( pItemDesc IN ITEM.ItemDesc%TYPE, pPercent IN NUMBER) IS BEGIN UPDATE Item SET ItemPrice = ItemPrice + ItemPrice * pPercent / 100 WHERE ItemDesc = pItemDesc; END;
This is my run script:
BEGIN AdjustPrice ('%Dishwasher%',10); END;
I think the problem is with the way I have done the run script to filter to items with Dishwasher in the description. I tried LIKE an that didn't work either.
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId) as valueId, adWeeks, clientId, adID, AdBookingID into #worktable from #tmp group by adWeeks, clientId, adID, AdBookingID having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need? Cheers, Matt
If I have a table (lets name it table1) contains the columns: Customer Name, Creation date, Call status and I want to create a query for the last call status per Account name how I am going to do that via sql? I tried :
SELECT DISTINCT customer name, creation date, call status FROM table1
And I got less rows but there where still duplicates (customer name) since the call status was different..
Hi, I have a problem which is probably really simple to solve but I have gotten a tilt and cant get it...Hoping you can help me. What Im trying to do in pseudo-code:
Get A from a tableX where B = 0
Check tableY for each A and get B from there
Update all B in tableX where TableX.LotNo=TableY.Lotno with the value from TableY.B
I have a query in access that is running extremely slow and I'm trying to find a better way to write it. It appears to be the criteria statement that is causing the lag. Is there a better way to write this? And unfortunately I have to keep it in access.
query: SELECT DISTINCT "JBC" AS ClientCode, PaymentDetail.PatientNumber, Procedures.CaseNumber, IIf(IsNull([TicketNumber]),Procedures.patientnumber & Year(Procedures.dateofservice) & Month(Procedures.dateofservice) & Day(Procedures.dateofservice),Procedures.ticketnumber) AS ClaimNumber, PaymentDetail.PaymentCounter, PaymentDetail.TransAmount, Payments.PaymentDate, PaymentDetail.AccountingDate, PaymentDetail.TransDate, payments.PaymentType & "-" & Adjustments.adjustmentcode AS CombinedPmtType, Payments.PaymentType, Payments.PaymentCode, Adjustments.AdjustmentCode, PaymentCodes.BriefDescription, PaymentCodes.LongDescription, Payments.CarrierCode, Payments.Remarks, Procedures.ProcedureCode, Procedures.DateOfService, PaymentDetail.DetailCounter FROM ((((PaymentDetail LEFT JOIN Procedures ON (PaymentDetail.PaymentCounter = Procedures.Counter) AND (PaymentDetail.AccountingDate = Procedures.AccountingDate) AND (PaymentDetail.PatientNumber = Procedures.PatientNumber)) LEFT JOIN Payments ON (PaymentDetail.TransDate = Payments.AccountingDate) AND (PaymentDetail.TransCounter = Payments.Counter) AND (PaymentDetail.PatientNumber = Payments.PatientNumber)) LEFT JOIN PaymentCodes ON Payments.PaymentCode = PaymentCodes.PaymentCode) LEFT JOIN Adjustments ON (PaymentDetail.TransDate = Adjustments.AccountingDate) AND (PaymentDetail.PatientNumber = Adjustments.PatientNumber)) LEFT JOIN AdjustmentCodes ON Adjustments.AdjustmentCode = AdjustmentCodes.AdjustmentCode WHERE (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=2)) OR (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=5) AND ((AdjustmentCodes.InsuranceIndicator)="N"));
I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)The data in the table is :'11', 'aa01'and on upto'11', 'aa50' : total 50 entries'22', 'b01''22', b'02''22', b'03''33', 'c01' to '33', 'c40' : total 40 entries'44', 'b02''44', 'd01''44', 'd01''44', 'd01'How can write a query which will bunch together values of c11with rows 5, and then bunch together values of c11 withrows < 6, and add them up.My output should be :'11' 50'33' 40'others' 7 (3 rows for '22' and 4 for '44' are bunchedtogetheras the # of rows < 6, and added. 3+4 = 7)
I created one fact and four dimensions in SSAS2005. I am accessing these in SSRS 2005.
But my problem is to develop one report with this fact i have to keep a criteria like this in SSRS 2005. how to compare a column in one dimession with another column in another dimension For Ex: CONVERT(VARCHAR,dt1.date,102)>= CONVERT(VARCHAR,dt2.Date,102)
I see that part of SP2 is the new CC certification -- can anyone give me some more details. What level is it at? EAL4? I can't seem to find the certificate on the CC Web site but it could be because it's not SP2 yet.
I have different codes and I want to specify different date of birth range based on selected code in the parameters.
DECLARE @CODE VARCHAR(5) = 'FXE' SELECT * FROM TABLE WHERE CODE = 'FXE' and AND DATE_OF_BIRTH BETWEENDATEADD(month,-15,GETDATE()) AND DATEADD(year,-1,dateadd(YEAR,-0,cast(GETDATE()asdate))))
How I can do for one code but I cant get my way around bringing another code 'WHY' which has a different date of birth range. Basically I want a user to be able to change a code in the parameter and display results based on the parameter selected.
Ehhm,great!! Could you tell me what is going wrong with the stored procedure below? I can't get any results! (the 'ap_dateIn' field in the sql table 'tbl_1' has "dd/mm/yy hh:mms" smalldatetime format)
