Daily,Weekly, Monthly Report
Greetings,
I am trying to create a report from a T-SQL query that show the following:
Daily Report
Details
Date Opened Resolved
1/3/2006 26 20
1/4/2006 87 90
1/5/2006 2 2
1/6/2006 26 24
1/7/2006 15 15
1/8/2006 19 10
1/9/2006 10 9
1/10/2006 7 4
Weekly Report
Week Ending Opened Resolved
3/5/2006 22 21
3/12/2006 64 50
3/19/2006 27 26
3/26/2006 50 30
4/2/2006 50 25
Monthly
Month Opened Resolved
February 150 145
March 526 500
April 89 88
All three of these reports are based on the entered(date) field and the closed(date) field in a table called calls.
I am trying to create a report from a T-SQL query that show the following:
Daily Report
Details
Date Opened Resolved
1/3/2006 26 20
1/4/2006 87 90
1/5/2006 2 2
1/6/2006 26 24
1/7/2006 15 15
1/8/2006 19 10
1/9/2006 10 9
1/10/2006 7 4
Weekly Report
Week Ending Opened Resolved
3/5/2006 22 21
3/12/2006 64 50
3/19/2006 27 26
3/26/2006 50 30
4/2/2006 50 25
Monthly
Month Opened Resolved
February 150 145
March 526 500
April 89 88
All three of these reports are based on the entered(date) field and the closed(date) field in a table called calls.
Brian Crowe
how do you determine opened/resolved? you're going to have to provide some information on your table schema i left my crystal ball at home.
ASKER
the resolved field is just an alias of a count in the query. Plus I already gave you the primary fields to base this query on which is the entered field and the closed date fileds. Any other fields in this table are not necessary.
SELECT CONVERT(datetime, CONVERT(varchar, entered, 101), 101) AS [Date], ...
FROM sometable
GROUP BY CONVERT(datetime, CONVERT(varchar, entered, 101), 101)
SELECT DATEADD(day, 7 - DATEPART(weekday, entered), entered) AS [Week Ending], ...
FROM sometable
GROUP BY DATEPART(week, entered)
SELECT DATENAME(month, entered) AS [Month], ...
FROM sometable
GROUP BY MONTH(entered)
FROM sometable
GROUP BY CONVERT(datetime, CONVERT(varchar, entered, 101), 101)
SELECT DATEADD(day, 7 - DATEPART(weekday, entered), entered) AS [Week Ending], ...
FROM sometable
GROUP BY DATEPART(week, entered)
SELECT DATENAME(month, entered) AS [Month], ...
FROM sometable
GROUP BY MONTH(entered)
T-SQL does not allow formatting of headers / footers, and to attempt it means you have to resort to tricks. It's best to handle this in a higher apps, like Excel.
Your query can be
select
'Daily' as Section, Date, Opened, Resolved from Calls
union all
select
'Weekly' as Section, dateadd(day, - datepart(day,Date) +1 , Date) as 'Week Ending',
Sum(Opened) as Opened, Sum(Resolved) as Resolved from Calls
Group By dateadd(day, - datepart(weekday,Date) +1 , Date)
union all
select
'Monthly' as Section, dateadd(day, - datepart(day,Date) +1 , Date) as 'Month Starts On',
Sum(Opened) as Opened, Sum(Resolved) as Resolved from Calls
Group By dateadd(day, - datepart(day,Date) +1 , Date)
---
This supports sorting. If you want the name of the month, it's datename(month,Date) but this conflicts with the UNION ALL, since the first two selects return a datetime as the column, and the last is varchar, plus you can't sort on datename.
Tom
Your query can be
select
'Daily' as Section, Date, Opened, Resolved from Calls
union all
select
'Weekly' as Section, dateadd(day, - datepart(day,Date) +1 , Date) as 'Week Ending',
Sum(Opened) as Opened, Sum(Resolved) as Resolved from Calls
Group By dateadd(day, - datepart(weekday,Date) +1 , Date)
union all
select
'Monthly' as Section, dateadd(day, - datepart(day,Date) +1 , Date) as 'Month Starts On',
Sum(Opened) as Opened, Sum(Resolved) as Resolved from Calls
Group By dateadd(day, - datepart(day,Date) +1 , Date)
---
This supports sorting. If you want the name of the month, it's datename(month,Date) but this conflicts with the UNION ALL, since the first two selects return a datetime as the column, and the last is varchar, plus you can't sort on datename.
Tom
ASKER
Have you given up on this topic?
Do you use Excel? Are you returning this query's results to an ASP page or something?
In Excel, you can use Data -> Pivottable and Pivotchart report -> External data source
to add the query using a wizard. This will return the data to a pivottable wizard, which will allow you to format the data very similar to your example.
Let us know how it works out.
Tom.
In Excel, you can use Data -> Pivottable and Pivotchart report -> External data source
to add the query using a wizard. This will return the data to a pivottable wizard, which will allow you to format the data very similar to your example.
Let us know how it works out.
Tom.
ASKER
This report is used in SQL Server Reporting Services. I need assistance on building the query itself. Can you assist?
Probably not. I don't work in Report Services at the moment. You can post back specific error msgs with the query and I can suggest a fix.
ASKER
What about just building queries? Can you assist in that area?
What sort of assistance do you need?
ASKER
I am trying to create a report from a T-SQL query that will show the following:
Daily Report
Details
Date Opened Resolved
1/3/2006 26 20
1/4/2006 87 90
1/5/2006 2 2
1/6/2006 26 24
1/7/2006 15 15
1/8/2006 19 10
1/9/2006 10 9
1/10/2006 7 4
Weekly Report
Week Ending Opened Resolved
3/5/2006 22 21
3/12/2006 64 50
3/19/2006 27 26
3/26/2006 50 30
4/2/2006 50 25
Monthly
Month Opened Resolved
February 150 145
March 526 500
April 89 88
All three of these reports are based on the entered(date) field and the closed(date) field in a table called ticket
Daily Report
Details
Date Opened Resolved
1/3/2006 26 20
1/4/2006 87 90
1/5/2006 2 2
1/6/2006 26 24
1/7/2006 15 15
1/8/2006 19 10
1/9/2006 10 9
1/10/2006 7 4
Weekly Report
Week Ending Opened Resolved
3/5/2006 22 21
3/12/2006 64 50
3/19/2006 27 26
3/26/2006 50 30
4/2/2006 50 25
Monthly
Month Opened Resolved
February 150 145
March 526 500
April 89 88
All three of these reports are based on the entered(date) field and the closed(date) field in a table called ticket
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.