I need to query data from a table which has order_date which is datetime field. How to pull orders by hour by day per month for the last 4 months. Any idea how to write the query?
Sample data:
order_dt order_no
2004-02-04 18:18:19.800 100
2004-02-04 18:18:22.677 101
2004-02-05 06:30:23.300 102
2004-02-05 06:31:39.533 103
2004-02-05 06:32:45.377 104
I want to round times down to the previous hour,
For example a call time of 7/31/07 11:51:13.532 would become 7/31/07 11:00:00.000.
if now the system time is 2014-12-23 23:45:345 . then I want to calculate the sales amount with total and get current date and current hour filter by hh:00-hh:59 , my desire output is like below :
Current time:23:50 run this query
Date Time amount
2014-12-23 23:40 $10
2014-12-23 23:01 $5
2014-12-23 23:39 $5
Total :$20
Time Interval for hour and 30 minutes
--------------------------------------------------------------------------------
Hi Everyone
I Have column in sql server databas as "HHMMSS" and data as and i am doing a substring to get values for hours and minutes. since my calculations based on hour interval and 30 minutes interval
for ex: Now i want to show all the transaction done b/w 6 to 7 am or pn.
and for 30 minutes interval i have get the calculation as transactions done b/w 6:00 to 6:30 and 6:30 to 7:00 either it's am or pm. now how i can write my sql statements that calculates hour and 30 minutes intervals
HHMM
-------------
06:43
09:26
09:26
11:58
12:25
18:17
20:45
00:43
00:53
16:47
Thanks
Phani
All ,
I need to convert the given time into 24 hour clock .
I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.
Please help ..
Regards,
Ashish
i have a query i need to calulate the HH of a datetime field
example
Dateval Need to get
10/01/2007 7:48
10/01/2007 7:00
10/01/2007 7:49
10/02/2007 7:00
10/01/2007 7:50
10/03/2007 7:00
10/01/2007 7:51
10/04/2007 7:00
10/01/2007 7:52
10/05/2007 7:00
10/01/2007 7:53
10/06/2007 7:00
10/01/2007 7:54
10/07/2007 7:00
10/01/2007 7:55
10/08/2007 7:00
field prodDate puts out values as such:
8/12/2013 7:50:15
8/12/2013 7:51:03
8/12/2013 7:53:42
8/12/2013 7:54:12
8/12/2013 7:56:02
...
...
8/12/2013 14:57:57
8/12/2013 14:59:59
I'd like to get a time span sum beginning with the first prodDate entry and ending with the last.
The idea is I'll then use that to divide lbs produced, thus giving me a gauge for lb per hour.
I have data that looks like the following.
ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
for 14 intervals up until 16:00
How can I do this? I have a column that is a varchar and times are stored like this:
0600
1240
0145
2335
How can I get those to HH:MM formats? I've tried this but I don't know if it's the best way, plus there are seven 0s after the MM:
convert(time,LEFT(b.status_time,2) +':'+ RIGHT(b.status_time,2))
I would like to make a function to convert a datetime to half-hour. E.g. If the timestamp is 1:23:05 then converts to 1:30:00, if 1:35:27 then converts to 2:00:00.
Anyone has any idea? Thanks.
SSRS 2008 R2. I am writing a report that uses a DB4 database. Because of this many SQL commands that I normally use are not available and I don't know what commands I can use. Because of this I am doing much of my calculations inside of SSRS.
I need to round a time field to the nearest quarter hour inside of SSRS.
For example:
8:12 becomes 8:15
2:30 becomes 2:30
1:57 becomes 2:00
4:07 becomes 4:00
Probably this will be a silly question?
1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.
2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.
3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.
I had sent many emails to SWYNK. No reply so far.
Any inputs r well appreciated!!!!!!
-MAK
i need to be able to do an sql call whereby i can check the day and hour for publishing.eg select story where day = ?? AND hour = ?? any one know the best way to do this.
i need to call out date as a day number or someting and the getdate as just the hour??? anyone know how??
I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as  the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.Â
Is there a way to DROP several SP at one time with some sort of wildcard? Something like "DROP PROCEDURE sp_%"
View 13 Replies View RelatedI have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
Thanks,
Eron
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?
View 3 Replies View RelatedHi,
How to hide my database with the users, I am deploying my application, i want to hide my database to client. Please help me regarding this.
Rajesh.
Our company is looking for fulltime Java/ASP/SQL gurus immediately.Big pay.If interested please send resume to polasp@hotmail.com.
Thanks!
I have 2 tables :
tab1 col1 char(10), col2 char(10), col3 char(10)....Primary key on col1+col2
tab2 col4 char(10),col5 char(10),col6 char(10)......Primary key on col4+col5
I want to insert a row from tab2 if its not there in tab1.
And I want to update col3 of tab1 from col6 of tab2 if col1+col2 matches col4+col5.
Any thoughts on doing these 2 tasks. I am sure these will be 2 differnet commads.
SQL Gurus please help....
jfk
Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj
View 6 Replies View RelatedHi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..please adviceThanx in advance ashish
View 2 Replies View RelatedWe need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ??
Thanks!
SQL Server Load Balancing -- Any ideas?
There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.
Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?
Will this work? If not, why not?
What will work? Will federated servers work for an entire database??
I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.
TableA
and
TableB
----------
ID int
ComBank int
inboth int
EqorMA int
TranType int
I need to know the rows that Are different in TableA from TableB.
Any help would be appreciated.
Thanks
James
Hi there!
I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :
Create table tbl_blob (
doc_id varchar(20) not null,
doc_blob image null )
My question is, do you have any script on how I can insert a MSWord/Bitmap file in my blob table. I will be running the script using Query Analyser.
thanks!
100NA
i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.
In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.
1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?
2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?
BELOW IS MY STORED PROCEDURE:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
--SET ANSI_WARNINGS OFF
INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)
VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)
--SET ANSI_WARNINGS ON
END
RETURN
AND HERE IS THE CURSOR:
Code:
DECLARE RelativePositionCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT photoId, photo_date
FROM PersonalPhotos
ORDER BY photo_date ASC
OPEN RelativePositionCursor
DECLARE @curs_id INT
DECLARE @curs_date DATETIME
DECLARE @totalRows INT
DECLARE @firstRow INT
SET @firstRow = 0
DECLARE @Results TABLE
(
curs_id INT NOT NULL PRIMARY KEY,
curs_date DATETIME,
RelativePosition INT
)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @totalRows = (SELECT COUNT(*) FROM PersonalPhotos)
WHILE(@firstRow <= @totalRows)
BEGIN
SET @firstRow = @firstRow + 1
BREAK
END
INSERT @Results
VALUES (@curs_id, @curs_date, @firstRow)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
END
CLOSE RelativePositionCursor
DEALLOCATE RelativePositionCursor
SELECT *
FROM @Results
ORDER BY curs_date
The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat
View 14 Replies View RelatedI am not good at SQL - hence here goesConsider this scenario of 2 tables X and Y with a many to manyrelationshipTable X (name,weightage)X1 2X2 1X3 5X4 1Table Y (name ,attrib)Y1 attrib1Y2 attrib2Y3 attrib3Y4 attrib4Relationship table Z (id,id)X1 Y1X1 Y2X2 Y1X3 Y1X3 Y2X3 Y4X4 Y4So based on Z the distribution (count of Y/ Total Y ) is likeY1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7Now I want to factor in the weighatge column in X alsoSoX1 Y1 2*1X1 Y2 2*1X2 Y1 1*1X3 Y1 5*1X3 Y2 5*1X3 Y4 5*1X4 Y4 1*1Sum: 21Given these tables ,How would I factor in the weights and get thedistribution in a single SQL query.Desired Output of SQL QueryId AttribDistrib--------------------------------Y1 attrib1 100*8/21Y2 attrib2 100*7/21Y3 attrib3 0Y4 attrib4 100*6/21ThxJP
View 19 Replies View RelatedGreetings All,
I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:
95752,95725,0001,0, ,N
11473,8704,0001,0, ,N
1279215,436612,0001,0, ,N
43595,43592,0038,0, ,N
and I need to get to here:
95752 ,95725 ,1 ,0 ," ","N ",0
11473 ,8704 ,1 ,0 ," ","N ",0
1279215 ,436612 ,1 ,0 ," ","N ",0
43595 ,43592 ,38 ,0 ," ","N ",0
Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.
Any feedback in regards to getting pointed in the right direction would be greatly appreciated...
I have a problem.. My code below works fine but when I want to add user to the database, it gives me error.
I added this part
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
and it gives error here
MyCmd.ExecuteNonQuery()
Thanks Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As StringDim sConnString, sSQL As String
Dim u As MembershipUser 'Receiving values from Form, assign the values entered to variablesname = Request.Form("name")address = Request.Form("address")
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")'declare SQL statement that will query the database
sSQL = "INSERT into test (name, address, user) values ('" & _
name & "', '" & address & "', '" & nice & "')"
'define the connection string, specify database'driver and the location of database
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString() 'create an ADO connection object Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()End Using
Response.Write("The form information was inserted successfully.") End Sub
End Class
Are many days that I've a problem with a matrix.... I hope that somebody can help me :-(( CREATE TABLE [dbo].[Matrice]( [ID] [int] IDENTITY(1,1) NOT NULL, [IDProfilo] [int] NULL, [IDAttivita] [int] NULL, [CodiceCompetenza] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [ObiettivoAttivita] [int] NULL, [PesoCompetenza] [int] NULL, CONSTRAINT [PK_Matrice] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]in this table I've a matrix with ... for each profile ... IDAttivita e CodiceCompetenzain the row I've a list of activites (3) and for each activity I've an "ObiettivoAttivia"in the columns I've all "CodiceCompetenza"In excel I have a table like thisAttività - Obiettivo attività - SM - CO - CM - CTatt 1 - 4 - ...att 2 - 3 - ...att 3 - 2 - ..."Obiettivo attività " is the same for all SM - CO - CM - CT of the same "Attività "In my SQL table I've[IDAttivita] row[CodiceCompetenza] column[ObiettivoAttivita] one for each activity[PesoCompetenza] row/column valuethis is an example:in my external table I'veProfile116 IDProfiloActivities1 att 12 att 23 att 3CompetenzeSMCOCMCTINSERT INTO Matrice VALUES (116,1,'SM',4,1)INSERT INTO Matrice VALUES (116,1,'CO',4,2)INSERT INTO Matrice VALUES (116,1,'CM',4,3)INSERT INTO Matrice VALUES (116,1,'CT',4,1)INSERT INTO Matrice VALUES (116,2,'SM',3,1)INSERT INTO Matrice VALUES (116,2,'CO',3,1)INSERT INTO Matrice VALUES (116,2,'CM',3,2)INSERT INTO Matrice VALUES (116,2,'CT',3,2)INSERT INTO Matrice VALUES (116,3,'SM',2,1)INSERT INTO Matrice VALUES (116,3,'CO',2,3)INSERT INTO Matrice VALUES (116,3,'CM',2,4)INSERT INTO Matrice VALUES (116,3,'CT',2,1)with these inserts I see that:attività 1 has "obiettivo attivita" 4attività 2 has "obiettivo attivita" 3attività 3 has "obiettivo attivita" 2my problem:I calculate the "valore peso" ... the product (obiettivo attivita * PesoCompetenza) for each rowNow I've to to sum all "valore peso" of the same "CodiceCompetenza"I will havefor SM4*1=13*1=32*1=2Total SM 9for CO4*2=83*1=32*3=6Total CO 17...Total CM 26Total CT 12now I've to se a variable @range_massimo = 4and calculate the "Max" variable as a sum of all "obiettivi attività " * @range_massimo(I've to consider only the 3 different activities for the "obiettivo attivita" and not all 12 activities of the matrix)"Max" will be:(4*4)+(3*4)+(2*4) = 36 -> "Max"The final step is Total / Max(for each Total of my Competenza)Total SMTotal COTotal CMTotal CT...(9/36)*100 = 25(17/36)*100 = 47,2(26/36)*100 = 72,2(12/36)*100 = 33,3now * @range_massimo /100(25*4)/100= 1,0(47,2*4)/100=1,9(72,2*4)/100=2,9(33,3*4)/100=1,3I hope you can help me!!Please note that I will have dinamic IDAttivita and CodiceCompetenza for each IDProfilo!Thanks
View 4 Replies View Related