How To Classify Hours By Ranges Of Time
Mar 31, 2008
Hi, I have the following query that is not working correctly
Basically I have a fields called TimeLeave (datetime) and I want to classify every record according to that field
The multiplication by the field b.Zulu is just to convert to local time, once converted I want to classify the local time with those numbers, but for example nothing falls in the first category and I get a bunch of records with a wrong ID
Any idea what should bet the correct sql statement to classify the records??
Thanks in Advance
CASE
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '22:30' AND '05:59' THEN 1
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '06:00' AND '08:59' THEN 2
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '09:00' AND '11:59' THEN 3
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '12:00' AND '14:59' THEN 4
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '15:00' AND '16:59' THEN 5
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '17:00' AND '19:59' THEN 6
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '20:00' AND '22:29' THEN 7
END
View 3 Replies
ADVERTISEMENT
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
Sep 24, 2007
Hello -
Building a small app for a school nurse......
Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etc
What I need to do is then check to see:
1. What period did they login in?
2. What period did they logout in?
3. How much time in a period did they actually miss?
The ranges look like:
Set @Period1Start='08:20 AM'
Set @Period1End='09:10 AM'
Set @Period2Start='09:15 AM'
Set @Period2End='10:00 AM'
...
...
...
So.....
if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1
If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2
Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.
Thanks!
Dan B
View 5 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 4 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 3 Replies
View Related
May 25, 2008
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 2 Replies
View Related
Jun 3, 2008
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] (
[TIME_ADDED_FIS] datetime NULL,
[PERSON_ID] int NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] (
[PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_ADDED_FIS] datetime NULL,
[LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
GO
View 2 Replies
View Related
May 8, 2008
I'm looking for a way to link records that would be related to an employee that worked during that shift.
we have 2 shifts days and nights going from 6:00AM to 6:00PM
we have records that are written to a table as events happen on equipment on site.
"Equip_Trans"
"Start_time" "Equip_ID" "Shift_date" "Status" "End_Time"
Date-Time, int, Date, varchar(3), Date-time
5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03
5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I have another transaction table "Operator_Trans"
"Operator" "Date" "Shift"
jane Doe, 5/6/2008, 1
Pete Who, 5/6/2008, 2
Sam What, 5/7/2008, 1
Pete Who, 5/7/2008, 2
john Doe, 5/8/2008, 1
jane Doe, 5/8/2008, 2
and last a third table "Shift"
Shift, Start_Time, End_Time
1, 6:00AM, 6:00PM
2, 6:00PM, 6:00AM
I need out put that will show all Equipment status's that started during the shifts worked by an operator
Pete Who, 5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
Pete Who, 5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03 AM
Pete Who, 5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
Pete Who, 5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
john Doe, 5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
john Doe, 5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I am not completely sure the direction I should start off with. I have some SQL knowlege but I would still consider myself new.
Thanks
View 1 Replies
View Related
Aug 31, 2004
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
View 3 Replies
View Related
Apr 18, 2006
I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
View 3 Replies
View Related
Jul 20, 2005
Hello Chaps,Just a little sounding on removing out of hours from some datetimedate that I have.Basically we have a helpdesk that logs calls when they are entered andwhen they are resolved. Now, if this happens during the day we justsubtract one date from the other to give the time it took to resolvethe issue. If it is not solved during the day we have an additional 11hours to add due to people going home and not worrying about peoplesprinters which is good but it buggers up stats that I'm trying toproduce.I haven't got anywhere with this at the mo and am looking at a routinethat compares the resolved time and see's is there is more that 24hours difference and if so remove 11 hours but wondered if you brightsparks had any ideas(also once I write something I tend to find the answer straight afterto confirm me stoppidity).Any ideas let us know.Ginters
View 3 Replies
View Related
Feb 3, 2014
I have a series of times in decimal 15 min slots. The data type is float and the field is the followng:
10
10.25
10.5
10.75
11
and so on
I would like to convert that to the hour and 15 minute slot
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
View 4 Replies
View Related
Sep 19, 2007
Hello,I need to create a column that will store hours bigger than 24. Forexample '25:00:00', '129:23:12', etc).That column will be used too, for perform calculations betweendatetime intervals: 'time'.In MySQL there is a datatype that perfect fits that necessity.Does anyone know what is the corresponding datatype in SQL Server?Thanks a lot!
View 4 Replies
View Related
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
May 30, 2008
How to categorize the SQL Server database size? Like very large databases (VLDBs) etc
SQL Server 2000.
Thank you,
Gish
View 4 Replies
View Related
Sep 16, 2005
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
View 1 Replies
View Related
Feb 26, 2013
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
Code:
Schedule for room called GR4
Course StartDate EndDate BeginTime EndTime HoursPerClass
MATH0001 06-FEB-13 19-FEB-13 0810 1700 9
MATH0002 20-FEB-13 04-Mar-13 0810 1700 9
MATH0003 10-JAN-13 05-Feb-13 0810 1700 9
MATH0004 22-APR-13 17-May-13 0810 1700 9
MATH0005 08-MAR-13 21-APR-13 0810 1700 9
MATH0006 07-JAN-13 09-JAN-13 0910 1300 4
MATH0007 20-JAN-13 17-MAY-13 1710 2000 3
MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code:
select DISTINCT ssrmeet_room_code,
ssrmeet_start_date,
ssrmeet_end_date,
ssrmeet_crn,
ssrmeet_begin_time,
ssrmeet_end_time,
(((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
[code]....
View 2 Replies
View Related
Jun 23, 2015
What is the proper way to ensure when pulling date between two getdates, that you include from midnight of the first getdate to 23:59 hours in the second getdate?
WD.WRKD_WORK_DATE
and WD.WRKD_WORK_DATE between DATEADD(DD, - 11, GETDATE())
and DATEADD(DD, - 5, GETDATE())
View 3 Replies
View Related
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
Sep 19, 2014
I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
View 3 Replies
View Related
Nov 10, 2015
Is it possible to convert for the following SQL statement into SSRS Expression:
SELECT
RIGHT('00' + CONVERT(VARCHAR(2), FLOOR(SUM(Hours))), 2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR(2), FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2),
FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60 - FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)) * 60), 2)
FROM TableTime
For example I need SSRS expression for converting 1.75 hours into 01:45:00.
View 3 Replies
View Related
Nov 5, 2013
I am trying to classify a customer's order method preference based on their history. My source table has every order, order method, date, etc.
The logic is:
If only one order method, that is their preference>1 order method, the majority is their preferenceif 50/50 split, the order method with the mose recent order is the preference
I then created a query to group by the customer, order method, and max(date):
[URL]
I am having trouble though, creating a query that applies the above logic and outputs this:[URL]
View 13 Replies
View Related
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
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
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
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Aug 10, 2006
Im trying to get a table where the columns are a range of dollars spent...
Code:
$1 - $24 $25 - $49 $50 - $74
01-12 Month 5,000 6,000 6,200
13-24 Month 7,000 8,800 9,120
-------------------------
This is what i have so far. It gives me one value. Of course I can run it a bunch of times changing the values, but im just curious if there is a way to do it all at once.
---------------------
Code:
SELECT COUNT(transaction_header.transaction_id) AS CustID
FROM transaction_header INNER JOIN
Northwind.dbo.Ttype ON
transaction_header.transaction_type =
Northwind.dbo.Ttype.transaction_type
WHERE (Northwind.dbo.Ttype.Cat2 = 'CATALOG' OR
Northwind.dbo.Ttype.Cat2 = 'ECOM') AND (DATEDIFF(mm, transaction_header.transaction_date, { fn NOW() }) < 13)
AND
(transaction_header.total_net_retail BETWEEN 1 AND 24)
Thanks,
Dynasty
View 5 Replies
View Related
Jul 1, 2004
Table:
SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue
Example Scenario:
Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.
Any advice would be greatly appreciated!
Thanks in advance,
Wheatster
View 8 Replies
View Related
May 27, 2008
after i create a paratiotion, i want to see the ranges.
when i tried to do :
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
i got the paratition data but not the ranges themselfs.
how can i do this?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
View 1 Replies
View Related
Oct 27, 2014
I'm trying to write a statement that will return all records within a range. The only problem is that the records can have modifiers on them.For example, a sample range I would want would be all records between 1008 and 1120. I can't use a between statement though because these records can end in modifiers, like 1009X or 1117B. How can I search for records within a range when they have modifiers such as above?
View 1 Replies
View Related
Sep 21, 2005
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
View 1 Replies
View Related
Dec 17, 2006
Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
View 6 Replies
View Related
Oct 3, 2007
Hi All,
I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
View 1 Replies
View Related
