Transact SQL :: Hours To Show In A Column?

Jun 19, 2015

i'm trying to capture data for every hour and would like to display the hours of the day in a column.

select
case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM],
case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM]
from table1
where cast(calldate as date) = cast(calldate as date)

My desired result should display

12-7 am
8 am
9 am
10 am

View 4 Replies


ADVERTISEMENT

Transact SQL :: To Display Days Hours Mins Format Based On Business Hours

Apr 22, 2015

I want to display Days Hours Mins Format.

I am Having two columns Like below,

Col1 (in days)    col2 (In Hours : Mins)
3days  4:5 

In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5

From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.

View 6 Replies View Related

Transact SQL :: How To Show Sum Of Columns To New Column In Server

Jul 8, 2015

I made a select query which shows following output as shown in picture . 

Now I want to add one more column in this query to show current bags  and Bags in these 2 columns i want to show calculation like    in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and  697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image

There is one more column common date according to which i have to make calculation like

rid     commondate   recdate   recbags        recweight             loss gain   delbags    delwght

101215109    01/01/2015   07/01/2015       1400                  697.5                0                  0            0             0
101215110   02/01/2015      08/01/2015         560                    279.64             0              0         0               0

View 7 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

Transact SQL :: Table Row Data Show In Column Data

Oct 22, 2015

I have a simple table data i want want to show row data in to column data.
      
SELECT clblcode,mlblmsg
FROM warninglabels

My expected result will be 

0001                 0002  0003                      0004
-------                ------- --------                    --------
May Cause....       Important.....  Take madi...........         Do Not Take.......

View 16 Replies View Related

Transact SQL :: Convert 1/2 Into Half Day Or 4 Hours Format

Sep 21, 2015

I'm developing one leave application form in that I've declared "No of days"column as nvarchar type..If the user taking half(1/2) day leave then have to covert that 1/2 into 4  hours itseems..since I'm trying to display the table data on datagridview while displaying that half day in gridview again it has to conver 4 hours into 1.2 format..

My Table design:
 Create table Leave_Form
(
  Employee_id int primary key,
  Emp_Date date,
  Emp_name nvarchar(50),

[Code] ....

Windows form:

View 8 Replies View Related

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

Transact SQL :: Date Difference In The Form Of Days And Hours?

May 21, 2015

declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'

i want output like this 2.18 means 2day 18hours difference how using query

View 6 Replies View Related

Transact SQL :: How To Convert Hours To Days And Months Correctly

Oct 13, 2015

I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.

My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.

This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30 

View 13 Replies View Related

Transact SQL :: Agent Jobs - Running More Than 3 Hours Based On Job Name?

Sep 24, 2015

I need a sql script for Jobs is running more-than 3 hours in a current server, based on a particular jobs.

View 2 Replies View Related

Transact SQL :: Making Hours Into Months And Days In 2008 R2?

Aug 5, 2015

I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.

View 2 Replies View Related

Transact SQL :: Convert Time In Hours And Minutes To Decimal

Jul 30, 2008

I am trying to convert hours and minutes to decimal and arrive at a sum of time taken. The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

SELECT DATENAME(weekday, Started) AS Day,        C.Category,     ClientCode,Description,    dbo.FormatDateTime(Started, 'HH:MMS 12') as Started,    dbo.FormatDateTime(Ended, 'HH:MMS 12') as Ended, CONVERT(varchar,TimeTaken,108) AS TotalTimeSpentFROM dbo.Journal JLEFT OUTER JOIN dbo.Categories C ON J.CategoryID = C.CategoryIDWHERE--DATENAME(weekday, Started) =@Weekday  AND Started >= @StartDate ORDER BY Day, Category, Started

View 6 Replies View Related

Transact SQL :: Two Row Serial Number Date Compare And Take More Than 5 Hours Between Two Rows

Jul 1, 2015

A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.

tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate

TLM3004242015

[Code] .....

I want to take the result like this 

Tld_tripno
Finish Loading
Gate Out
Date and Time difference

TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00

View 10 Replies View Related

Transact SQL :: Window Function To Count Number Of Rows In The Previous 24 Hours?

Nov 16, 2015

is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.

I have a table of events like:

User, TimeStamp, etc...

I want to identify the row which is the event number 50 in the past 24 hours.

does the window functions can do this? and how?

the ROW PRECEDING etc... appear to not have enough feature to support time related function.

Stream-insight is able to create this type of query, but I have to do it directly in SQL.

View 6 Replies View Related

8 Hours To Add A Bit Column

Jul 1, 2004

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0


On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?

View 13 Replies View Related

Adding A BIT Column - 100+ Hours!

Jul 7, 2004

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?

View 9 Replies View Related

Flag Last Two Hours Of Day In A New Column

Feb 4, 2014

i have an hours and a seperate minutes column

e.g.

Hour
10
Minute
15

I now need to flag the last two hours of the day in a new column both datatypes are INT

View 6 Replies View Related

Transact SQL :: How To Show Value In Only One Group

Nov 3, 2015

I have a table with "Project", "Demo" and "Value". Here is a sample. Is there a way to show the "Value" in just one Demo after a I use group by?

Project  Demo Value

Project1 Adult 100
Project1 Kids 100
Project1 Adult 100
Project2 Adult 200
Project2 Adult 200
Project2 Kids 200
Project2 Kids 200

That is after I ran the SQL: I will get only: (see Project1's Kids's value is null now.

Project  Demo Value

Project1 Adult 100
Project1 Kids
Project2 Adult 200
Project2 Kids 200

View 12 Replies View Related

Transact SQL :: Show Total For Each Name

Sep 23, 2015

I tried my syntax below, but it said that the field were not in the group by.  This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:

Total, 29, 150, 2400/60
Total, 25, 143, 2400/60

Here is syntax:

Create Table #Farquard
(
empID varchar(50),
fullname varchar(500),
break1 int,
break2 int,
dailytotal int,
workday date
)

[Code] ......

View 8 Replies View Related

Select Rows With Datetime Column Ealier Than 2 Hours Ago?

Apr 5, 2007

Hello.


I have a table with a datetime column.




I need to select rows with a NULL or a datetime earlier than 2 hours
ago if getdate() time is currently between 6am and 7pm.




Thanks for any help or information!

View 1 Replies View Related

Transact SQL :: Show Particular Records Within Same Group?

Aug 13, 2015

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)

View 4 Replies View Related

Transact SQL :: Query To Show Totals

Aug 28, 2015

This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008

Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)

[code]....

Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to.  How does this need to be written so the data is displayed in the proper formatting?

Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc

[code]....

View 4 Replies View Related

Breaking Down Total Hours Worked Into Day And Evening Hours

Sep 21, 2006

I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'

View 3 Replies View Related

Transact SQL :: Show Customized Error To Clients?

Jun 16, 2015

I don't want show the DBMS error to my clients. I want show a simple error instead of SQL error.

View 3 Replies View Related

Converting Decimal Hours To Hours And Minutes

May 13, 2008

I have a float variable that holds a decimal number of hours.

So 1.5 equals 1 hour 30 minutes.

I need to change this to the format 1:30

Any idea how to do this?

View 10 Replies View Related

Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?

Apr 21, 2015

SELECT 
    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

o/p
indate   04/18/2015
time part :17:45:00

I need to convert this 17:45:00 to 12 hours date format...

View 8 Replies View Related

Transact SQL :: Query To Show Most Recent Value Based On Date Field

Jun 18, 2015

I simply need to list the most recent value for each employee here... the value at each person's maximum date

Sample Detail Data:

EmpID                   Date                      Value
1                              1/1/14                  27
1                              2/12/15                333
2                              5/5/15                   255
3                              5/4/15                   110  
3                              1/1/13                   67                          
3                              3/2/14                   80

[Code] ....

What is the most efficient way to display the most recent value for each employee ID via MS SQL.?

View 4 Replies View Related

Need Show 2 Results From Same Column In Same Row

Oct 29, 2007

I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.

SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'

View 5 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

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 Related

How To Show Variance In Column Values

Feb 19, 2014

This is my table structure

--=====
create table calculate(
ID int identity (1,1),
PreviousYear_Profit float not null,
)

insert into calculate values (12500)
insert into calculate values (22700)
insert into calculate values (18500)
insert into calculate values (25800)
--======

I want to calculate variance and expected output is

ID PreviousYear PriorVersion Variance
1 12500 sum(PreviousYear) PreviousYear-PriorVersion
2 22700 sum(PreviousYear) PreviousYear-PriorVersion
3 18500 sum(PreviousYear) PreviousYear-PriorVersion
4 25800 sum(PreviousYear) PreviousYear-PriorVersion

I don't have PriorVersion and Variance columns.

PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)

I can query if I have column, since I don't have these two column how could I possibly achieve the output using my existing table schema.

View 1 Replies View Related

SQL Server 2014 :: Show Sum Of Particular Column In Last Row Of Table

Sep 18, 2015

I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.

SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;

View 4 Replies View Related

Float Column Not Show Scientific Notation

Oct 16, 2013

We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).

is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.

View 8 Replies View Related







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