Determine Value Based On Two Separate Date Columns
Jun 20, 2014
This is the logic I need to incorporate in to sql
if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H
I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.
Is there a way to look at multiple columns in a case statement?
CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end
View 5 Replies
ADVERTISEMENT
Feb 25, 2015
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
View 9 Replies
View Related
Mar 26, 2012
I have 2 tables:
TransactionsImport (which is the destination table)
TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600
View 2 Replies
View Related
Feb 15, 2012
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies
View Related
May 28, 2008
Hello.
I have three INT columns in a table that record the users birth year, month, and day.
BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)
I'd like to include a function in my query that will return their Age in years based on these three columns.
I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.
Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End
Usage (how do i pass the three columns into this function??)
SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21
View 3 Replies
View Related
Sep 2, 2015
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
View 13 Replies
View Related
Nov 9, 2005
Hi to all of you,
I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.
Help is highly appreciated
View 4 Replies
View Related
Apr 24, 2015
I am pulling down out of range values from a single table on one database to a different table on a different database on a different server (one i have full access to). Basically, it looks something like this:
id1 value1 prev_value1 value2 prev_value2 date prev_date
id2 value1 prev_value1 value2 prev_value2 date prev_date
id3 value1 prev_value1 value2 prev_value2 date prev_date
all the "prev"'s are null. I want to do one do one query that will get me the previous values and dates for each id from the original database. how to do this.
View 0 Replies
View Related
Mar 15, 2006
Hi,
I have a few different files in a directory.
I want to take each file and determine which columns and data types it has.
How can I know how many columns there are in a file?
Thank you.
View 10 Replies
View Related
May 16, 2008
Does anyone have a script that can do this?
To keep drilling down as far as possible regardless of complexity?
e.g.
A proc uses a couple of columns from a table and a another few from a view (drill down to view to find source columns), also a table function (drill down to find columns used) and so on
View 9 Replies
View Related
Apr 17, 2015
How to separate a long string and put them into three columns.
For example string
Toronto|Ontario|Canada,Dallas|Texas|USA,New York|New York|USA,Windsor|Ontario|Canada
I have a table with 5 columns, 3 columns are City, State and Country.
I would like to separate them and put those into categories
Continent City State Country Added
Toronto Ontario Canada
Dallas Texas USA
New York New York USA
View 1 Replies
View Related
Jun 5, 2014
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[Code] ....
View 9 Replies
View Related
Jun 12, 2014
I've a table as below
custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7
How to split csv values to new fields. so that the desired output should be as below
custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7
View 9 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Aug 31, 2013
SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
[Code] ....
In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?
1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M
2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30
3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999
View 3 Replies
View Related
Mar 23, 2006
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
View 1 Replies
View Related
Aug 18, 2014
Can you separate a domain attribute into two columns in the Excel Add-In?
We are using the Excel Add-In as the UI for business users and they want to see one of the domain attributes as 2 different columns.
The attribute is Store, currently it is displayed like this:
"123 {STORENAME }".
They want it displayed in two columns in the Excel document for easier filtering/sorting: Store Number and Store Name.
Is this possible? If one column is changed the other would change right along with it.
View 2 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Jun 27, 2007
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
Can I do that??
Any reply showing me the right way is appricited.
-Thanks,
Digs
View 3 Replies
View Related
Sep 19, 2006
andre writes "I have two questions:
1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??
Thank you!!"
View 1 Replies
View Related
Aug 18, 2015
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
View 3 Replies
View Related
Sep 14, 2015
I have values in two columns separated by commas, like shown below:
I need the Output Like
How to do this in SQL server ?
View 6 Replies
View Related
Jun 15, 2006
Hi There
I am trying to establish the date a database was last used.
At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.
I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.
And i need to use tsql, checking audit logs etc is not viable, can anyone help ?
Thanx
View 6 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Jul 10, 2006
Hi There,
Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?
for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?
Is Derived column transformation the best way to do it?
Thanks.
Sam.
View 6 Replies
View Related
Apr 22, 2008
My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it.
Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.
Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school.
View 5 Replies
View Related
Aug 30, 2007
I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?
View 4 Replies
View Related
Nov 5, 2015
I have a table that contains the SKU and a period of time the SKU was available to purchase (StartDtKey and EndDtKey). I am trying to write a query determine which skus were available during a given date range. The DtKeys correspond to a date in our calendar table and are in order. So DtKey 5 is 4 days after DtKey 1.
Code:
CREATE TABLE #SKU(
[SKU] varchar(10) NULL,
[StartDtKey] int NULL,
[EndDtKey] int NULL);
INSERT INTO #SKU VALUES ('Sku1',1,9)
,('Sku2',2,6)
,('Sku3',10,20)
,('Sku4',5,9)
,('Sku5',25,30)
Example:
Which SKUs were available on at least one day between DtKey 7 and DtKey 9?
Answer:
Sku1, Sku4
View 1 Replies
View Related
Aug 12, 2014
MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
View 5 Replies
View Related
Feb 1, 2007
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies
View Related
Oct 2, 2006
I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.
View 4 Replies
View Related
Nov 7, 2012
Is it possible to determine an index create date in SQL Server 2008 R2? The index in question is not a primary key.
View 8 Replies
View Related
Jan 11, 2007
I need to separate the date stamp (which looks like this 2006-10-05 09:08:41.000) into the date in this format 05OCT2006 and then the date stamp separate in this format 09:08. Thanks!
View 2 Replies
View Related
