July 9, 2007 at 10:08 pm
hi guys can anyone here help me to create a TSQL script to generate monthly and weekly reports thx
July 10, 2007 at 12:25 am
Can you be more specific whats the reports that you want?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 10, 2007 at 5:10 am
July 10, 2007 at 5:25 am
oooh srry....
i'm using SQL Express, i want to create a TSQL script that will dynamicaly generate reports example.
1.
Month missed appoint
january, 2007 24
feb,2007 9
2.
Week missed appoint
jan-14-2007 3
jan-21-2007 4
etc
etc
thx again
July 10, 2007 at 8:24 am
July 11, 2007 at 12:45 am
Write a single TSQL statement with taking 2 arguements so that the user can pass the arguments necessary and take reports as per his need.
select * from tablename where datecolumnname between 'startdate' and 'enddate'
hope this helps you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 11, 2007 at 1:55 am
Say you have a table with just one column d containing dates which represent missing appointments, then a monthly statistic would go something like this
select left(convert(varchar,d,120),7),count(*)
from t
group by left(convert(varchar,d,120),7)
order by left(convert(varchar,d,120),7)
and a weekly statistic would go something like this
select year(d),datepart(week,d),count(*)
from t
where d is not null
group by year(d),datepart(week,d)
order by year(d),datepart(week,d)
But you have the usual problem of not seeing any months or weeks for which there are zero missing appointments. For these you need a table spanning months and weeks that you want to consider.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply