Hi,
I am doing Select into to make a table (I want to skip the log), but I want to add an IDent column to the new table. Can I do this somehow in the Select into? I have tried a couple of approaches, but have come up short....
Thanks,
Judith
Hello
I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}
Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;
I get 3 record, the dates is the last, but the lat. and long. is wrong...
Thanks,
Laci
I'm using this query to update the top 1 row:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
set rowcount 0
The key field on the table is not an identity field.
Is there a way to get that KEY field's data (for the row that was updated)?
I tried this just to see what would happen and it returns null:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
select @@IDENTITY
set rowcount 0
Thanks!
Reseeding in SQL Server gets different results based on whether the table has ever had data in it.
ref: http://cs.thycotic.net/blogs/bryant_smith/archive/2006/01/17/65.aspx
I am having the same problem. Any suggestions ?
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
Thanks,
Steve
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key time column selection. e.g, I want to predict monthly sales amount, then what column in date dimension should I choose to be the key time column? Is it calendar_date (the key of date dimension) column or calendar_month?
Thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
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 RelatedHow can I obtain just the time portion from a date/time column?
My data contains "2008-05-19 09:30:00.000"
Actually, all I want/need is the hh:mm part of it.
Thanks,
Walt
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
View 2 Replies View RelatedExample, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies View RelatedUsing SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View 11 Replies View RelatedUsing SQL Server 2000. How can I refer to one alias in another column?
E.g., (this a contrived example but you get the idea)
SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
View 1 Replies View Relatedi need help to get the value of a column which is selected from a column of another table....
View 3 Replies View Relatedi dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
how can i retrieve two columns from sqltable with - seperating the results and a common column name
someth like this
select id ,name from user
id name
1 a
2 b
3 c
i need the result set to be
my_reports
1-a
2-b
3-c
is that possible, if so, could anyone tell me how to do?
hello !
for MS SQL 2000
how can I get something like
SELECT name from users WHERE Count(name) > 1
i want to return only the rows where name appears more than 1 time
thank you
It may sound like a newb question and if thats the case, I'm really sorry, but I would like to know if something like this is possible....
Lets say I have a table called TestA on a database DBA on a ServerA
and I have an another table called TestB on a database DBB on a ServerB
Now each of these table has a a field Id, to make an INNER JOIN between these two tables, taht are located on two different server on two differents database...Is it possible? and if so what would be the SELECT instruction to do it? Thanks for taking the time to read this :)
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category".
Sample:
Human Resources
All documents related to new hire process.
New Hire Request forms, New Hire forms, Termination Forms
Total Files: 25
Programs & Utilities
Windows utilities and other tools to keep you safe on the net.
Antivirus, Spyware Removal Tools, Other programs
Total Files: 37
My SQL2005 DB has 2 tables.
1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription)
2) Files. (CategoryID, ParentCAtegoryID, FileTitle)
My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents.
My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only.
Sample:
Human Resources. CategoryID=4, ParentCategoryID=NULL
New Hire Request forms. CategoryID=13, Parent CategoryID=4
New Hire Forms. CategoryID=17, Parent CategoryID=4
Termination Forms. CategoryID=22, Parent CategoryID=4
Programs & Utilities. CategoryID=6, ParentCategoryID=NULL
Antivirus. CategoryID=8, Parent CategoryID=6
SpyWare Removal Tools. CategoryID=26, Parent CategoryID=6
Other Programs. CategoryID=31, Parent CategoryID=6
When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories.
Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else.
I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
Hi! Good Day!
What is the correct SELECT query if i want to count the distinct date from my table.
Example:
Table1
DateField
10/11/2005 5:14:48 PM
10/11/2005 5:24:48 PM
10/10/2005 5:14:30 PM
10/13/2005 5:15:02 PM
My query is something like this.
SELECT COUNT(DISTINCT DateField) FROM Table1
The result should be 3 and not 4
because there are 2 records with the same date, only different time.
Thanks :)
Hey guys,
I am trying to design a query that will return all the records based on a single date but because of the date/time data type in MS SQL 2005 it defaults to the date and the time 00:00:00 which will return no records for me.
Is it possible to ignore the time part in the select statement?
Thanks.
Hi,
I have to create the query in which I have to select complete row records according to the column "time" where the time is most recent entered.
there are columns "username", "bookname", "Time"
a asp 6/4/2008 6:02:00 AM
b sql 6/4/2008 6:04:00 AM
a php 6/4/2008 6:05:00 AM
Navi
I've got a table where the date and time fields are all out of sequence and I need them in sequence when I am extracting data from the table, but I'm having trouble writing the SQL.
I've got this line which sorts the date and time for the table:
SELECT * from tblcodegreydiv order by date desc, time desc
Now I'm trying to retrieve the closest record prior to a date,time selected by a user...
SELECT * from tblCodeGreyDiv
WHERE recid=(SELECT MAX(recid) from tblCodeGreyDiv WHERE date + '' + time <= '2007-09-19 05:24')
I need to somehow sort the table before I run the above sql statement. How would I go about doing this?
Hi NG,i've got a mystic problem i can't solve, perhaps one of you has a good idea...i will explain with some code, so fo better understanding my probroblem:i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.following Stored Procedure is ok and does it's job fine with "EXECUTEIntDayView"CREATE PROCEDURE dbo.IntDayView ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)AND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)but now, i want to have variables for the Date string, so i tried followingsyntax:CREATE PROCEDURE dbo.IntDayView @DayViewf DATETIME,@DayViewl DATETIME ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=@DayViewfAND Interviews.DatumZeit <=@DayViewlAND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)--------EXECUTE IntDayView@DayViewf=convert(datetime,'2004-02-10 00:00:00',120),@DayViewl=convert(datetime,'2004-05-16 23:59:59',120)so i tried the convert in execute, and also in the Procedure, but both times anerror occures Error Nr. 156, or 8114,so something with the convert is wrong but, also if i do it without convert itdoes not work.So how to select by time, if this doesn't work?????Any suggestions??Please help me,thanksJan Schmidt
View 3 Replies View RelatedTrying to get the records where the date = today and the time is only
within 15 mintues
So if the time was 10:20 i only get records 2 and 3
COLUMN STAT_DATE
1.2008-06-10T10:30:00.537
2.2008-06-10T10:15:00.530
3.2008-06-10T10:00:00.760
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
View 1 Replies View RelatedHi expert.. I am doing a project that have function to capture the Day and Night time talking duration of caller.... I make use of MSSQL to store the date time file..Let say I have this date: "2006-10-02 00:02:09".. How di I select "00:02:09" only???? Thanks in advancesuigion
View 2 Replies View RelatedA table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help
Thanks
I have a Select TOP 1 statement which works fine, but i also want to count the total records at the same time. This is my SQL
Code:
SELECT TOP (1) dbo.ClientOffers.ProductID, dbo.ClientOffers.ClientID, dbo.Offers.Offertext, dbo.Offers.Offerlink, dbo.Offers.Image, dbo.Offers.Cost
FROM dbo.ClientOffers INNER JOIN
dbo.Offers ON dbo.ClientOffers.OfferID = dbo.Offers.OfferID
WHERE (dbo.ClientOffers.ProductID = 1284) AND (dbo.ClientOffers.ClientID = 0)
How do i also count the total records?
Hello all,
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
Thanks in advance
I have this SELECT statement.
SELECT [issueID], [name] FROM [MyIssue]
What I wanted to do is in addition to the above statement, I want to add two run time fields like this:
99 [issueID],'All Issues' [name]
So let's say the above select statements generates this list:
Summer 2007 Issue
Winter 2007 Issue
The two addition fields will make the result list like this:
01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues
How do I accomplish this? Any help is much appreciated.