SQL Server 2008 :: Putting Rows Together To Get Total Times

Jan 30, 2015

I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:

Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM

Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2

Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.

View 8 Replies


ADVERTISEMENT

SQL Server 2008 :: Pulling Daily Total From Cumulative Total

Jun 28, 2015

I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.

CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')

View 9 Replies View Related

SQL Server 2008 :: Same Database With Two Different Machines At Different Times

Jun 19, 2015

For security reasons customer wants to put a SQL database on an encrypted thumb drive (IronKey). Here's the rub though. He wants to be able to work with the data on a workstation. Then, if he takes his laptop out of the office he wants to be able to simply plug that thumb drive into the laptop and fire up SQL on the laptop and use that same database. Procedurally this would work in that the database can be created so that the location is the same from both machine viewpoints, however will the two different SQL instances allow moving the database back and forth like this?

View 9 Replies View Related

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

Mar 20, 2015

If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End

I want to iterate this query using a loop as many as 5 times max.

View 3 Replies View Related

SQL Server 2008 :: Left Joins And Query Plan Compile Times

Mar 8, 2015

We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.

So rather then the following exemplary fragment

select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id

Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.

I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.

View 9 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

SQL Server 2008 :: Group By Total Sum

Oct 31, 2015

Despite the products are different, I want to get the sum of all products according to customer card.(client ref)

select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX

View 1 Replies View Related

SQL Server 2008 :: Tell Number Of Times People Use SSMS To Change Client Data?

Mar 21, 2015

I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?

View 6 Replies View Related

Transact SQL :: Running Total In Server 2008

Nov 20, 2015

Is it possible to assign to a variable, then add to it later on?  When I run the below, all I get is 3 rows affected I never see the value printed.  What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers
= 6
Create Table #Test ( randarnumbers int )
Insert Into #Test Values ('2'), ('1'), ('3')
Declare @sumofallnumbers int, @nbr int
Declare c1 Cursor For
Select randarnumbers
FROM #Test

[code]..

View 6 Replies View Related

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

Jun 20, 2013

Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?

I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.

Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.

Server Configuration:

SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM

SQL Code:

Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;

[code]....

View 9 Replies View Related

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 Replies View Related

SQL Server 2008 :: Divide Total Rate By Number Of Records

Jun 22, 2015

my table includes detailed records with total Rate repeated in each record:

CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,

[code]....

View 1 Replies View Related

SQL Server 2008 :: Log File Space Is 5 Times The Data File

Mar 16, 2015

one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.

View 9 Replies View Related

Deleting Large Number Of Rows Times Out

Sep 14, 2007

I have a table with about 10 million rows, its been logging data incorrectly and I want to start again.

DELETE FROM tblLog

I just get a message about the server timing out, what can I do?

Thanks

View 5 Replies View Related

SQL Server 2008 :: Concatenating Many Rows Into One

Jul 14, 2015

I have a table called Signatures

It contains the fields

Parentobject and [View As]

there can be more than one Parentobject so I want to concatenate them so I have them on one line

for example

901 Joe Dow
901 Jane Dow

I want one line - | 901 | Joe Dow, Jane Dow

I found something similar as below but I'm getting dups like

901 |Joe Dow , Joe Dow
901 | Jane Dow, Jane Dow

DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
SELECT DISTINCT
ParentObject,
(SELECT STUFF(
(SELECT @Delimiter + s1.[View As]
FROM Signatures s2

[Code] .....

View 3 Replies View Related

Transact SQL :: How To Generate Rows Certain Times With A Counter Column

Jul 10, 2015

How can I write it in SQL? I have a table  that column  A   has two values of   Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4  more rows, and add column b with values from 1 to 5 for each rows ( counter).

View 3 Replies View Related

SQL Server 2008 :: Concatenate Rows With For XML Logic

Feb 24, 2015

I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?

This question is just for my understanding

CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))

INSERT #tbl
SELECT 'This'
UNION ALL

[Code] .....

View 4 Replies View Related

SQL Server 2008 :: INSERT INTO Not Inserting Enough Rows

May 22, 2015

I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).

The code for creating the table is here:-
USE [CDSExtractInpatients6.2]
GO
/****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/
SET ANSI_NULLS ON
GO

[code]...

I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.

View 9 Replies View Related

SQL Server 2008 :: In Query - All Rows With Same Status

Oct 28, 2015

I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?

View 6 Replies View Related

SQL Server 2008 :: Transpose Columns To Rows?

Oct 29, 2015

I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.

I have data in the form of:

Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,

The output should look like:

Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black

View 2 Replies View Related

Integration Services :: Reading Recordset Multiple Times Gives 0 Rows

Oct 26, 2013

I have an EXECUTE SQL Task, which gets a result-set from SQLServer using OLEDB Connection.

The result set is mapped to an object variable , say @[User::FilePath]

There are 33 row is the above resultset.

Then, I have a For-each loop, inside which, I have a Script task .

I am trying to put the above @[User::FilePath] recordset into a DataTable using DataAdapter.Fill() function. I perform some read operations to its rows.

The problem is , in the First Iteration of For-Each-Loop, the number of rows in data-table shows 33.

But from the Next Iteration, it comes out to be 0. (ZERO!!)

This causes my package to fail.

View 5 Replies View Related

SQL Server 2008 :: Select Alternate Number Of Rows?

Jan 28, 2015

A simple Query to select alternate rows from a table ?

View 9 Replies View Related

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

Feb 19, 2015

I need the requirements of merging multiple rows of same ID as single row.

My Table Data:

IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL

Required Output Should be,

IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH

How to achieve this output. Tried grouping but its not working also producing the same result.

View 1 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2008 :: Combine Multiple Rows To Single Row?

May 24, 2015

How to combine multiple rows to single rows for the below sql query.

SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID

The reult for the above query is:

RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1

I need the result like the below

RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46

View 2 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

SQL Server 2008 :: Delete Duplicate Rows From ANY Table

Jul 31, 2015

Is there is 'fairly' simple query to delete duplicate rows from ANY table ?

A script which removes duplicates from a table, when a table_name is supplied.

View 9 Replies View Related

SQL Server 2008 :: Update The Document Number Row For 3k Rows?

Aug 4, 2015

I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:

DOCNUM: 4445787 Version 1
DOCNUM: 4445790 Version 2

It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).

The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to

1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.

2. Update them so that the docnums match.

View 7 Replies View Related

SQL Server 2008 :: BULK INSERT Inserting No Rows

Aug 7, 2015

I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.

Here is the code for the operation (only the parameters for the insert itself):

set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''
''
, codepage = ''1252''
, keepnulls);'

The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.

The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.

View 4 Replies View Related

SQL Server 2008 :: Best Way To Optimize View That Contains Millions Of Rows?

Aug 26, 2015

I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.

CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,

[code]....

View 3 Replies View Related

SQL Server 2008 :: Check For Missing Rows In A Table

Oct 20, 2015

I have a table of languages, identified by a lang_id column as follows:

LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech

I also have a RESOURCES table of phrases (for translation purposes) similar to this:

res_id res_lang res_phrase
AccessDenied en Access Denied

For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.

For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.

View 6 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.

E.G.

A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:

'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'

What would be the best way to do this ?

View 3 Replies View Related







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