Discovering AGE Based On Date Columns

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


ADVERTISEMENT

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

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

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

Discovering Databases

Apr 26, 2002

I've been given the task to discover all SQL Server databases within the corporation. However, the only information given to me were all NT/2000 servers. Does anyone know a way to discover whether there is a SQL Server database (6.5,7.0,2000) on servers? Could I discover them possibly thru the 1433 port?

View 4 Replies View Related

Discovering Sql Server Characteristics

Nov 5, 2006

Hi everyone, I'm a complete novice in databases :|
I have SQL Server 2005 Express Edition on my personal computer and need to connect to it through Java.
Therefore I need some information about it and don't know where to look. Some of them are:
=> the address of the server to connect to. This could be a DNS or IP address (I have installed the Server on my pc).
=> the instance to connect to (how can i find out where the instances are and which they are ?)
=> the port to connect to (i have no idea what this is at all)
=> the db is set to integrated security or do i have to give a username and password.
=> The name of the database to connect to (how can i find out which databases are available, if any)
note that there is no enterprise manager available. just a configuration manager, error and usage reporting, and surface area configuration.

many thanxs for reading my question.

never no lament

View 3 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

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

Calculated Columns Based On Multiple Columns?

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

Reporting Services :: Expression To Return Date Value Based On Most Recent Date Only

Oct 7, 2015

How do you Write an expression to Return the date value based on the most recent date only?

Eg.  Date 10-7-2015,  action - done, notes - all items fixed.
       Date 4-5-2016, action - not yet done, notes - buying parts

All these dates are returned based on a search parameter based on a project number.  I only want the most current date and associated fields displayed.

View 4 Replies View Related

Problem Inserting Integers And Date In A Sql Server 2005 Datatable Row And Selecting It Afterwards Based On The Date

May 4, 2007

Hi,
 
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6  integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry.  Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows:  1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
 
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
 
and the following code to get the row back (to put in arraylist):
 
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())?
 WHERE LDate =  '" + DateTime.Today.ToString() + "'"
 
Which is the correct syntax?  Is there a better way to insert and select based on the date?
 
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
 
Any help would be greatly appreciated!
 
Grtz
 
Pascal

View 5 Replies View Related

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

View 2 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

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

Insert Date Into Column Based On Date Field

Feb 26, 2008



Hi,

I need to insert into a column (lets say column x) a date based on the date on another column (lets say column y).

What I need is:



Take the day and month from column x (all records are formated yyyy-mm-dd)

Place it in column y

The yyyy in column y should be - currenct year +1 and no the year in column x.
All help welcome.

View 9 Replies View Related

Identity Columns And Date Columns On Transactional Replication

Sep 16, 2006

Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

View 1 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Rows Based On Columns

Feb 3, 2008

I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.

I have a Table/View called [Shipment] with the data below.

ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA

I want the following as my result.

ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA

Thx.
Rav

View 6 Replies View Related

How To Do Update Of Select Columns Based On...

Jun 21, 2007

the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.

View 7 Replies View Related

Output Several Columns Based On Rows

Jun 9, 2008

I'll show my schema first, then I'll explain what I'm doing:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------


--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------


--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------


--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------


--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------


--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------



There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).

A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.

For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:

----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3

So in that example, that event had 3 sessions.

Right now, I can only output E_Title and ER_Name, I don't know how to output the session information

View 2 Replies View Related

Multiply Of Columns Based On New Group

Dec 19, 2013

Product IdIsPrimaryQuantity
P0011
P001.102
P001.204
P001.305
P0021
P002.106
P002.207
P002.309
P002.4010
P002.5011

Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL

Product IdSubProductQuantity
P001 40
P001 P001.3NULL
P002 41580
P002 P002.5NULL

This is same as [URL] ....

View 5 Replies View Related

Sum Columns Based On A Single Column Value

Sep 10, 2014

I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.

If I have the following view:

IDLNFNCDTotal
123456MearsBenP12
123456MearsBenA2
123456MearsBenT6
234567NortonSusanP15
234567NortonSusanA2
234567NortonSusanT2
234567NortonSusanE1

I would like something like this:

IDLNFNPresentAbsentPercentage
123456MearsBen18290.0
234567NortonSusan17385.0

I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.

I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.

SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
((SELECT SUM(TotalAHD) AS Expr1
FROM SumAHDforAttndPercentages AS p

[Code] ...

View 2 Replies View Related

SQL Count Records Based On 2 Columns

Oct 15, 2007

Newbie alrt...

I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.

I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.


here is the chunk of code in question... all RF_variables are request.form variables


cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")


if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")

if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if




Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.

Thanks

View 1 Replies View Related

Why Can't I Use Columns Based On Case Down In My Where Clause

Sep 13, 2007



I am writing a fairly simple sql, and I would like to write something like




Code Snippet
select

firstname as firstname,
case

when firstname = 'Peter' then 'yes'
else

'no'
end as whatever

from

MyTable
where

whatever = 'yes'



And this should then select out the rows where column number 2 is 'yes'.

It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.

But why?


View 4 Replies View Related

Selecting The Rows Based Off Of Unique Columns

Mar 18, 2007

Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ...  There will be entries where there can be duplicate customerFirst and customerLast names.  I would like to just return a single entry of the duplicate names and all associated row information.  IE: [customerID], [customerFirst], [customerLast],            [ email],             [program]         01               Bill                 Smith             bill.smith@hotmail.com    ymca        02               Bill                 Smith             bill.smith@hotmail.com    Sports        03               jon                   doe                 jon.doe@hotmail.com    AAA        04               jon                   doe                 jon.doe@hotmail.com    Ebay          05               Paul                 Sprite             paul.sprite@hotmail.com    Rec Desired Returned result:        01               Bill                 Smith             bill.smith@hotmail.com    ymca        03               jon                   doe                 jon.doe@hotmail.com    AAA
         05               Paul                 Sprite             paul.sprite@hotmail.com    Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr);         dAdapter.Fill(pocDS, "Data Set");        However this is throwing up an error when i build the app:  An expression of non-boolean type specified in a context where a condition is expected, near ')'.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.

Source Error:




Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison  1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry  

View 12 Replies View Related

T-SQL (SS2K8) :: How To Repeat Columns Based On Rows

Mar 6, 2014

I have two columns which needs to repeat based on ID and number of distinct rows in that ID.

ID Date Created
1 1/1/2012 Sudheer
1 1/2/2013 Sudheer
1 3/3/2013 Sudheer
2 1/2/2014 Veera
2 2/5/2015 Veera

Results

ID Date Created Date Created Date Created
1 1/1/2012 Sudh 1/2/2013 Sudh 3/3/2013 Sudh
2 1/2/2014 Veera 2/5/2015 Veera

View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

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

T-SQL (SS2K8) :: Insert Columns Based On Condition

Aug 15, 2015

I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query

Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.

ALLOW INSERT:

Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug

DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)

Column1 Column2
A0007 23-Mar
A0007 02-Feb

FINAL OUTPUT Should be

Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep

Discard Insert when Column1 ID's comes with Different dates.

View 4 Replies View Related

UPDATE On Table Based On Matching Columns

Apr 8, 2008

I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".

ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A

I want the result to be:

ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC

I am stumped as to how to do this - any help or advice would be appreciated.


The only thing I've come up with is:

UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')

But when I run it, it says "0 rows affected".

View 5 Replies View Related

Query To Return Entire Row Based Upon Two Columns?

Aug 12, 2013

I have a database that has a table that contains basic demographics

•First_name
•Last_name
•Address
•City
•State
•Zip phone
•email
•dob
•etc
•etc

Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.

View 5 Replies View Related

Insert Columns Into Table Based On Condition?

Jan 30, 2015

My requirement is below.enhancing the T- sql query as I was told not to use SSIS.

Insert data from Table A to Table B with conditions:

1. Truncate gender to one character if necessary.

2. Convert character fields to uppercase as necessary.

3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.

In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '

default outputname :Consider Res Address; Outputname:Consider mail address.

and mapped as:

(Table A) mail_street_address---street address(Table B)

(Table A) mail_city----------------City(Table B)

(Table A) mail_Zip----------------Zip(Table B)

(Table A) mail_state-------------state(Table B)

(Table A) res_street_address--street address(Table B)

(Table A) res_city---------------City(Table B)

(Table A) res_Zip----------------Zip(Table B)

(Table A) res_state--------------state(Table B)

I want to do the same with T-sql code too:

I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):

Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix

[code]....

View 2 Replies View Related

Renaming Columns Based On Data Using Transact Sql

Jan 18, 2008

A happy belated New Year to all!

Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?

example

Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'

I want to use the month and year of the date to rename the column.

Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'




Thank you in advance for any help you can offer.


TMendez

View 5 Replies View Related

SQL Query To Sum Two Columns Values Based On Its Record

Mar 27, 2007

May i know what is the SQL query to create another intermediate column which is used to store the sum of two columns of each and every record. Thank you.

View 16 Replies View Related

Need Gridview To Display Different Columns Based On New SELECT Statement

Apr 23, 2008

I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing.  Does anyone have any suggestions?
Thanks,
Brad

View 9 Replies View Related

SQL Server 2012 :: Inserting New Columns Based On Condition

Feb 25, 2014

I have a very simple query like the following…

SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)

From this query, its returning me a result set of something like below:

--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------

RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER

Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…

Something for example (I am not trying to write a condition: my question is how to write it),

if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.

Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…

See the attachment for better formatting...

---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH

View 7 Replies View Related







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