Transact SQL :: Using Different Date Criteria

Nov 16, 2015

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.

View 9 Replies


ADVERTISEMENT

Form Criteria Based On An Entered Date And Date Today

Mar 16, 2007

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

View 2 Replies View Related

Transact SQL :: Selection Criteria In DB1 - Need Fields In DB2

Sep 24, 2015

I need to query a table in Database 1 then, using a primary key, pull the columns from a table in Database 2.

The query I'm using in db_1 works:

SELECT * FROM db_1.table_1 WHERE  (hcpcs_cd = '20552' AND srvc_units_num>'1 ' AND claim_dos_yr >'2012') 

But what I really need are the columns associated with these records in db_2.table_1.  There is a primary key in both tables.

Ultimately what I need is all of the columns in db_2.table_1 and the hcpcs_cd, srvc_untis_num, and claim_dos_yr from db_1.table1

View 11 Replies View Related

Transact SQL :: Function To Return Unit Price Based On Various Criteria

Apr 30, 2015

We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation.  What we need to do is break up the total quantity passed to the function and return the total of prices found.  In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.

Low Qty    High Qty    Fee        Actual Qty        Price
0                  7500        0.025            7500           187.50
7501           15000        0.020            2221          44.42

Below is the table definition that we have to work with (ugghh).

CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,

[Code] ....

What we have so far is shown below.  The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range.  So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628

The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort. 

ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)

[Code] ....

View 9 Replies View Related

Date Range Criteria

Mar 4, 2008

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!

View 4 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Trouble With Simple MS SQL Date Criteria

Oct 4, 2005

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 Related

Problem With Date In Selection Criteria

Apr 10, 2001

Hello 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

View 4 Replies View Related

Question About Pulling A Max Value On A Date For On Specified Criteria

Dec 20, 1999

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

View 2 Replies View Related

Help With DATE Criteria In SELECT Statement

Jun 25, 2004

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 Related

Select Most Recent By Date Plus Other Criteria

Feb 1, 2015

I 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

View 5 Replies View Related

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

T-SQL (SS2K8) :: Get Start And End Date Based On A Criteria?

Apr 17, 2014

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'

View 9 Replies View Related

Select Criteria Based On Date - Cleaner Way To Write?

Sep 7, 2005

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.

View 2 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

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )

[Code] .....

I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3.  

If the declined date is filled in.  Then I want to get the row, when the Datein column value is greater then the declined date only.

I tried grouping it by max date, but   i got an error message when trying this out.  Against the code  

WHERE MAX(Datein) > Declined

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.  What do I need to do to get both my outputs working? 

View 9 Replies View Related

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

Transact SQL :: How To Get Date Where Planned Delivery Date Key Is Less Than Getdate

Aug 13, 2015

How to get the date where planned delivery date key is less than getdate()

I want to make a view where I want put the condition :

select CONVERT(datetime, CAST(planned_delivery_date_key AS CHAR(8)), 101) < CONVERT(datetime,getdate(),101)
from qlk_fact_sales_back_order

View 2 Replies View Related

Transact SQL :: Date Variables That's being Set Around Current Date

Nov 23, 2015

I have a the following date variables that's being set around the current date.  how to adjust it to work around a date variable @Date instead of the current date?   Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly.  I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.

Declare @EndOfLMPriorYear  Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date =  EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))

View 3 Replies View Related

Transact SQL :: Getting Closest Date From A Given Date Column

Sep 28, 2015

Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.

please see below DDL and sample data.

--this is the result from my last cte process
Create table #sample
(ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)

insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')

[Code] .....

Expected result:

ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000

[Code] ....

View 8 Replies View Related

Transact SQL :: Outputting Date Difference Between Two Date

Sep 11, 2015

I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?

View 6 Replies View Related

Transact SQL :: Date Arithmetic With Date And Datetime

Sep 21, 2015

if I do this:

print @@version
print 'arithmetic with datetime'
go
begin try
declare @datetime datetime = getdate()

[Code] ....

... I get this:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) 
Mar 26 2015 21:18:04 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

arithmetic with datetime
@datetime = Sep 22 2015  1:39PM
arithmetic with date
Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

Why the inconsistency? Datetime is lenient in this regard - I can even do set @datetime += 0.5 (although the meaning is less intuitive).

View 6 Replies View Related

Transact SQL :: Expand Date Within Date Parameter

Jul 23, 2015

I am working into a t-sql where i need to expand the date within a date parameter. For example:

Start Date = '1/1/2014'
End Date = '12/31/2014'

I want to expand this as:

01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to 
12/2014

View 4 Replies View Related

Transact SQL :: How To Get Date - Last Monday From Given Date

Jun 15, 2015

How can I get a date that would be last/previous Monday from given date?

For example for given 22/01/2015 it would give 19/01/2015.

View 16 Replies View Related

Transact SQL :: Max And Min Date

Jun 19, 2015

I have date in my table like this

id           from           to
1        01/01/2013           02/01/2014
1        02/02/2014           12/31/9999

so for same id i want min (from ) and max(to) date  in my select statement.

Example ,  for id 1 
id             from             to
1          01/01/2013       12/31/9999

View 3 Replies View Related

Transact SQL :: Get Max Date For Data Available For All IDs

Jul 19, 2015

I have a temp table

drop table #temp
create table #temp (id int, dat datetime)
insert into #temp values(1,'2015-03-01')
insert into #temp values (2,'2015-02-01')
insert into #temp values (2,'2015-01-01')
insert into #temp values (3,'2015-01-01')
insert into #temp values (3,'2015-03-01')
insert into #temp values (3,'2014-12-01')
insert into #temp values (2,'2014-12-01')
insert into #temp values (1,'2014-12-01')
select * from #temp

The result i am expecting is  2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result. 

View 11 Replies View Related







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