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 RepliesI 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')
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.
Hi
I am very new to SQL so please excuse me if my question seems too easy to answer.
Basically I need to populate a form based with records based on the criteria that the next mot date and todays are +/- 10 days.
i.e if todays date is 13/05/07 and the next mot date is 3/05/07 or later OR 23/05/07 or less then various fields will be shown in the form.
Can you please help.
Thanks
Paul
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
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
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 RelatedI am completely new to SQL and I am trying to filter a table on one column to build a view. The column is 5 characters. For most of the records only the first three characters are present the last two are spaces. I would like to search for the first three characters but include records that contain the last two characters. I am looking for 001, 006, 061, 064, 070, and 227.
[RD_ID] nvarchar length 5
I have tried ‘001’ or ‘001 ‘ or ‘001*’
Thanks for any help.
In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?
View 4 Replies View RelatedI have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
[code]....
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)
(
@date_min smalldatetime='1/1/1910',
@date_max smalldatetime='1/1/2010'
)
AS
SET NOCOUNT ON;
SELECT tbl_1.*
FROM tbl_1
WHERE (ap_dateIn BETWEEN @date_min AND @date_max)
thakns for your time,fellaz!
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
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
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
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
View 1 Replies View RelatedHello All,
I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'
Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'
It works fine! Can anybody explain me why it is so???
Many thanks in advance…
Cheers,
Santosh
Ok...
Here is what I want to to:
I have a number of tables, but this query concerns only two of them: Employee, Disposition, with a one to many relationship on the phone number.. Employee has personal info, and dispostion is a large table that keeps track of transactions. There is a unque_id for every transaction.
I want to pull some information. A date is assciated with each trans action on the disposition table. I want to be able to pull up all the unique employee phone numbers from a disposition table but only the latest date they made a transaction. My result should be only one phone nbr, one transaction number, and the date of the last transaction, per employee phne_nbr. But I can't seem to be able to pull this.
This query gets me the max date called per phne_nbr:
select 'Max'= max(dte_call), phne_nbr into TestTable
from Disposition
group by phne_nbr
The problem arises when I need to get the unique_id for that transaction, as I need it for other queries...The code below does not work...I get duplicate records and so forth.
select 'Max'= max(dte_call), phne_nbr, unique_id into TestTable
from Disposition
group by phne_nbr, unique_id
This shouldn't be that hard!! (((I tried a variation usin select distinct with the filed I wanted to be distinct in () otherfield, other field... but the distinct is not limited to the field in (), it gets the distinct record for the entire select ))) I must be missing something simple here. Can anyone help!!!
I am trying to create a SELECT statement that would allow my users to type in a date parameter like 6/25/04. My SELECT statement would then pull all entries for that date. The problem I am running into is that it seems SQL wants the date to be parameterized as between 6/25/04 and 6/25/04 11:59:30 PM. Is there any way around that? Again I would like my users to simply enter 6/25/04 and have all entries pulled. Thanks for any help.
View 1 Replies View RelatedI have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".
I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?
Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join
(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'
Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.
worknumber date_created itemcode Notes
-------------------- ----------------------- -------------------- -----------------------------------------------------------------------------------
HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired
HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2.
HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working
HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted
HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working
HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted
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,
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.
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.
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
[code]....
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'
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.
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
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
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 RelatedI 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.
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.
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
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
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