Counting The Years In Multiple Datetime Values
Jul 19, 2007
am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance
View 8 Replies
ADVERTISEMENT
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Aug 29, 2007
Hi All,
I have the following table "Project"
-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following
Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003
February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
View 5 Replies
View Related
Nov 5, 2015
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
View 9 Replies
View Related
Aug 31, 2015
Developing a measure which displays the difference of two values from the selected years.
An example : Show the difference of the sales amount from 2013 and 2015.
Since i am not really into mdx or calculated members.
View 6 Replies
View Related
Sep 15, 1999
hi, how can I count the number of letters in a string and get the result in a variable,
here what Ithought,
declare @counter int
select @counter = (.... what is the code that count a string and retrun numeric value)
thanks for your help
Ali
View 2 Replies
View Related
Nov 12, 2004
I know this question has been asked before but this is a little different and I can't seem to get my head around it right now.
What i have is a table like so:
ID1 ID2 DESC
100 24 something1
100 24 this is a test
100 24
100 25 somethingelse
101 36 something
101 37 something else altogether
What i need is to determine which ID1 value has the same ID2 value listed more than once WITH the description filled in. If there is no description filled in even though the ID2 may be listed twice on one ID1, then it's ok and don't want it displayed in the query.
So, in this case, ID1 of 100 has ID2 24 listed twice with a description on both ID2's (of 24). this is what I need to show up in the query like so:
ID1 ID2 DESC
100 24 something1
100 24 this is a test
Could someone give me a hand with this? I've found similiar problems...but haven't been able to apply those answers to this.
Thx
View 7 Replies
View Related
Jul 20, 2005
I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?
View 1 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Feb 22, 2008
Declare @MYTable Table
(
RecordID int identity,
EmployeeID int,
JobCode varchar(4)
)
Insert into @MYTable Values(1,'123')
Insert into @MYTable Values(2,'123')
Insert into @MYTAble Values (1,'123')
Insert into @MyTable Values (2, '123')
Insert into @MyTable Values (3,'123')
Insert into @MYTable Values (1,'222')
Insert into @MYtable Values (3,'222')
Insert into @MYTable Values (1,'222')
Insert into @MyTable Values (4,'222')
Insert into @MyTable Values (5,'123')
Insert into @MyTable Values (6,'123')
Insert into @MyTable Values (7,'457')
Select * from @Mytable
The each employee can come to this table multiple time however
the job code should be different for each every single record.
So If I have to check this error and see which record has been repeated with
the same Employee ID and JobCode and how many times. how would i do that
Thanks,
View 15 Replies
View Related
Feb 19, 2013
I have a large table that tells me customers and mailings (~4 million rows). So for each customer, I see what mailing(s) they received. It is at the mailing level so if a customer received 2 mailings, their will be 2 records for that customer number, one for each mailing they received. There are 3 different mailings.I am trying to calculate the count of customers that received all 3, as well as those that received 1&2 only, 1&3 only, and 2&3 only. For example:
CustNo Mailing
Tim 1
Tim 3
Dan 1
Dan 3
Jane 1
Max 1
Max 2
Max 3
Lauren 2
Lauren 3
Results:
All 3 - 1
1&2 only - 0
1&3 only - 2
2&3 only - 1
I have tried doing some funky table joining and EXISTS, but the query runs and runs because of how much data it has to sift through. Here is the code I am using to find Mailing 1&2 only customers, but I am not even sure it is getting me the right numbers. There are 2 different types of customers as well which is why I have custtype in there.
Code:
select a.custtype,count(distinct a.custno)
from myTable a
inner join (select distinct custno from mytable where [mailing]=2) b
on a.custno=b.custno
where a.[mailing]=1
and not exists(select * from myTable c where a.custno=c.custno and c.[mailing]=3)
group by a.custtype
View 14 Replies
View Related
Jul 14, 2014
Consider the following dataset:
ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B
I need to count the number of IDs for each MD and each TYPE like this:
MD|A|B
JOHN|1|2
BOB|2|3
I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD
The query above results in:
MD|TOTAL
JOHN|3
BOB|5
View 5 Replies
View Related
Mar 29, 2006
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
View 1 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Mar 29, 2007
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Feb 2, 2006
guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated
Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found
thanks again
this forum is the best
rik
View 5 Replies
View Related
Mar 5, 2008
hello all,
i have table - compmail in that i have compmailid, compmaildetail and compmaildate - columns
compmailid compmaildetail compmaildate
-------------------------------------------
94341 comp mailing list 2008-1-23 16:41:17.037
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108229 comp mailing sales 2008-1-27 15:13:37.207
108740 comp mailing usages 2008-1-27 16:44:17.517
108261 comp mailing stats 2008-1-27 14:42:55.780
so i want maxcompmailid for each date
so output will be like:
compmailid compmaildetail compmaildate
-------------------------------------------
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108740 comp mailing usages 2008-1-27 16:44:17.517
if i m doing:
select max(compmailid),compmaildetail,max(compmaildate) from compmail
group by compmaildetail
not giving me correct results which i want
can anyone have idea?
View 4 Replies
View Related
Apr 18, 2007
greets again folks,
The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.
The date is usually correct but the time is always hours off.
Is there some way to get the time part of the DateTime to be correct?
Do I have to write code to set the time when the user logs in?
Thanks a mil!
View 9 Replies
View Related
Jan 30, 2008
How can i return how much of the timespan in table2 is in the timespan in table1?
table 1 - email
-------
Login time: 2007-12-12 13:14:26.363
Logout time: 2007-12-12 14:15:58.803
table2 - phone
------
Login time: 2007-12-12 12:11:08.343
Logout time: 2007-12-12 14:13:10.847
View 9 Replies
View Related
Nov 2, 1998
How Can I BCP in datetime values in nondefault format, wich is "mdy" in U.S. English.
Thanks in advance, for any help.
View 1 Replies
View Related
May 22, 2008
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
View 8 Replies
View Related
Sep 12, 2006
Hi,
I have two tables TABLEA and TABLEB. They both have the same columns. I need to insert data from TABLEA to TABLEB based on the following conditions
TABLEA Structure with Sample Data
PKEY
Attribute
Value
Updated_DateTime
1
A
2.2
08:30AM
2
A
3.3
08:40AM
3
B
3.2
08:40AM
4
C
1.1
08:00AM
5
C
1.2
08:43PM
6
D
1.01
08:42AM
This is the sample data for TABLEB
PKEY
Attribute
Value
01
A
2.2
02
F
3.3
03
B
3.2
04
D
1.01
Now..When inserting data into TABLE B ,
->I need to check if the Attribute already exists in TABLE B .
-> If it already exists then I must update the Value feild with the latest value from TABLEA based on updated_datetime.
(Ex: Attribute 'A' already exists in tableB so i need to update the VALUE feild in TABLE B to 3.3 which is the latest updated value in TABLEA at 08:40AM)
-> If the Attribute doesnt exist then I simply need to insert that row into TABLEB
Can some one please post the query or the procedure that I can use for the above requirement.
Any help greatly appreciated.This is a urgent requirement for me.Please help
Thanks
View 1 Replies
View Related
Jan 20, 2007
Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now: Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>" SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number"> <DeleteParameters> <asp:Parameter Name="Ticket_number" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> <asp:Parameter Name="Ticket_number" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> </InsertParameters> </asp:SqlDataSource> I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate: <InsertItemTemplate> column1: <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'> </asp:TextBox><br /> Employee_ID: <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:TextBox><br /> Problem_description: <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:TextBox><br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"> </asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"> </asp:LinkButton> </InsertItemTemplate> <ItemTemplate> column1: <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br /> Employee_ID: <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:Label><br /> Problem_description: <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:Label><br /> Date_submitted: <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'> </asp:Label><br /> Ticket_number: <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'> </asp:Label><br /> <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"> </asp:LinkButton> <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"> </asp:LinkButton> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New"> </asp:LinkButton> </ItemTemplate> </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work. Any help would be greatly appreciated. Thanks!
View 2 Replies
View Related
Aug 17, 2005
If myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?
View 6 Replies
View Related
Jul 12, 1999
Hi,
When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?
View 1 Replies
View Related
Jan 15, 2004
Hi all...
how I can obtain the sum of a datetime field as aggregate function?
Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?
Thanks..
Massimo
View 7 Replies
View Related
