weekends - 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

weekends

Author

Topic

rohit.manj
Starting Member

11 Posts

Posted - 2010-08-04 : 01:34:32

hi all hi have following sql command
sqlstr = sqlstr & "SELECT em.EmpNo,em.Name,'" + monthname + "' AS [Month]," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d1,4)+' ] : OUT [ '+Right(d1,4)+' ]' AS Day1," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d2,4)+' ] : OUT [ '+Right(d2,4)+' ]' AS Day2," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d3,4)+' ] : OUT [ '+Right(d3,4)+' ]' AS Day3," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d4,4)+' ] : OUT [ '+Right(d4,4)+' ]' AS Day4," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d5,4)+' ] : OUT [ '+Right(d5,4)+' ]' AS Day5," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d6,4)+' ] : OUT [ '+Right(d6,4)+' ]' AS Day6," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d7,4)+' ] : OUT [ '+Right(d7,4)+' ]' AS Day7," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d8,4)+' ] : OUT [ '+Right(d8,4)+' ]' AS Day8," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d9,4)+' ] : OUT [ '+Right(d9,4)+' ]' AS Day9," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d10,4)+' ] : OUT [ '+Right(d10,4)+' ]' AS Day10," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d11,4)+' ] : OUT [ '+Right(d11,4)+' ]' AS Day11," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d12,4)+' ] : OUT [ '+Right(d12,4)+' ]' AS Day12," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d13,4)+' ] : OUT [ '+Right(d13,4)+' ]' AS Day13," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d14,4)+' ] : OUT [ '+Right(d14,4)+' ]' AS Day14," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d15,4)+' ] : OUT [ '+Right(d15,4)+' ]' AS Day15," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d16,4)+' ] : OUT [ '+Right(d16,4)+' ]' AS Day16," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d17,4)+' ] : OUT [ '+Right(d17,4)+' ]' AS Day17," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d18,4)+' ] : OUT [ '+Right(d18,4)+' ]' AS Day18," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d19,4)+' ] : OUT [ '+Right(d19,4)+' ]' AS Day19," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d20,4)+' ] : OUT [ '+Right(d20,4)+' ]' AS Day20," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d21,4)+' ] : OUT [ '+Right(d21,4)+' ]' AS Day21," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d22,4)+' ] : OUT [ '+Right(d22,4)+' ]' AS Day22," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d23,4)+' ] : OUT [ '+Right(d23,4)+' ]' AS Day23," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d24,4)+' ] : OUT [ '+Right(d24,4)+' ]' AS Day24," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d25,4)+' ] : OUT [ '+Right(d25,4)+' ]' AS Day25," & vbCrLf

sqlstr = sqlstr & "' IN [ '+Left(d26,4)+' ] : OUT [ '+Right(d26,4)+' ]' AS Day26," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d27,4)+' ] : OUT [ '+Right(d27,4)+' ]' AS Day27," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d28,4)+' ] : OUT [ '+Right(d28,4)+' ]' AS Day28," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d29,4)+' ] : OUT [ '+Right(d29,4)+' ]' AS Day29," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d30,4)+' ] : OUT [ '+Right(d30,4)+' ]' AS Day30," & vbCrLf
sqlstr = sqlstr & "' IN [ '+Left(d31,4)+' ] : OUT [ '+Right(d31,4)+' ]' AS Day31," & vbCrLf
And i want to eliminate those columns which are weekends like d1,d7,d8,d14
so will you please tell the query.I have weekends in an array like
arr[]=(d1,d7,d8,d14) i want to eliminate these columns.so please help

CRhudmude

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-04 : 02:08:02

since you are dynamically creating the query in your front end application, can't you do it there ?

KH
[spoiler]Time is always against us[/spoiler]

rohit.manj
Starting Member

11 Posts

Posted - 2010-08-04 : 02:20:25

how can i do it?

CRhudmude

rohit.manj
Starting Member

11 Posts

Posted - 2010-08-04 : 02:21:21

quote:
Originally posted by khtan

since you are dynamically creating the query in your front end application, can't you do it there ?

KH
[spoiler]Time is always against us[/spoiler]

how can i do it?/

CRhudmude

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-04 : 02:55:27

i don't know, i am not familiar with the front-end language that you are using. But i presume it should be something like


if < d4 of the month >  is not a weekend then
    sqlstr = sqlstr & "' IN [ '+Left(d4,4)+' ] : OUT [ '+Right(d4,4)+' ]' AS Day4," & vbCrLf
end if

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 05:37:52

Are you using Microsoft SQL Server?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources