How to manage Date columns in reports? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Development Tools

Reporting Services Development

How to manage Date columns in reports?

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-14 : 03:41:16

Hi all,
I have a report which has a date field, end_date.
But in the table few of the rows are NULL for end_date.
But in the report, the value of those null fields is '01-01-1900'
I found this was due to the cdate function i used in the expression.

Is there any way by which i can retain those values as null and as well have my other values in the "dd-MMM-yyyy" format?

It works if i remove that cdate function, but i wont get the desired format if i do so....So please help me in getting the correct format
along with null.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-14 : 04:32:54

Hello, try this:
=IIF(IsDate(Fields!end_date.Value),CDate(here comes your cdate),"")

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-14 : 05:41:39

hi,
thanks for your suggestion.I tried this but it did not work. I have few doubts:
IsDate works similar to Isnull isn't it?
Then i will have to write it as
IIF(IsDate(Fields!end_date.Value)is "null","",CDate(Fields!end_date.Value).tostring("dd-MMM-yyyy"))

i tried this way and it is not displaying values. Can you tell me where have i gone wrong?

thanks again....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-14 : 06:43:09

Hi,
I asked google and it told me to solve the problem using custom code:
http://www.mydatabasesupport.com/forums/ms-sqlserver/232508-date-conversion.html
I hope that works for you.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-14 : 07:59:11

Hi,
thanks a lot for your reply. I used some other method to get the result.But thanks for this info.
can you please help me on one more issue on sql server?

I have posted the question under "New to SQL Server" group.
I have a resource table which gives info abt employees and the projects they are working on.
It has two date fields, start_date and end_date.
I have to create a report to list the no. of employees who are free between a given date range.

the report is working now but if an employee is working on more than one project then that record repeats. to avoid this, i want to choose the date range between start_date and max(end_date) so that there will not be any repetition.

Can you please tell me how can i do that in the query? Pls help me.....

Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 08:07:47

please post some data to clarify your requirement.

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-14 : 08:39:51

Employee | Project | Start Date | End Date
ABC | PROJECT 1 | 03-Sep-2007 | 11-Jul-2008
ABC | PROJECT 2 | 02-Oct-2006 | 30-Nov-2006
ABC | PROJECT 3 | 11-Jul-2008 | 11-Jan-2009

The data is like this. Here, as you can see the employe ABC is in 3 projects.
But i want only one single record for ABC and the date range must be between start_date and the max(end_date) so that it gives an idea of till when he is busy...(in this case till 11-Jan-2009)
how to obtain this in a single row?

Thanks again....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-14 : 09:41:45

you can do something like
select
Employee,
max(Project), -- max () because only one can be displayed
min(start_date),
max(end_date)
from resource_table
group by Employee

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 13:22:44

quote:
Originally posted by mrm23

Employee | Project | Start Date | End Date
ABC | PROJECT 1 | 03-Sep-2007 | 11-Jul-2008
ABC | PROJECT 2 | 02-Oct-2006 | 30-Nov-2006
ABC | PROJECT 3 | 11-Jul-2008 | 11-Jan-2009

The data is like this. Here, as you can see the employe ABC is in 3 projects.
But i want only one single record for ABC and the date range must be between start_date and the max(end_date) so that it gives an idea of till when he is busy...(in this case till 11-Jan-2009)
how to obtain this in a single row?

Thanks again....

ok so what happens if he has a free time b/w two projects? shouldnt that be considered?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-14 : 13:27:08

Hello visakh16,
I have seen in an older Post of mrm23 that it should not.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-16 : 06:48:20

Hi all,

I have got a query for the above. Its lists all the employees with their latest project details.
i.e, with max(end_date). Even repetition is avoided here :)
The query is like this:

select e.emp_name,p.prj_project_name,rra.rra_start_date,rra.rra_end_date
from rpmg_resource_allocations rra
inner join employee e on e.emp_seq_no = rra.emp_seq_no
and e.emp_status_item_code = 1 and rra.rra_status = 1
inner join project p on p.prj_seq_no = rra.prj_seq_no
inner join sys_business_code_detail sbcd on sbcd.sbd_item_code = p.prj_status_item_code
and sbcd.sbm_type_code = 'SZ' and sbcd.sbd_item_desc not in ('Completed','Scrapped')
where rra.rra_end_date =
(select max(rra1.rra_end_date) from rpmg_resource_allocations rra1
where rra1.emp_seq_no = e.emp_seq_no)
order by e.emp_name

Now i want to use the result of the query elsewhere.
I want to list those employees who are free in a particular period of time.
The use enter from and to dates, and the employees' start and end dates should NOT fall in tht range.

I will get those START and END dates from the above query.
Now i must get something like:

select emp_name where @from_date NOT BETWEEN (START_DATE) and (END_DATE)
(where the start and end dates are from the above query.)
Can anyone help writing this?

Thanks in advance........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 08:13:34

If there are no intermediate gaps you just need to look at those employees having max end date <= pass start date of period.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources