|
MIN and MAX are indeed the right functions to use, however, for them to work properly the data must be stored in a DATETIME field, because using strings to store those times will obviously yield string-based results for min & max. There is no way to calculate the min and max times from strings representing dates.
Secondly, in order ot know that 12:15am is after 7:10pm you must also store the date date part along with the time part.
Here is a simple test script that shows the process (and returns the results you want). You'll notice ive used yesterdays date for the 3 values before midnight, and today's date for the one after midnight.
WITH data(firstTimeIn,lastTimeOut )
AS
(
SELECT CAST('4 july 2010 7:10:09:000PM' AS DATETIME),CAST('4 july 2010 9:40:45:000PM' AS DATETIME)
UNION
SELECT CAST('4 july 2010 10:20:32:000PM' AS DATETIME),CAST('5 july 2010 12:15:27:000AM' AS DATETIME)
)
SELECT
MIN(firstTimeIn) as firstTimeIn,
MAX(lastTimeOut) as lastTimeOut
FROM data
Result:
firstTimeIn: 2010-07-04 19:10:09.000
lastTimeOut: 2010-07-05 00:15:27.000
|
|
|
|
|
If at all possible don't use that string format. It will break in other locales.
'2010-07-04 22:20:32:00' is the better format. It is always parsed correctly no matter what the locale.
Jason S Short, Ph.D.
VistaDB Software, Inc.
|
|
|
|
|
JasonShort wrote: It is always parsed correctly no matter what the locale.
You don't say!  I was providing test data for the OP, which incidentally will parse in all locales as the month names are fully qualified.
|
|
|
|
|
SELECT DISTINCT DATEOFWORKS,EMPNAME,DEPARTMENT,SHIFTTIME,FIRSTTIMEIN,LASTTIMEOUT,WORKINGHOURS,BREAKDURATION
from [dbo].[fn_emp_Workdetails]('2833409','9/25/2010','9/26/2010')INNER JOIN
[dbo].[fn_FirstTimeIn_LasttimeOut] ('2833409','9/25/2010','9/26/2010') ON
[dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn_FirstTimeIn_LasttimeOut].EMPLID
INNER JOIN [dbo].[fn__Emp_Working_Hrs]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=
[dbo].[fn__Emp_Working_Hrs].EMPLOYEE_ID INNER JOIN [dbo].[fn__Break_Duration]('2833409','9/25/2010','9/26/2010')
ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn__Break_Duration].EMPLOY_ID
In this stored procedure,the output is
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:21:-24
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:21:-24
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0
26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0
26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0
26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:21:-24
26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:21:-24
The same row appear several times.Please help me to select one row only one times.By using 'distinct' it wouldnt become correct..pls help
|
|
|
|
|
Use dateparts to remove the time component of the fields with time values.
You can also do a double convert > varchar using a formater convert(varchar(20),date,103), then convert the result back to datetime format and use that in the report
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there any way to link two server other than using OPENQUERY.
|
|
|
|
|
Hi All,
I have a stored proceudure which has dynamic sql in it. This stored procedure has be accessed in a crystal report. Crystal report expert is not showing the Stored Procedures columns. Please anybody advice me how to show the columns of a stored procedure which uses the Dynamic SQL.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
I think you will find the MS discovery will also not get and fields either. I'm not sure (never read up on it) just what magic they use to get the columns but it cannot work with dynamic sql. This seems perfectly reasonable to me as dynamic sql allows you to change the column names - 1 dead report.
Usually dynamic sql indicates a change of underlying object, database, server, table or column structure. Unless it is columns then just dummy up a proc for design purposes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Graph is map having rooms, house, roads etc.(i.e edges and nodes to find shortest path).
|
|
|
|
|
The answer to this is, as always, it depends. It depends on the quantity of data, the portability required, the type of data, the type of application and possibly a few others.
As a starter I would consider XML (XAML is for markup) only for minimal data that is only relevant to the current user on the current machine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
All,
I have a slightly complicated issue, I have a Sql 2005 DB which is like 20GB right now, It has different tables related like Projects->Facilities->Locations->Pictures
What I am looking for is the size occupied by an individual project,
Lets say Projects Table has the following
1 Sample Project One
2 Sample Project Two
Facilities has the following
1 1(ProjectId) Facility One
2 1(ProjectId) Facility Two
3 2(ProjectId) Facility Three
and Locations and pictures are related similarly using FacilityId and LocationId
Now I need the data occupied by the entire Project One including the data related to Project One in all other tables.
Hope this is clear,
I welcome any suggestions,
Thanks in advance
|
|
|
|
|
Never having addressed this requirement this is a guess only.
I would take a look at the properties in SSMS, I'm sure the tables have size and rowcount in their properties, this means it is stored in a sys table somewhere. It now becomes a search and mathematics problem. Good Luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can you help me in doing a query in sql that selects for example products that are registered on a date that is on the last week of month, for each month in a specific year?
Thank you
|
|
|
|
|
gertag - read the bloody guidelines, you know the horrible yellow sticky messages at the top of the screen.
Use datepart and datediff to identify the last 7 days of the month and use those dates to filter your product registration.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day,
I wonder if it is possible to joins two columns from two tables (ie column 1 from table A and column 2 from table B) into one column.
Here are my tables (note that the columns from both the tables have the same names)
TableA
----------------------
|Column1 | Column2 |
----------------------
| aaaa | 12345 |
----------------------
| bbbp | 12245 |
----------------------
| ccc | 12245 |
----------------------
TableB
----------------------
|Column1 | Column2 |
----------------------
| asd | 12245 |
----------------------
| bbb | 12245 |
----------------------
| ddd | 12245 |
----------------------
The first part of my SQL statement combines the two columns of each table into one column in each table, thus
TableA
------------------
|Column3 |
------------------
| aaa : 12345 |
------------------
| bbbp : 12245 |
------------------
| ccc : 12245 |
------------------
TableB
------------------
|Column4 |
------------------
| asd : 12245 |
------------------
| bbb : 12245 |
------------------
| ddd : 12245 |
------------------
Now, what I want to do is to combine these two columns into one column.
The SQL statement below gives me the following result
Result 
------------------------------
| Column 1 | Column2 |
------------------------------
| bbbp : 12245 | asd : 12245 |
------------------------------
| bbbp : 12245 | bbb : 12245 |
------------------------------
| bbbp : 12245 | ddd : 12245 |
------------------------------
| ccc: 12245 | asd : 12245 |
------------------------------
| ccc: 12245 | bbb : 12245 |
------------------------------
| ccc: 12245 | ddd : 12245 |
------------------------------
What I want is this : 
----------------
| Column1 |
----------------
| bbbp : 12245 |
----------------
| ccc: 12245 |
----------------
| asd : 12245 |
----------------
| bbb : 12245 |
----------------
| ddd : 12245 |
----------------
Here is my SQL statement
SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3, TableB.Column1 + ' : ' + TableB.Column2 AS Column4
FROM TableA FULL OUTER JOIN
TableB ON TableA.Column1 = TableB.Column1
WHERE (TableA.Column1 IN
(SELECT Column1
FROM TableA
WHERE (Column2 = '12245'))) AND (TableB.Column1 IN
(SELECT Column1
FROM TableB
WHERE (Column2 = '12245')))
I've tried all kinds of joins; left outer, right outer, inner, cross. All with the same results.
I would really appreciate your help, as this is kinda urgent!
Thanx a lot!
Rossouw
P.S I am using SQL Server 2005
|
|
|
|
|
Well, maybe I'm missing something but I don't see how that query gives the result set that you say it does. You are joining tables together where A.Column1 = B.Column1, but in the result set that you have put here, the first row shows A.Column1 = bbbp and B.Column1 = asd.
Why can't you just use a UNION to get what you want?
|
|
|
|
|
As David said go with a union, if there are dupes in the result then use distinct.
SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3
FROM TableA
UNION
SELECT TableB.Column1 + ' : ' + TableB.Column1 AS Column3
FROM TableB
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Puurrrrrrfect! 
Thanks a lot! (Note, column1 should have been column2), but nevertheless, it works beautifully!
Enjoy the rest of the day
Rossouw
|
|
|
|
|
Normaly I am reluctant to supply code, I prefer to give hints only - as David did and encourage you to do some research but as you posted some code you were abviously willing to learn.
Look into UNION and UNION ALL as suggested by Jorgen.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: As David said go with a union, if there are dupes in the result then use distinct
Aren't you mixing up UNION with UNION ALL?
UNION is supposed to be distinct while UNION ALL gives you all rows I thought.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Jörgen Andersson wrote: Aren't you mixing up UNION with UNION ALL?
Uhm yes I am
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join.
like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME".
I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine.
Can anybody please help me in that.
I am putting my query here
And I have to join the Type with the GroupName, but there is no specific table to relate these two.
SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2,
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueId
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
You have a serious problem, it's called crappy data!
You either create a CASE in the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions. CASE needs a change to a procedure if there is a new mapping, map table needs maintenance.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I concur. But how many times have we all see stuff like this?
I like your mapping table solution, unless this is a one off exercise in which case a case statement will run faster and won't need to be maintained.
|
|
|
|
|
Hi,
Yes, I have done it. As my office is far from my house I couldnt inform the same. But your reply is very usefull. Thanks a lot buddy.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
