Transact SQL :: Select Conditional Dates
Oct 15, 2015TSQL statement, it should select the first date of the month at 11:59 PMÂ as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies
ADVERTISEMENT
MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem
Dec 13, 2004Hi All,
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
Regards, Trog28
Transact SQL :: How To Create A Conditional Scheduled Procedure
Aug 19, 2015I have a table (currently with no constraints or relationships) with two columns:
Employee_CodeEarned_Leave_Balance
The leave balance should be updated automatically on a daily basis using a formula. I understand this can be done using stored scheduled procedures. To give you a full and clear picture, this is what I need:
IF [Employee_Code] LIKE 'KUW%'
[Earned_Leave_Balance] = (Today - [Employment_Date]) * (42 / 365)
ELSE
[Earned_Leave_Balance] = (31/12/CurrentYear - [Employment_Date]) * (30 / 335)
Transact SQL :: How To Get Results Based On Conditional Where Clause
Jul 14, 2015My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum             PolicyStartdate
123GÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/01/2012Â Â Â Â
456DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 02/16/2012Â Â Â Â Â
789AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/21/2012
163JÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex:Â policynum ='456D'Â Â Â Â It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
Transact SQL :: Iterate Through Table Rows And Do Conditional Update
Aug 11, 2015I have a table with the following fields:
ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
Transact SQL :: Conditional Update A Field From Multiple Tables
Sep 9, 2015Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies View RelatedTransact SQL :: How To Sum All Dates
May 29, 2015I want to sum all the dates and show the total date sum. How can I do that in SQLÂ
For these dates
00:35
00.40
00.55
03.50
Date Select Query - Select Between Two Dates
Aug 22, 2006have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
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.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
Conditional Select
Nov 8, 2007I am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.
Code:
SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END
FROM HEAT.dbo.CallLog C,
(SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution
FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P
WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta')
GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution
If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.
Thank you
Court
Conditional SELECT
Jul 20, 2005Dear GroupI'm having trouble with the statement below. I tried CASE and IFwithout success. What I'm trying to do:There is a field in the database called Business_TelNo. If the fieldhas some value, I would like to return a generated field(LaBusinessTelNo), which is the label of Busines_TelNo, reading'Phone:'If Business_TelNo has no value, the label should be set to ''.Something like this:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNoEND ELSE BEGIN SELECT '' AS LaBusinessTelNo ENDFROM i2b_vw_contactThis is working:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,'Phone: ' AS LaBusinessTelNoFROM i2b_vw_contactPS: I know it would be much easier to add some logic in theapplication but need to do this in SQL.Thanks very much for your time and efforts!Martin
View 2 Replies View RelatedTransact SQL :: Comparing Two Dates
Aug 4, 2015I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
View 6 Replies View RelatedTransact SQL :: Get Expiry Dates From CC
Sep 18, 2015I have a Column CCExp Varchar(7) Â that stores data like below
NULL
'01/15'
'02/15'
'03/15'
'04/15'
'05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT Â CustomerBillingInfoID ,
    CCExp
FROM Â Â CustomerBillingInfo
WHERE Â LEFT( CCExp, 2) < MONTH(GETDATE())
    AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2)
and CCExp NOT LIKE Â '#%'
and CCExp not like 'NULL'
Select Conditional Aggregates
Jan 16, 2004I want to create a query to summarize sales for each of the last five weeks and group by product type ie.
select product, sum(amount this week), sum of (amount last week) ... group by product
my sales table looks like this
product date amount
x d1 xx
x d2 xx
y d3 xx
y d4 xx
x d5 xx
How can this be done?
thanks
Conditional Select Statement
Mar 23, 2004Hello dbForumers,
Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?
Thank You!
Conditional Sum With Select Statement
Feb 24, 2014Table with water consumption per month and customer.
I want to sum up total consumption per customer with a select statement
Conditional Column Select
Aug 9, 2006Is there a statement to change the column in a select clause?
For example:
select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.
Transact SQL :: How To Compare More Than Two Dates In Server
Oct 10, 2015I have three date columns startdate,enddate,rmd
I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate
id startdate enddate rmd
1 Â 10-oct-15 Â 16-oct-15 Â Â 11-oct-15
2 Â 11-oct-15 Â Â 14-oct-15 Â Â 11-oct-15
3 Â 09-oct-15 Â Â 11-oct-15 Â Â Â 10-oct-15
4 Â 07-oct-15 Â Â Â 08-ot-15 Â Â Â Â 07-oct-15
5
if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3
Transact SQL :: Repeatedly Assign Set Of Dates To Each ID?
Nov 18, 2015I have a table with 3 columns , let say (PatientIDÂ int, AppointmentDate date, PatientName varchar(30))
My source data looks in below way..
PatientID       AppointmentDate     PatientName
 1                01/01/2012         Tom
 2                01/10/2012         Sam
 3                02/15/2012         John
I need output in below way..
PatientID       AppointmentDate     PatientName
 1                01/01/2012         Tom   (actual patient record)
 null             01/10/2012         Tom
 null             02/15/2012         Tom
 null             01/01/2012         Sam
 2                01/10/2012         Sam    (actual patient record)
 null             02/15/2012         Sam
  null             01/01/2012         John
 null             01/10/2012         John
 3                02/15/2012         John    (actual patient record)
I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
Â
Create table sample (PatientIDÂ int null, AppointmentDate date null, PatientName varchar(30) null)
Insert sample values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')
Transact SQL :: Looping Through Dates In Server
Jun 21, 2015I have a sql code like this:
declare @currentDate datetime,@enddate datetime;
select @currentDate = '06/1/2015';
select @enddate='06/05/2015'
while @currentDate <= @enddate
begin
-- do whatever is needed
select @currentDate = dateadd(DAY,1,@currentDate);
select @currentDate as currentdate
end
In result am not getting the current date. am getting date from 06/02/2015.i want to get records from 06/1/2015 to 06/05/2015. total 5 records..but now i am getting only 4 records.
How I can get full records.
Transact SQL :: List Date Between Dates
Aug 22, 2015i have a table like below
CREATE TABLE #Test (FromDate DATE,ToDate DATE)
insert into #Test VALUES ('2015-08-08','2015-08-11')
insert into #Test VALUES ('2015-08-13','2015-08-16')
insert into #Test VALUES ('2015-08-19','2015-08-21')
SELECT * from #Test
drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE)
insert into #Result VALUES ('2015-08-08')
insert into #Result VALUES ('2015-08-09')
insert into #Result VALUES ('2015-08-10')
insert into #Result VALUES ('2015-08-11')
[code]....
Best Practice For Conditional Insert Else Select?
Aug 7, 2007I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN
INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END
(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
Transact SQL :: Check If A Date Is Within A Range Of Dates?
Aug 20, 2015Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
Transact SQL :: Date Difference Between Two Dates In Due And OverDue
Sep 25, 2015I want a difference in daysÂ
Select datediff(dd,Target_Date,Achv_Date)Â
Now , checks areÂ
1] when target date greater than achv_Date the difference should be greater than 0Â
means for FileIDÂ 77608Â
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
Â
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside  achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%Â
Transact SQL :: Combining Parts Of 2 Dates Into Third Date
May 7, 2015In a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.
Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'
I have been messing around with datepart and dateadd with no luck.
Transact SQL :: Displaying POs With Due Dates Greater Than Three Days
Apr 24, 2015What I want to see is how to show PO's who's due dates  are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000
Transact SQL :: Trying To Pass Dates To Stored Procedure
Oct 13, 2015We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:
Declare @FromDate as date
Declare @ToDate as date
Set @FromDate=Convert(date,'09-01-2015',110)
Set @ToDate= Convert(Date,'10-13-2015',110)
Select @FromDate
[code]...
How can I keep the date as a date and still pass it to stored procedure.
Transact SQL :: Aggregate 2 Dates And Concatenate Into New String
May 21, 2015Have this table
ACCOUÂ NAMEÂ Â Â Â Â NAMEÂ TODATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â IDÂ Â Â Â EDUCATÂ Â Â EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647Â MILUCON Empl1 1900-01-01 00:00:00.000Â 9751Â VCA-VOLÂ 2018-02-11 00:00:00.000
Need to get it like
ACCOUÂ NAMEÂ Â Â Â Â NAMEÂ TODATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â IDÂ Â Â Â EDUCATÂ Â Â EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647Â MILUCON Empl1 1900-01-01 00:00:00.000Â 9751Â VCA-VOLÂ 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
Transact SQL :: Overlapping Dates For Same Member Query
Oct 18, 2015IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
  DROP TABLE #OverLappingDates
  CREATE TABLE #OverLappingDates
(
  MemberID      Varchar (50),
  ClaimID        Varchar(50),
  StartDate       DateTime,
  EndDate        DateTime
)
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records  (MemberID 1 has 2 claiims overlapping dates).         Â
Transact SQL :: How To Get Four Quarter End Dates Based On Given Date
Jun 3, 2015I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that
Transact SQL :: Pull Dates From Database And Then Compare
Jun 10, 2015I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.
Database: DB2, Customer information housed here
Columns:
UTOFMM - Month (2 character, numeric)
UTOFDD - Day (2 character, numeric)
UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc)
UTOFCV - Century Value (2 char, numeric. Â 0 = before 2000, 1 = in or after 2000)
I need to concatenate the date to be "sql" friendly, and then compare to today's date. Â It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix. Â This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.
Add Customer ID
Update [responder].[Temp_RX_CUSTOMERS]
set CustomerID = lf.UTCSID
from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join
[HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID
where lf.UTOFMM = 0
GO
Transact SQL :: Query To Get Date Span Between Dates
May 23, 2015I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN
, CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit
, pv.TicketNumber
, vstatus.Description AS VisitStatus
, doc.ListName AS Doctor
[Code] ....
Stored Procedure With Conditional Select Statment
Feb 6, 2007Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
Transact SQL :: Check Constraint With UDF To Prevent Overlapping Dates?
Oct 6, 2015I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
 IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
  RETURN 1
  RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message:Â The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
drop table t1
drop function dbo.dt_overlap