Not Filtering Criteria

Mar 22, 2007

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')

and Chan IN ('XM', 'BN', 'RS', 'MM')

and Seg IN ('Hoc','LeftOver', 'COnly')

View 4 Replies


ADVERTISEMENT

Date Criteria In View Not Filtering

Mar 19, 2007

I am using BETWEEN '02/01/2007' AND '2/01/2006' in the criteria of a VIEW and have tried <= '02/01/2007' AND >='2/01/2006' but both are not filtering the dates correctly. Is there another way? What am I missing?

View 4 Replies View Related

How To Ignor Filtering Criteria If There Is A Null Value For Parameter

May 15, 2008

I have gridview and i want to ignor that filtering if that parameter is empty how do i write that query.
my query is
select * from UserMAster where UserId=@USerID and RoleId=@RoleId
if @UserId is blank then i want to select all records without filtering with user Id a
nd if i have passed null or anything as parameter in roleId then alsoi want all the records if it contain value then i want to do filetring is it possible to with query. or else send me some alternative so that i don't need to execute more query.
 

View 7 Replies View Related

Best Practice Question: JOIN Criteria Vs. WHERE Criteria

May 24, 2004

For example, consider the following queries:


DECLARE @SomeParam INT
SET @SomeParam = 44

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?

Thanks.

View 3 Replies View Related

WHERE Criteria

Nov 28, 2007

SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings
Where WinningPct > 0.5

SQL will not allow me to put a column that I created(WinningPct) as criteria for WHERE (I know this is cause Select is evaluated last)

How can I list my results according to criteria I am creating in my query?

View 9 Replies View Related

Best Searching Criteria

Sep 30, 2007

I have a table
 GO
 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 : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand 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?

View 1 Replies View Related

BCP With Differing Criteria

Jul 12, 2002

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation

View 3 Replies View Related

Group By Different Criteria

Feb 2, 2015

I have a table in the following format

reporting_date interest_payment balance
200401 10 10
200402 20 15
200403 30 20
200404 40 30
200405 50 40
200406 60 50
200407 70 60

i wanted to generate an OUTPUT in the following format :

The output of the query should look like this :

reporting_date interest_payment balance
Q1 -2004 60 10
Q2 -2004 170 30
Q3 -2004 70 60
Q4 -2004 0 0

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

View 1 Replies View Related

Get Multiple MAX With Where Criteria

Mar 17, 2015

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

View 2 Replies View Related

FROM Vs WHERE Selection Criteria

Jan 27, 2006

Hi,

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

Any comments on which is best, and why?

Thanks,

Chris

View 3 Replies View Related

Order By Criteria

Jun 7, 2006

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"

View 3 Replies View Related

Searching Criteria

Feb 5, 2007

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

~~~Focus on problem, not solution~~~

View 20 Replies View Related

Using Dates In Criteria

Aug 31, 2007

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, Object[] methodArguments, SQL_API odbcApiMethod)

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.

SSIS package "Package_test.dtsx" finished: Failure.

View 2 Replies View Related

Does It Matter If The Criteria Is In The FROM Or The WHERE?

May 6, 2008

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%'


THANKS!!!

View 3 Replies View Related

FilterExpressions With Multiple Criteria

Sep 10, 2007

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

View 9 Replies View Related

Query Criteria Format

Feb 9, 2004

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') ?

Thanks,

Paul

View 1 Replies View Related

Select Same Field Twice For Different Criteria

Mar 11, 2005

I need a little insight on how to select the same field from the same table, but for different criteria.

here are example tables...

Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1


DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2

desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2


Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT

View 1 Replies View Related

Using Results From One Query As Criteria For Next

Jan 4, 2006

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?

View 5 Replies View Related

View 2 Tables With A Certain Criteria

Aug 28, 2000

Is it possible to to view 2 tables with a common field name and display it in the following way

Name telephon no.

John 123-4567
789-4561
987-6543
Peter 159-7536
654-9874
896-3214
456-9874
without repeating the name in each row.
Thanks

View 1 Replies View Related

ORDER BY Specific Criteria

Aug 25, 2005

Hi,

I was wondering if it is possible to order a recordset by specific values.

That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.

For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.

Can you do something like
Code:

SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)

...etc

Thanks

e

View 2 Replies View Related

View Criteria: Only One Instance

Nov 3, 2006

I have a table with 500 stores. (StoreNumber, StoreZip, StoreOpenClosed [999 if store is closed, 1 if store is open])

I have a view that only selets the open stores

SELECT *
FROM tblStores
WHERE StoreOpenClosed <> 999

This selects all the open stores. There are 2 instances where there are two stores in the same zip code.

EX StoreNumber: 3452 Zip: 02192
EX StoreNUmber: 5325 Zip: 02192

I only want to select ONE store per zipcode. Maybe even select one of the two stores randomly. How could I do this with out hardcoding anything?

Thanks
Dynasty

View 2 Replies View Related

Deleting With Multiple Criteria

Aug 27, 2004

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

View 3 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

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 ?

View 12 Replies View Related

Excluding Data For Certain Criteria Only

Feb 6, 2012

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

WHERE PR04PTF.EmployeeID=441

View 3 Replies View Related

Procedure Increase By % Using Criteria?

Sep 17, 2014

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.

View 2 Replies View Related

Picking Records Having Both Of Two Criteria

May 14, 2008

Hi,

Slightly knotty and hard-to-articulate problem here, so please bear with me.

As part of a series of transactions, I have a table which contains data which looks like this:

AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608


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

View 7 Replies View Related

Command With Several Search Criteria

Dec 4, 2013

I am very new to sql programming. I have a database with 15 columns. At this moment I do this to get all rows containing the year 2013:

WHERE DATEPART (yyyy, DownloadDate) = 2013 .

But I also want to add a criteria so that I can have all rows where DownloadDate contains 2013 AND WHERE IsRead(BIT) contains NULL. I tried this:

WHERE DATEPART(yyyy, DownloadDate) = 2013 AND IsRead = null .

But this gave me nothing!

View 3 Replies View Related

Export Without Duplicates Under Certain Criteria

Mar 20, 2014

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..

View 8 Replies View Related

Update With Criteria And Value From Another Table

May 16, 2006

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

Anyone feel generous enuf to help a newbie out?

View 10 Replies View Related

Slow Query Due To Criteria

Jan 24, 2008

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"));

View 5 Replies View Related

Trying To Group By Two Diff Criteria

Feb 16, 2007

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)

View 1 Replies View Related

SSRS Report Criteria

Jul 26, 2007

Hi,

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)


Where should i keep this condition in SSRS 2005.


Can you help me


Thanks
Dinesh

View 4 Replies View Related

Common Criteria Certification

Dec 18, 2006

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved