How To Create Total Time, Count It

Aug 29, 2005

i hv a problem in sql,

i) if in data hv time_start, time_end, date , id, how i can calculate the total time for very sesion user login

ii) how to make it if the total come out hv same id(may be two id hv two diffrent total), i want make it just one id then the total time...

post me at jeffliew2001@yahoo.com

View 6 Replies


ADVERTISEMENT

Select TOP 1 And Count Total Records At Same Time

Jun 7, 2012

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?

View 7 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 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

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Total No Of Row Count

Sep 25, 2007

Hi,



I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.

I want to do the following -



Sum the total no. of rows in mdb while the package executes

Sum the total no of rows transferred to Sql server.

Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)


Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.


I saw a RowCount transformation..but just dont know where to place and what to do with it.



thanks

View 2 Replies View Related

Get Records With A Count Total

Feb 14, 2008

Hello, I am having problems with this query below:
 1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
 

View 1 Replies View Related

Getting Total Count From Query

Sep 21, 2013

I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is

Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date))
which gives me a result like
inv_cust ' Salescount

[code]...

How can I modify my existing query to get this.

View 2 Replies View Related

Getting Total Row Count For Complex Query Using CTE

Apr 26, 2007

I have a query that uses a CTE which looks similar to this

WITH cte_Products AS
(SELECT SELECT
ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS RowNum,
RANK() OVER (ORDER BY id DESC) AS rank,
FROM Products
WHERE SomeColumn = @SomeVariable)
SELECT rownum,
columns,
(SELECT COUNT(*) FROM cte_Product) AS TotalRowCount
FROM cte_Product AS products
WHERE Rank <= @LastXItems
AND RowNum BETWEEN (@StartRowIndex + 1) and (@StartRowIndex + @MaximumRows)
ORDER BY RowNum

The (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount is in there because I need the total row count that is queried by the CTE. But I feel like this is an inefficient way of doing this. I would just split out the query for the total row count, but then I have to do another sub query to get the rank again since rank is calculated using the Rank method. Does anyone have any ideas of how best to do this?

View 3 Replies View Related

Count The Total Number Of Results Returned

May 17, 2007

Iam using front page to dispalay my results.

At the bottom it shows me 1/10 i.e 1st page of 10 pages.

but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).

or it would be really good if i get count of both no. of recors as well as no. of pages.

View 2 Replies View Related

Trying To Return Total Record Count With Query

Feb 26, 2007

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>


Thanks for any help!
Dale

View 10 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

How To Get Total Page Count For Individual Groups

Aug 3, 2007

Hello,



I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.



For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,



For 1st group

'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc



Similarly for other groups as wll.



Thanks!

View 3 Replies View Related

Transact SQL :: Total Count Of Table Rows

Sep 15, 2015

I have a question regarding the total count of the table rows

Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".

So now I have to store the count in 'nn'.

View 5 Replies View Related

Get Total Count And Top N Rows In Single Call

Dec 3, 2007



Hi,

I want to return the total number of rows and top n records from my query. Is it possible to do this by simply running a single query. My query is dynamic so I can not create any temp table or table variables.

Bacially I want to eliminate 2 calls for sp_executesql

Set @ColumnCount = "Count(*)"
Set @Columns = " Top 100 a,b,c"
Set @Filter = "a = 1001"

Set @sql = "select " + @ColumnCount + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Set @sql = "select " + @Columns + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Thanks
Pun

View 3 Replies View Related

Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

T-SQL (SS2K8) :: How To Group Total Count Of Similar Items

Mar 30, 2015

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.

So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
--------------------------
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

View 6 Replies View Related

SQL 2012 :: SSRS Multi-value Parameters - Total / Sum / Count

Sep 11, 2015

I have a multi-value parameter that I am having a hard time writing a COUNT expression for in SSRS. Here is the situation:

1. If the "(Select All)" in the drop down is selected, COUNT all last names for ALL of the Auditor parameter
2. If a specific or multiple auditors are selected from the drop down, COUNT all last names based on that selection for the Auditor parameter

Currently, I am having it COUNT by ALL and it works but if a specific or multiple auditors are chosen, then the COUNT doesn't work.

View 3 Replies View Related

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

Err On Grand Total In Cube Measure Using A Distinct Count Agregation

May 28, 2007

Hi,

I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube

can some body help me !!

View 1 Replies View Related

Run-Time Running Total *(SOLVED)

May 14, 2008

I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.

I want to avoid a Loop (i.e. cursor or a CTE).

Please help!



Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0




UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

FROM@Stage AS s1

select * from @Stage

My desired Results are


2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300


But I am geting

2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928


Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.

Please help!

View 15 Replies View Related

T-SQL (SS2K8) :: Calculating Total Outage Time

Jun 17, 2014

I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.

Ex:

3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.

In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Total Execution Time?

Oct 30, 2014

I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.

i planned to test it as follows

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

----- for the SP that is before optimize

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

View 9 Replies View Related

Summing Time Span (for Total Per Hour)

Sep 12, 2013

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.

View 3 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Query To Show Total By Shop On Time Range

Aug 28, 2014

How to edit this query to show the total by shop on time range ?

current result.(Time range from 9:00am-23:00pm)
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
BBB 11:59 $100 $50 +150 (+)
BBB 12:59 $100 $50 +50 (+)
------------------------------------------------------------------

Desired Result .
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
Total $300 $150 +$200 (+)

BBB 11:59 $10 $50 -40 (-)
BBB 12:59 $10 $50 -40 (-)
Total $20 $100 -80 (-)
-----------------------------------------------------------

select shop
,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span
,sum(case
when datediff(dd,yourdatetimefield,getdate())=0

[Code] .....

View 1 Replies View Related

Transact SQL :: Calculate Total Working Time Against Attendance ID?

May 16, 2015

I have a table like below

i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)

ex : 

AttID TotalTime
1 08:02:00
2 07:45:00

View 7 Replies View Related

Problem With Printing Execution Time And Total Pages

Mar 28, 2008

I have a strange problem. All my report footer has 3 text boxes with the following formulea in them.

="Printed by " + User!UserID + " on " + DateTime.Now.ToString()


="Execution Time: " +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",

(

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))

)


="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

When I run the report on the report manager, (through the site), the execution time, number of pages everything show properly. But when I print the report, the execution time and total pages is printing huge numbers, which are not in any way related the actual page numbers.

For example:
When I run the report it shows the footer like below:
Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM Execution Time: 6 Seconds Page 1 of 213
When I print
Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM Execution Time: 47 Seconds Page 1 of 483

Does anyone know what is the solution to this problem is?
I really appreciate your help,

Thank you,


View 3 Replies View Related

Power Pivot :: Sum For Events And Time - Calculate Total Ratio

May 22, 2015

I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).

In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.

For instance
                            201501 201502 201503 TOTAL
Number of events           8         10        10     28
Time                             5           5         
4     14
Ratio                            8/5       10/5     10/4  28/14

View 3 Replies View Related

Create Stored Procedure To Get Total Last 7 Day Sales For Each Day

Mar 13, 2015

Table name: ONIV
Columns:
Date: DocDate
Sales: DocTotal

I want to populate a line graph which would show the 7 days of the week on X-Axis and the sale on the Y-Axis.

View 2 Replies View Related







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