Function problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function problem

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-05-25 : 08:18:46

please can any one explain what is the meaning of this numbers whose written in red

SUM(DATEDIFF(s, CONVERT(DATETIME, TimeIn, 108), CONVERT(DATETIME, TimeOut, 108)))
/ 86400 Day,

(SUM(DATEDIFF(s, CONVERT(DATETIME, TimeIn, 108), CONVERT(DATETIME, TimeOut, 108))) / 3600

immad uddin ahmed

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-25 : 08:27:06

86400 is the number of seconds in one day.
3600 is the number of seconds in one hour.

The way the expressions are written, the division will be INTEGER DIVISION, which means fractional parts will be discarded. If you want to get fractional days/hours, force floating point division by changing 86400 to 86400.0 and 3600 to 3600.0

immad
Posting Yak Master

230 Posts

Posted - 2013-05-26 : 04:36:02

thanks for explaining me actullay this query made by my senior developer and iam new in sql so i want ot know whats the meaning of that numbers

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:10:21

quote:
Originally posted by immad

thanks for explaining me actullay this query made by my senior developer and iam new in sql so i want ot know whats the meaning of that numbers

immad uddin ahmed

to further clarify

86400=24 hours in the day * 60 minutes per hour * 60 seconds per minute ie total seconds in a day
3600 = 60 minutes in a hour * 60 seconds per minute = total seconds in an hour

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-05-27 : 02:28:12

quote:
Originally posted by visakh16

quote:
Originally posted by immad

thanks for explaining me actullay this query made by my senior developer and iam new in sql so i want ot know whats the meaning of that numbers

immad uddin ahmed

to further clarify

86400=24 hours in the day * 60 minutes per hour * 60 seconds per minute ie total seconds in a day
3600 = 60 minutes in a hour * 60 seconds per minute = total seconds in an hour

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ok i got it

can u explain me what is the meaning in red

(rn = 0 OR rn is null)
AND Date BETWEEN @FromDate and @ToDate
AND (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)
AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%')
AND (ISNULL(@CID,'')='' OR CID = @CID)
AND (ISNULL(@BID,'')='' OR EBID = @BID)
AND (ISNULL(@Grade,'')='' OR Grade = @Grade)

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 02:47:43

[code]1) ISNULL(@EmpID,'')='' --> means If @EmpID is NULL, then replace @EmpID with empty string
2) (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID) --> means search based on specific employee id else all employees in the case of @empId is NULL
3) ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%' ---> Means they are passing comma separated departmentIDs through variable @Dept and searching for those particular departments
4) (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%') --> Means Search based on set of departmentIDs else all departments in the case of @Dept is NULL[/code]

For further information / clarification refer posts of Visakh's & mine in the below link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185597#725390

--
Chandu

immad
Posting Yak Master

230 Posts

Posted - 2013-05-27 : 03:21:39

quote:

Originally posted by bandi

1)   ISNULL(@EmpID,'')=''  --> means If @EmpID is NULL, then replace @EmpID with empty string 
2)   (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)  --> means search based on specific employee id else all employees in the case of @empId is NULL
3)   ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%'   ---> Means they are passing comma separated departmentIDs through variable @Dept and searching for those particular departments
4)   (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%') --> Means Search based on set of departmentIDs else all departments in the case of @Dept is NULL

For further information / clarification refer posts of Visakh's & mine in the below link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185597#725390

--
Chandu

Thanks

immad uddin ahmed

immad
Posting Yak Master

230 Posts

Posted - 2013-05-27 : 03:28:28

quote:

Originally posted by immad

quote:

Originally posted by bandi

1)   ISNULL(@EmpID,'')=''  --> means If @EmpID is NULL, then replace @EmpID with empty string 
2)   (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)  --> means search based on specific employee id else all employees in the case of @empId is NULL
3)   ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%'   ---> Means they are passing comma separated departmentIDs through variable @Dept and searching for those particular departments
4)   (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%') --> Means Search based on set of departmentIDs else all departments in the case of @Dept is NULL

For further information / clarification refer posts of Visakh's & mine in the below link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185597#725390

--
Chandu

Thanks :)

immad uddin ahmed

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 03:31:22

welcome

Did you understand the logic?

In the 3rd condition of my earlier post, there is one limitation..
You must pass the comma separated departments without any spaces and also useful only in the case of small datasets.
For large datasets you prefer to use User-Defined Function (Ex: Split) to split Depts and then make comparision

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources