SQL QUERY DATES Please - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL QUERY DATES Please

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-27 : 08:47:09

shabir writes "i have sql query like
select * from table where emp_code='"&emp&"' and day(dte)>='"&beginday&"' and day(dte)<='"&endday&"'"
this is retrieving correct result but while displaying data
i am not able to associte the date field with each emp_code
like

emp_code istday endday 3rdday....... till end of month
10000000 present present Absent
10000002 Absent present Absent
10000003 Absent present Absent
Remember there can be some times no record for emp_code,any way it should retrieve data like above i have shouwn"

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-27 : 17:21:50

if your table is something like tblAssistance(empCode,wordDay,assistance) then you would need to make a cross table

Another thing you shouldn't discriminate by the day (day(WorkDay) between day(@beginDate) and
day(@endDate)) discriminate by the whole date instead and display in one column month and year,
and the days you want to show in different columns you can use a view or a stored procedure

but I think you can easily create this kind of reports with Crystal Reports

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-28 : 11:18:14

[code]Declare @Assistance Table
(
empID char(10),
workDay datetime,
assistance bit
)

Insert into @Assistance
Select 10000000, '10/1/2004', 1 Union
Select 10000000, '10/2/2004', 0 Union
Select 10000000, '10/3/2004', 0 Union
Select 10000000, '10/4/2004', 1 Union
Select 10000000, '10/5/2004', 0 Union
Select 10000000, '10/6/2004', 1 Union
Select 10000000, '10/7/2004', 1 Union
Select 10000000, '10/8/2004', 1 Union
Select 10000000, '10/9/2004', 1 Union
Select 10000000, '10/10/2004', 0 Union
Select 10000002, '10/1/2004', 0 Union
Select 10000002, '10/2/2004', 0 Union
Select 10000002, '10/3/2004', 0 Union
Select 10000002, '10/4/2004', 1 Union
Select 10000002, '10/5/2004', 0 Union
Select 10000002, '10/6/2004', 1 Union
Select 10000002, '10/7/2004', 0 Union
Select 10000002, '10/8/2004', 1 Union
Select 10000002, '10/9/2004', 1 Union
Select 10000002, '10/10/2004', 1 Union
Select 10000003, '10/1/2004', 0 Union
Select 10000003, '10/2/2004', 0 Union
Select 10000003, '10/3/2004', 0 Union
Select 10000003, '10/4/2004', 1 Union
Select 10000003, '10/5/2004', 0 Union
Select 10000003, '10/6/2004', 0 Union
Select 10000003, '10/7/2004', 0 Union
Select 10000003, '10/8/2004', 1 Union
Select 10000003, '10/9/2004', 1 Union
Select 10000003, '10/10/2004', 1

Select empID, max(right(convert(char (11),workday,106),8)) as AssistanceMonth,
max(case datepart(day,workday)
When 1 then
case assistance
When 1 then 'Present'
else 'Absent'
end
else
''
end) as day1,
max(case datepart(day,workday)
When 2 then
case assistance
When 1 then 'Present'
else 'Absent'
end
else
''
end) as day2,
max(case datepart(day,workday)
When 3 then
case assistance
When 1 then 'Present'
else 'Absent'
end
else
''
end) as day3
.
.
.
from @Assistance
group by empID[/code]

Results

[code]empID AssistanceMonth day1 day2 day3 ...
---------- --------------- ------- ------- ------- ...
10000000 Oct 2004 Present Absent Absent ...
10000002 Oct 2004 Absent Absent Absent ...
10000003 Oct 2004 Absent Absent Absent ...[/code]
To determine the amount of working dates
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40692

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources