queryyyy nested - 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

queryyyy nested

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-12 : 01:47:52

guyz i am desginng a report .. i want a query in such that

total count of day = 5 dayz (suppose) - total of pub holidayz == total wrk hrs = 4*8 = 32

suppost on each dayz he has wkrd 8+6+4+4+8=30

I NEED TOTAL PERCENTAGE in my reports .... 30*100/32 = 90%

any ideas ......

how shuld i use m query

Declare @start_Date datetime
Declare @end_Date datetime
SET @start_Date = '2012-01-01'
sET @end_Date = '2012-01-06'
declare @a int
set @a = DAY (@end_Date) - day(@start_Date) + 1
PRINT @a

for getting count dayz

declare @b int
set @b = select COUNT(*) from @Holidays * 8
print @ b

now how total work hours ..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-12 : 01:53:29

total count of day :


select datediff(day, @start_Date, @end_Date)

total of pub holidayz :


select count(*)
from   @Holidays
where  date between @start_Date and @end_Date

All together now


select (datediff(day, @start_Date, @end_Date) - count(*)) * 8 as [total wrk hrs]
from   @Holidays
where  date between @start_Date and @end_Date

quote:
suppost on each dayz he has wkrd 8+6+4+4+8=30

Not sure how do you get that information . . .

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 02:09:23

quote:
Originally posted by kshitizgp

guyz i am desginng a report .. i want a query in such that

total count of day = 5 dayz (suppose) - total of pub holidayz == total wrk hrs = 4*8 = 32

suppost on each dayz he has wkrd 8+6+4+4+8=30

I NEED TOTAL PERCENTAGE in my reports .... 30*100/32 = 90%

any ideas ......

how shuld i use m query

Declare @start_Date datetime
Declare @end_Date datetime
SET @start_Date = '2012-01-01'
sET @end_Date = '2012-01-06'
declare @a int
set @a = DAY (@end_Date) - day(@start_Date) + 1
PRINT @a

for getting count dayz

declare @b int
set @b = select COUNT(*) from @Holidays * 8
print @ b

now how total work hours ..

do you've workhours per day stored somewhere or atleast start and end datetime of work?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-12 : 03:26:49

select EM.Employee_Name as Name ,DM.Department_Name as Department_Name , FD.Work_Date as DATE, sum(FD.Work_Hours) AS HOURS
from Employee_Master EM , Department_Master DM , Form_Details FD
where
Em.Employee_Master_Id = FD.Employee_Id
and DM.Department_Master_Id = FD.Department_Id

and Work_Date between @start_Date and @end_Date
GROUP BY department_name , work_date ,EM .Employee_Name
order by DM .Department_Name , DATE asc ,Name asc
--end

this wil gve me a table like
jon 2011-01-01 IT 5
jON 2011-01-02 IT 8
jon 2011-01-03 IT 5
jon 2011-01-04 IT 0 (ph i want over here)

now suppose 4 is public holiday so it should give it as PH still give me hours 0

my query for PH is

;'SELECT count(*) FROM GSPC_Efiling.dbo.tblHoliday_Master where HolidayDate between '+@startDate +' and '+@endDate +'''

how can i get PH in Date 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 03:31:27

you would need a calendar table for that. left join your table to it on condition Work_Date = date

see the code below for a calendar date function

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources