Solved

Daily,Weekly, Monthly Report

Posted on 2006-06-19
13
5,840 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Omega002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16935341
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.
0
 

Author Comment

by:Omega002
ID: 16936039
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.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16936208
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)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:folderol
ID: 16936295
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


0
 

Author Comment

by:Omega002
ID: 16992239
Have you given up on this topic?
0
 
LVL 19

Expert Comment

by:folderol
ID: 17010186
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.
0
 

Author Comment

by:Omega002
ID: 17010271
This report is used in SQL Server Reporting Services. I need assistance on building the query itself. Can you assist?
0
 
LVL 19

Expert Comment

by:folderol
ID: 17010646
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.
0
 

Author Comment

by:Omega002
ID: 17010675
What about just building queries? Can you assist in that area?
0
 
LVL 19

Expert Comment

by:folderol
ID: 17011478
What sort of assistance do you need?
0
 

Author Comment

by:Omega002
ID: 17012381
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
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 17026485
SELECT
      T.Entered,
      SUM(1) AS Opened,
      SUM( CASE WHEN T.Entered = CONVERT(varchar(10),Ticket.Closed,101) THEN 1 ELSE 0  END) CLOSED
FROM Ticket
INNER JOIN ( SELECT DISTINCT CONVERT(varchar(10),Entered,101)Entered FROM Ticket ) T
ON T.Entered = CONVERT(varchar(10),Ticket.Entered,101)
GROUP BY T.Entered

SELECT
      T.Entered[Month],
      SUM(1) AS Opened,
      SUM( CASE WHEN T.Entered = DATENAME(mm,Ticket.Closed) THEN 1 ELSE 0  END) CLOSED
FROM Ticket
INNER JOIN ( SELECT DISTINCT  DATENAME(mm,Ticket.Entered)Entered FROM Ticket ) T
ON T.Entered = DATENAME(mm,Ticket.Entered)
GROUP BY T.Entered
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question