Correlated Subquery And Date Filtering Problem
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
ADVERTISEMENT
Aug 25, 2006
Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?
Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)
select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'
Thanks
View 9 Replies
View Related
Jun 6, 2008
HiI was trying an example of subquery Create Table #Temp( PK_ID int identity(1,1), sName Varchar(50) )Create Table #TempAddress( PK_ID int identity(1,1), PersonID int, Address Varchar(100))Insert Into #Temp Values('Karan')Insert Into #Temp Values('Gupta')Insert Into #Temp Values('Karan')Insert Into #Temp Values('Karan')Insert Into #TempAddress Values(1,'Address1')Insert Into #TempAddress Values(2,'Address2')Insert Into #TempAddress Values(3,'Address3')select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)Drop Table #TempDrop Table #TempAddressBut I am getting an errorMsg 4145, Level 15, State 1, Line 29An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'. Am i doing something wrong.Kindly adviceRegardsKaran
View 1 Replies
View Related
Apr 28, 2000
Hello,
I need to come up with a query (I really don't want to use cursors) that will perform the following:
I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.
Here are the pertinent fields: userid,message_id,message_date
I really appreciate your help,Rob
View 2 Replies
View Related
Nov 8, 2007
This really a question of approach more than anything else.
The situation is as follows:
I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.
So that...Given the date range 11/1/2007 to 11/2/2007 and the data set
Code:
11/1/2007 CompanyA req1 req2 req3 req4
11/2/2007 CompanyA req1 req2 null req4
11/1/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyC req1 null req3 req4
Only the two rows for CompanyB would ultimately be delivered.
On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?
View 1 Replies
View Related
Mar 24, 2007
Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.
My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.
View 8 Replies
View Related
Mar 24, 2008
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value
View 2 Replies
View Related
Mar 24, 2008
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value
View 3 Replies
View Related
Mar 14, 2002
Hi, the following query works in Oracle, how do I do it in SQL Server? Thanks.
UPDATE table1 a SET a.newid =
(SELECT b.newid
FROM table2 b
WHERE a.id = b.id)
Basically, if table 1 and 2 have the same value in the "id" column, then I update the "newid" column in table a to match that of "newid" in table b.
View 1 Replies
View Related
Jan 19, 2014
I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.
Two quick questions:
1. Is the below considered a correlated subquery?
2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.
USE MyGuitarShop
SELECT EmailAddress,
(SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder,
(SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID
FROM Customers
GROUP BY Customers.EmailAddress
View 10 Replies
View Related
May 10, 2006
Hi,I have a history table with about 400 million rows, with a uniquecomposite nonclustered index on two columns (object id and time period)which is used for most of the queries into the table, and an identitycolumn for the clustered primary key.Many of my queries use correlated subqueries to pull unique historyrows from the history table for each of a set of objects from theobject table, for instance, pulling the earliest history row for eachobject in a set. These correlated subqueries reference the object tableand return the primary key of the history table, e.g.:select *from lp_object linner join lp_object_history hon h.lp_object_id = l.lp_idwhere l.lp_set_id = 'SOME_LITERAL'and h.lp_id = (select top 1 lp_idfrom lp_object_historywhere lp_object_id = l.lp_idand lp_some_column > 0order by lp_time_period)Now, if lp_some_column is not indexed, this query has no choice but toread the entirety of every single history row for every object in theset where lp_set_id = 'SOME_LITERAL', so that it can determine iflp_some_column > 0, and because the history table is clustered by theidentity column rather than the ID of the relevant object whose historywe're tracking, the reads take forever - they have to bop all aroundthe disk. The sets I deal with tend to have about 5K objects in themand about 200K associated history rows.I'm considering reclustering by the (object id, time period) index, butthen my queries will need an extra bookmark lookup step to get the rowdata from the identity value returned by the correlated subquery. Ithink it will still be faster, though, so I will probably build a copyof the table with the alternative clustering scheme to run someperformance tests.What I'm wondering is, if I were to dispense with the identity columnaltogether and replace it with a composite primary key of (object id,time period), would I be still be able to use my correlated subqueries?Because then there wouldn't be a single column that uniquely identifieseach row in the history table and I don't think SQL Server supportsmulticolumn correlated subqueries.Thanks for reading,Seth
View 4 Replies
View Related
Jul 20, 2005
Hello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason
View 1 Replies
View Related
Aug 13, 2005
Suppose I have this query, which shows each order and the price of the most expensive item in each order:
SELECT Ord.OrderID, Ord.OrderDate, MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet INNER JOIN Northwind.dbo.Orders AS Ord ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate
I need to also show the ProductID that has MaxUnitPrice from the Order
Details. I can't just add ProductID to the select list because
I'd have to group by it, and then I'd get a row for each product,
instead of a row for each order... I think I need a correlated subquery
but can't work out how to do it!
View 1 Replies
View Related
Aug 29, 2006
I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance
View 5 Replies
View Related
Jul 20, 2005
record_id Status Due_date549In Progress2004-06-02 00:00:00.000549Not Started2004-06-07 00:00:00.000549Not Started2004-06-08 00:00:00.000549Waiting 2004-05-31 00:00:00.000549Waiting 2004-06-04 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-06 00:00:00.000551Completed2004-05-07 00:00:00.000551Completed2004-05-10 00:00:00.000551Not Started1900-01-01 00:00:00.000552Not Started1900-01-01 00:00:00.000Hi I have this table with 3 columns.. What I want isDistinct(record_id),max(due_date) and Status.. I tried thisselect distinct(record_id),status,(due_date) from table1 where(due_date) in(select max(due_date) from table1 as A where a.record_id=record_idand a.due_date is not null group by a.record_id,status)So the result that I want isRecord Status Max(due_date)549Not Started2004-06-09 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-10 00:00:00.000Any help is appreciated..ThanksAJ
View 3 Replies
View Related
Feb 2, 2007
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
View 6 Replies
View Related
Nov 14, 2006
Original code (works in Sql Server 2000 but has error in Sql Server 2005) --
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
Error message in Sql Server 2005 --
Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Corrected code --
AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
View 10 Replies
View Related
Apr 1, 2008
I have a 7 million line table named SecurityID with the following data:
Date, Security, Identifier1, Identifier2, Identifier3
I am trying to reduce it to a table newSecurityID in the following form:
FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3
This new table will have the first instance for each securityId with the identifying information. New rows will be added If any of the 3 identifying information changes. This isn't as simple as querying for the maximum and minimum value given each distinct group of identifiers because identifiers can change from an initial set and then change back to the initial values.
My plan was to first select all distinct (Security, Identifier1, Identifier2, Identifier3) into a temporary table. Then query the table SecurityID for the minimum date available which matches these 4 fields and find the corresponding maximum value.
This doesn't seem to working as I had planned as I am getting one row for each date rather than when identifiers change. Plus its taking a really long time to finish.
Any help will be appreciated!
Here is my code:
select distinct SecurityId, Identifier1, Identifier2, Identifier3 into #DistinctSecurityID from SecurityID
insert into
newSecurityID (FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3 )
select
FromDate=min( a.Date),
ToDate=isnull((
select min(b.Date)
from
SecurityId b
where
b.SecurityId=a.SecurityId
and b.Date>a.Date
and (a.Identifier1!= b.Identifier1
OR a.Identifier2!=b.Identifier2
OR a.Identifier3!= b.Identifier3)
),'20991231'),
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3
from
SecurityID a,
#DistinctSecurityID
where
#DistinctSecurityID.SecurityId=a.SecurityId
and #DistinctSecurityID.Identifier1=a.Identifier1
and #DistinctSecurityID.Identifier2=a.Identifier2
and #DistinctSecurityID.Identifier3=a.Identifier3
group by
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3,
a.SecurityId,
a.Date,
a.Identifier1,
a.Identifier2,
a.Identifier3
order by
a.SecurityId,
min(a.Date)
View 1 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
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
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
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
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
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
