Transact SQL :: Filtering Join Tables By Date
Sep 14, 2015
I need to Filter Employees by Available Date and grab their Basic contact information
I used Join to Join both tables but I can't seem to find a way to only get data from employees where DateAv > SelectedDate.
I'm using this in a SqlCommand with C#.
SELECT " EmployeeNameTable.ID, EmployeeNameTable.FullName, EmployeeNameTable.DateAV,ContactTable.HomePhone, ContactTable.MobilePhone, ContactTable.Email FROM EmployeeNameTable INNER JOIN ContactTable On EmployeeNameTable.ID = ContactTable.ID OrderBy EmployeeNameTable.FullName"
This code loads all employees Successfully but it doesn't filter them by the DateAV Column which is in Date Format.
View 2 Replies
ADVERTISEMENT
Jan 28, 2015
I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2
FROM (
SELECT DISTINCT a.key1, b.key2
FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON
a.key1=c.key1
WHERE a.year=c.year)
AS query
GROUP BY query.key2
HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever.
View 0 Replies
View Related
Feb 19, 2008
I am trying to generate a report that is basically a class list. It lists classes for a specific semester and lists them by day of the week and then by time.
If I am pulling back a dataset of all classes, how do I filter my report so as to show each set of classes appropriately (by day and by time within the day)? I tried using the Filter function on the table level, but I guess I don't get it.
My table would need to look like this:
MONDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
TUESDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
View 4 Replies
View Related
Sep 10, 2015
I have the following 2 tables:
Location:
Policy:
1 Location can be attached to many policies.
I need to create a query to get 1 row per location and get the minimum PolicyBookingDt and RowUpdateDt from the policy table. All the attributes from the Location table should also be from the Policy that has the minimum PolicyBookingDt.
So from the above example, i need to get the following:
Getting a little confused on how to create the syntax for this.
View 4 Replies
View Related
Apr 23, 2015
i have two tables Table one have 2 columns id and value
id value
1 Dell
2 Hp
B2 Hp-mini
B3 Hp-lapTop
3 Acer
the second table have 3 clomuns id,name,idTable
1 TeaBou B
2 Mark B
3 Jack 1
4 Piere 2
5 Jean 2
6 Mark 3
i tried this query
select*from table1 t1 ,table2 t2
where t1.id=t2.idTable
i had some data but also i need to include the person who have the B product.
View 6 Replies
View Related
Aug 13, 2015
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
View 3 Replies
View Related
Aug 18, 2015
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID
View 9 Replies
View Related
Oct 24, 2013
I'm using SQL Dev 2.1 and i want to find the latest date and also joining tables. My query is below:-
I want to get the latest DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE
As there are more that one date relating to the same invoice number but different comments posted against the DEBT_COLLECTION_STEPS_V.DESCRIPTION.
SELECT AR_INVOICE_INQ_V.CLIENT CLIENT,
AR_INVOICE_INQ_V.CLI_NAME CLI_NAME,
AR_INVOICE_INQ_V.PARTNER PARTNER,
AR_INVOICE_INQ_V.PAR_NAME PAR_NAME,
AR_INVOICE_INQ_V.MANAGER MANAGER,
[Code] ....
View 1 Replies
View Related
Oct 5, 2015
I am joining on two tables, and returning the values that do not exist in #topoftheline -- well now I see that their are multiple calltimes so I want to return ONLY the most recent call time. What would I change in my syntax to only return that most recent datetime?
Create Table #topoftheline
(
callreceived datetime,
callerfirstname varchar(100),
callerlastname varchar(100),
callnotes varchar(4000)
)
[Code] ...
View 3 Replies
View Related
Sep 10, 2015
I have two tables. Status and Fourhistory tables.
Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.
Below i have given some possible sample scenarios which i face and the third table contains the expected output.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)
[Code] .....
View 10 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Jul 29, 2005
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined.
Filtering in where clause....
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
wherepc.removed = 0
andp.cancelled = 0
andp.deleted = 0
ands.closed = 0
ands.deleted = 0
andyear(getDate()) between year(P.startDate) and year(P.expDate)
Filtering in joins...
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
and pc.removed = 0
and p.cancelled = 0
and p.deleted = 0
and year(getDate()) between year(P.startDate) and year(P.expDate)
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
and s.closed = 0
and s.deleted = 0
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
View 1 Replies
View Related
Apr 22, 2008
Hi All,
Can anybody tell me which of the following is the most efficient query if i have huge tables.
SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1 AND Tab1.Col1 = 5
OR
SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1WHERE Tab1.Col1 = 5
As long as i explored this, Sql Server Query Execution Plan shows the similar cost for both cases. Is there any difference?
If yes why?
Thanks in advance.
Regards,
Sulaman Riaz
View 4 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Dec 21, 2007
Hi.
I am working on a procedure which can filter data through three tables.
First Table is User
---------------------------
UserName UserID
---------------------------
Second Table is Customer
---------------------------
UserID Cust_Site_Int_ID
---------------------------
Third Table is l Details
---------------------------------------------------------------
Cust_Site_Int_ID Created_Date Status
----------------------------------------------------------------
I want to create a stored procedure which can filter through these tables. I will have four input fields in my application to accept parameters. The four parameters will be UserName, two date fields which specifies a range of dates and status. I want the filter to work like this, if only one parameter is provided it will use only that parameter if additional parameter is given it will use the AND logic.
I tried to do a Proc but it is not working any way here it is. So you will get a better idea of what I am trying to achieve.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
ALTER PROCEDURE [dbo].[FilterServiceTickets]
-- Add the parameters for the stored procedure here
@userName varchar(20)= NULL,
@startDate datetime =NULL ,
@endDate datetime = NULL,
@status int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT SERVICE_TICKET.SERVICE_TICKET_CODE, SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_NUMBER,
SERVICE_TICKET.STATUS
FROM aspnet_Users INNER JOIN
BuildingAddress ON aspnet_Users.UserId = BuildingAddress.UserID INNER JOIN
SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID
WHERE ((aspnet_Users.UserName = @userName)OR (@userName=NULL))
AND ((SERVICE_TICKET.STATUS = @status) OR (status=NULL))
AND ((SERVICE_TICKET.CREATED_DATE >=@startDate AND SERVICE_TICKET.CREATED_DATE <=@endDate ) OR (@startDate=NULL)OR (@endDate=NULL)OR(@startDate=NULL AND @endDate=NULL))
END
Could some one please help me with this.
Thanks
View 1 Replies
View Related
Aug 31, 2015
I have a simple query
select distinct
bl_rec_ref
,freq_cd
from hgmprty1
where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')
That produces the following output (part shown)
However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.
Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.
However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.
View 4 Replies
View Related
Sep 11, 2007
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
View 5 Replies
View Related
Mar 19, 2007
Please can someone assist me which this simple query:
Im using MSSQL Server 2005:
select * from ex_messagelog where DateCreated = '2007-03-19'
The query above does not return any results, but my data looks as follow: Note, the DateCreated is a DATETIME Datatype field.
239test 2007-03-19 08:42:19.00000
240Hallo Frank2007-03-19 08:45:43.00000
241spring 123 2007-03-19 08:49:41.00000
242testing 1232007-03-19 08:51:15.00000
243testing 1232007-03-19 08:52:39.00000
244as 2007-03-19 08:55:30.00040
View 4 Replies
View Related
Jun 13, 2008
Hello I am trying to gilter a table by getdate() (i also tried now()) but I cannot seem to be able to do it I place my code below if anyone can help. am grateful, my db is sql 2005.
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = getdate()
If (Request("MM_EmptyValue") <> "") Then
Recordset1__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_connpeepeek_STRING
Recordset1_cmd.CommandText = "SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__MMColParam) ' adDBTimeStamp
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
if any one can help I would be grateful
k
View 11 Replies
View Related
Dec 12, 2007
I have a View with a structure like this
VisitTimes Company
2007-07-10 14:24:38.000 Microsoft
2007-03-10 11:14:38.000 Microsoft
2007-12-01 13:04:56.000 SQLTeam
2007-12-13 12:54:52.000 GoldMan Sac
2007-08-11 02:15:38.000 Oracle
2007-02-11 12:45:04.000 SAP Ltd
I am asked to write a stored procedure that get a count of each count of visit on a START and END date
I wrote the below SP but am not getting the right result
I think the **where VisitTime >= @Start AND VisitTime <= @End)** is not being evaluated. Help pls
CREATE procedure dbo.GetVisits
@Start varchar(50),
@End varchar(50)
as
SELECT TOP 100 PERCENT COUNT(company) AS VisitCount, company
FROM visits.dbo.IViewVisits where EXISTS
(SELECT * FROM VISITS.dbo.IViewVisits where VisitTime >= @Start AND VisitTime <= @End)
GROUP BY company ORDER BY COMPANY ASC
View 6 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Sep 22, 2015
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
View 2 Replies
View Related
May 31, 2007
Hi,
There is a date field present in table1 as character field in the format of YYYYMMDD. I need to convert this to datetime
and load it into table2. But there are some invalid dates in that field of table1.While converting and loading to table2 i need to put NULLS for the invalid dates and continue loading the rest of them into table2.
Eg: Dates in Table1:
19860930,
0,
999,
19820925 etc.
When i load into table2 it should be loaded as
1986-09-30 00:00:00.000
NULL,
NULL,
1982-09-25 00:00:00.000
How can i add this conditon to the query. Currently i have the below selection query:
SELECT CAST(LTRIM(RTRIM(ODRDT)) as datetime) AS ORDERDT
FROM Table1
Please advise
Thanks,
View 9 Replies
View Related
Mar 3, 2007
Hi all,
I have a datasource with three columns.
NAME / DATE / COST
The dates run from January 2005 to March 2007.
They appear in the following format in the query window:
03/01/2005 00:00:00
All I want to do is put the dataset in to a graph and filter the date so it only shows any cost from January 2006 onwards. What ever date I type in the filter I get the following error
"Cannot compare date of types System.Datetime and System.String"
Anyone know what I am doing wrong or how to solve the problem?
Cheers,
Rob.
View 1 Replies
View Related
Mar 18, 2008
We are pulling data from a sharepoint list and we have created a report using business development studio in sql 2005 and have created a table with data that we want to display.
We are trying to filter this data so that we can get the report to show entries for the day or for a week, but im not sure how to achieve this?
Thanks in advance.
View 28 Replies
View Related
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
Nov 5, 2007
Hi I have a issue here where i have to store a record from a flat file into Sql Server Table.
The criteria is that there are records in flatfile having same business key but different transaction dates.
I have to place only htose records into the table which are having recent or greater transaction date .
example
name age date
xyx 34 00000000
xyx 34 20071103
xyx 34 20071031
key age and Name
ihave to pick only one record with greater o recent date from 3.
What approach should i take.
Some of the Business keys conssit of 2 or 3 columns .
I have to insert or store only the recent Date consisting records. how do i acheive this please help me.
View 13 Replies
View Related
Jun 19, 2007
Im trying to get the top 10 sales people by date range
my cutdown select statement for my dataset is as follows:
SELECT TOP 20 SaleDate,Consultant, State, Sum(Value) As Winbacks, UserID
FROM dbo.tbl_kpi_sales
GROUP BY Consultant, State, SaleDate, UserID
HAVING (SaleDate)>= @Param_StartDate and (sales.SaleDate)<= @Param_EndDate) AND State IN (@Param_State)
ORDER BY Winbacks Desc;
The problem im getting is that when a date range is entered by the user, the same representative might appear in the top 20 list afew times as they made the top sale for different days, I am wanting the select statement to add up the total sales for that representative for that date range and return him as a single entry...Because the sale date is in the select statement the group by clause separates these records, I cannot take the sale date out off the select because i need it to determine the date parameter for the reports
for example for date range - 1/3/2005 to 20/3/2005 get the top 20 reps Im currently getting
Name Value Date
Jim Brown 20 1/3/2005
Jim Brown 10 12/3/2005
Jim Brown 23 13/3/2005
Tom 10 12/3/2005
etc...till top 20
I am wanting the following output
Name Value
Jim Brown 53
Tom 10
Is there someway off doing this which still allows me to use a date range entered into report services
View 3 Replies
View Related
Jan 3, 2007
Hi,I have a date/time field in a SQL2000 database, and what I would liketo do is to filter on a specific part of the field, for example thetime or hour.Supposing I have a set of data for the last 5 years and would like tofilter out any records which are outside working hours i.e. I wouldlike to show records where the time is between 9am and 5pm.Does anyone know if there is a simple way to do this in SQL? If yousimply don't specify the date part in the where clause (e.g. WHEREissuedatetime between '09:00:00' and '17:00:00') it defaults it to1900-01-01 so basically no data is returned.The only way I can see to do this is by using the DATEPART function,converting it to a varchar, appending 1900-01-01 on to it andconverting it to a datetime, and then using the where clause as statedabove. This is quite a long-winded way, however. Any other suggestions?Thanks,Matt
View 3 Replies
View Related
Sep 8, 2015
I have an Excel 2010 Pro pivot table that uses a Power Pivot view sourced to a SQL Server 2012 SQL view. This view contains multiple date fields, and when I attempt to use the 'Value Filter' on one of these, the data is not filtering. The data type in the SQL view is a date.
After attempting to set a Value Filter, and seeing that the filtering didn't work, when I go back to the Value Filter criteria (in my case, I used 'Greater Than' option), the date value that I plugged in to filter has been replaced with a numeric value.
View 3 Replies
View Related
Oct 6, 2006
Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.
SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber
Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!
I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.
Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.
Rick
View 7 Replies
View Related
Jun 29, 2006
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-Ben
View 1 Replies
View Related
May 21, 2014
I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
[URL] ....
Then i could add an indexed view to improve performance, or can this be done another way?
View 9 Replies
View Related
