SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]....
I am trying to display all records older then 5 Mondays span..so far I got this and it is working fine but it is excluding the records with Id 2 and 4 (Sundays) I would like to include these records as well.
I need a boost to understand how to get started on solving this SQL Query.
TicketId Seq StatusDateTime
T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000
I need to find the total StatusDateTime for each TicketId
I need to find the average StatusDateTime for all TicketIDs
Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN
, CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit
, pv.TicketNumber
, vstatus.Description AS VisitStatus
, doc.ListName AS Doctor
[Code] ....
I have a table that includes the fields below:
ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME
What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:
ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000
So what I am looking for is taking the above record and turning it into 3 records like below:
ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000
Hello ,
I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....
I am using the query as
delete MYTABLE
where datediff(dd,loaddate,getdate())>5
I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .
Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .
I am getting some different results .
Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .
Is the logic correct or i am missing someting important ?
Thanks and Regards
Admin001
Hi ,
I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .
Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .
Any thoughts how to do it through query and schedule it ???
Thanks
I have a one problem in my sql server. I would like to find out the list of all Monday's dates in the year 2006. Can anybody help me out?.
View 14 Replies View RelatedI am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.
Here is the query I would like to change:
SELECT count(*), clientTime
FROM dbo.V_COMBINED
WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value')
GROUP BY clientTime
It returns records like:
1 |2006-02-16 23:21:05.250
1 |2006-02-16 23:21:05.267
1 |2006-02-16 23:21:06.470
I'd like it to return records like:
5 |2006-02-16 23:21:06 - 23:21:10
3 |2006-02-16 23:21:11 - 23:21:15
4 |2006-02-16 23:21:16 - 23:21:20
Anyone know how I could do this? Is it even possible?
Thanks
I have to compare two time duration for a resource booking system. for example if a resource is booked for 9.00am to 2.00am ,it must not be booked during that duration and other coinciding times e.g 1.00pm to 2.00 pm should not be booked or even 9.00 am to 5.00pm should not be booked.
how can i write a sql query for this.
Hello,
Some advice appreciated on the following, thanks!
I have this query:
select field1, field2, astartdate, anenddate
from atable
where astartdate BETWEEN '9/11/2007 12:00:00 AM' AND '9/18/2007 11:59:59 PM'
I need the results to be like the following:
field1,field2,anenddate-astartdate <<that's minus
Thank you for the help!
--PhB
--PhB
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
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.
Hi,
I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.
See made up example of a report below:
Person Name Address <-Report header
Area: A12 3BC, Newtown, Someplace <-TableGroup header
Mr Some Body 1 The Street <-Detail rows
Mrs Anne Other 2 The Street
Area: B23 4CD, Oldtown, Someplace <-TableGroup header
Mr Ran Dom 3 The Avenue <-Detail rows
So the TableGroup displays Area which is grouped by Post code, city etc.
The detail rows displays individual people and their street address.
NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?
Thanks in advance for your help,
Mike G
I have a big table which has fields(ID, RecordDate, Value). The records were 15 minutes interval, start at yyyy-mm-dd 00:00:00 and end at yyyy-mm-dd 23:45:00 for each day. Some records may be missing.how can I do the query:if the every 15 min records exists in the day, do sum(value) for the day
else do something else.I would like see difference between SQL 2008 and 2012.
Looking for returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
I have the following store procedure which list a certain amount of product
SELECT P.Name, P.ProductCode, P.Quantity, P.ItemUnitWeight, P.Weight, P.WeightUnitMeasureID, P.IsDeleted, UNIT.UnitMeasureCode, P.StoreID, dbo.v_ProductItemWeight.TotalWeight,
dbo.v_ProductItemWeight.UsedWeight, Production.ProductCategory.Name AS CategoryName, Production.ProductCategory.ProductCategoryID, Production.ProductItem.Quantity AS UnitQuantity,
Production.ProductItem.UsedQuantity
FROM Production.Product AS P INNER JOIN
[Code] ....
This store procedure produce the following result set :
Result Set
From the result set above, in fact what I need to get is a single records for each ProductCode and adding 2 fiels which are the Sum(UNitQuantity) and the Sum(UsedQuantity)
How can I add those 2 field in my querry and group it by ProductCode ?
I have 2 tables:
create table #InsuredLoc (
PolGK int,
InsuredLocGK varchar(20),
InsuredLocEffDt datetime,
InsuredLocType varchar(20),
InsuredLocStatus varchar(20),
InsuredLocAddress varchar(50),
PolEdrGk varchar(20),
[Code] ...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why.
There is a huge empty space between my tablix which is inside a rectangle to the footer.
I need to be able to fit another row but the other rows go on to the second page. How can I fix this?
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.
View 9 Replies View Related IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN
CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
2015-04-29 10:22:08.243
2015-04-29 11:01:49.150
2015-04-29 11:08:16.617
2015-04-29 11:40:34.407
[code]...
I have a stored procedure that attempts to INSERT @BatchSize number of records at a time into a table. Currently, I have @BatchSize set to load 50,000 at a time. The table I am inserting from has a little over 67,000 records.
When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.
Below is my code:
begin try
--error catching variables:
declare @Error_NumberLocal int
,@Error_MessageLocal varchar(4000)
,@Error_SeverityLocal int
,@Error_StateLocal int
,@Error_ProcedureLocal varchar(200)
,@Error_LineLocal int
,@User_NameLocal varchar(200)
[code]....
What is the problem with the looping structure that would cause this issue?
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5
2 1 01/01/2011 Accept 5
3 2 02/01/2010 Accept 10
4 2 02/01/2010 Deny 20
5 3 02/01/2012 Accept 15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
5 3 02/01/2012 Accept 15 (show Accept row=5)
6 4 05/05/2012 Accept 25 (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7 4 07/08/2012 Accept 20
11 6 01/01/2011 Accept 5 (show Accept rows=11,12 because Qty is different)
12 6 01/01/2011 Accept 15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5),
(3,2,'02/01/2010','Accept',10),
(4,2,'02/01/2010','Deny',20),
(5,3,'02/01/2012','Accept',15),
(6,4,'05/05/2012','Accept',25),
(7,4,'07/08/2012','Accept',20),
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),
(12,6,'01/01/2011','Accept',15)
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 10
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER,
* FROM summary
) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is
R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button
Since records R21, R22, R23, R24, R25, R26 are inserted
page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location
1 4/1/2015 bld1
2 4/2/2015 bld1
3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])
And the results would be:
4/3/2015 bld1
I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
My table is test and I have an ID and DateTest columns
I would like to count the weeks with more then one record.
So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record
select sum(c)
from (
select c = count( id) over (partition by id, datepart(week, DateTest))
from test where id = '1' and DateTest >= '7-7-2015'
) a where c = 1
I have a table that have student names and their birth dates just like below
a) tblStudentInfo
which shows records like below (Birth Dates are shown in Year-Month-Day format)
ID StudentName BirthDate
1 ABC 2002-12-25
2 DEF 2002-09-10
3 GHI 2002-09-19
4 JKL 2002-06-10
[code]...
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate
1 ABC 2002-12-25
2 VWX 2002-01-01
3 STU 2002-02-03
4 PQR 2002-03-05
[code]...
I am working on one logic. I have a table as below.
Declare @Table table (ID int, StartDate datetime, EndDate datetime)
Insert Into @Table (ID, StartDate, EndDate)
Values (1, '2013-01-01', '2013-12-31')
, (1, '2014-01-01', '2014-06-30')
, (2, '2014-01-01', '2014-07-31')
, (2, '2014-08-02', '2014-08-30')
select * from @Table
I need as below.
ID, StartDate, EndDate
1, '2013-01-01', '2014-06-30'
2, '2014-01-01', '2014-07-31'
2, '2014-08-02', '2014-08-30'
Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?
How do I align output from two columns into a single column? Below are the DDL/DML as required with sample data.
CREATE TABLE #Users(
UserID VARCHAR(7),
G1 VARCHAR(30),
G2 VARCHAR(30),
G3 VARCHAR(30),
[code]....
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1
,b.table2
from table1 a, table2 b
where StringDistance (''a.table1,'b.table2') >90