Need help writing query

Question

Need help writing query

Brian Pastre

SSC-Addicted

Points: 494
More actions
June 23, 2006 at 4:02 pm

#168714

Hello all,
I am a system administrator and have slowly been assuming more DBA responsibilities recently. I have been asked to write a query to get the total call count by day for each sales rep in our sales department. The query gets called from a vbscript file which runs at 12:05 every night to get the previous day’s call totals and gets written to an html file for viewing on our intranet. The output would look like this after the script was run on Tuesday am.

Extension
Name
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Total
5180
User1
0
14
0
0
0
0
0
14
5202
User2
0
3
0
0
0
0
0
3

This is the query I’m working with.:

SELECT count(*) As 'Monday Outbound', User_ID, Orig_Party_Number AS Extension
FROM Tbl_Billing_Data WHERE (Orig_Date >= CONVERT(CHAR(10),GETDATE()-1,110)) AND (Orig_Date < CONVERT(CHAR(10),GETDATE(),110)) AND (Orig_Party_Number LIKE '52%' OR Orig_Party_Number = '55180') AND Call_Classification <> 'I'
Group By User_ID, Orig_Party_Number

The problem is the html file gets overwritten each night so this query would only work for getting the previous day’s call totals and thus would overwrite the file so only the previous day’s call totals would be displayed instead of each day of the week. I guess what I’m looking for is a query that would give me the call totals for each day of the week.

TIA,
Brian

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Why not just create a table and kick off an INSERT INTO with your SELECT statement above? You'll save each day's call totals in the table every night. You could write a simple ASPX (or even ASP) page to query the table and display it's contents in HTML for any specified day, week, month, or whatever...

Or you could create an SP that accepts a DATETIME parameter and performs your query above with that value instead of GETDATE().

If you're looking for the same results any day, you can replace the GETDATE() calls with the specified date and it will return the specified data for that date. Not sure why you're converting the date before comparing to Orig_Date however, but whatever makes you happy

kgayda SSCrazy Points: 2157 More actions · Answer 2

You are looking to do a cross-tab query. If you have SQL Server 2005, you can use the PIVOT function. Use books online for examples. If you are using SQL Server 2000, you will need to use a stored proc and dynamic sql to get the same result. Do a search on cross-tab on this site for examples

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

Shoot... here we go... this is a perfect example of what I've spoken of in the past... You had to write a little VB thingy to make a Web Page from some SQL data...

... and you didn't need to because SQL Server can do it for you.

Ok, Brian... run like hell... here they come... all the folks that insist that "stuff like this should be done in the application" without realizing that SQL Server can be that application and is frequently better at it than some other application!

First, let's create a simple test table and populate it with data to simulate your table and data...

--===== Create a test table for this problem
 CREATE TABLE YourTableName
        (
        RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        [User_ID] VARCHAR(32), 
        Orig_Party_Number VARCHAR(5),
        Orig_Date DATETIME,
        Call_Classification CHAR(1)
        )

--===== Populate the test table with some simple data
 INSERT INTO YourTableName
        ([User_ID],Orig_Party_Number,Orig_Date,Call_Classification)
 SELECT 'User1',5201,'20060614 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060614 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060615 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060615 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060616 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060616 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060618 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060618 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060620 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060620 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060621 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060622 12:05','0' UNION ALL
 SELECT 'User2',5202,'20060614 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060614 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060615 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060615 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060616 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060616 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060618 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060618 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060620 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060620 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060621 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060622 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060623 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060624 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060625 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060626 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060627 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060628 12:05','O' UNION ALL
 SELECT 'User4',4202,'20060621 12:05','O' UNION ALL
 SELECT 'User4',4202,'20060622 12:05','O' UNION ALL
 SELECT 'User5',55180,'20060621 12:00','O'

So far, so good...

Now, let's first write a query to solve the problem of how to capture the data you want... as one of the other good respondents said, you need a crosstab. I went a little further and made it so that no matter when you run it, it will ALWAYS give you the current week's information EXCEPT when you run it on Sunday morning at 12:05 AM... Then it will give you the previous week's information which ended 5 minutes ago. That means this baby is a totally create it, schedule it, and forget it run... hmmm... sounds like an application to me, so far.

 SELECT 
        Orig_Party_Number AS Extension,
        [User_ID] AS Name,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Sun%' THEN 1 ELSE 0 END) AS Sun,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Mon%' THEN 1 ELSE 0 END) AS Mon,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Tue%' THEN 1 ELSE 0 END) AS Tue,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Wed%' THEN 1 ELSE 0 END) AS Wed,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Thu%' THEN 1 ELSE 0 END) AS Thu,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Fri%' THEN 1 ELSE 0 END) AS Fri,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Sat%' THEN 1 ELSE 0 END) AS Sat,
        COUNT(*) AS Total
   FROM YourTableName
  WHERE Call_Classification <> 'I'
    AND ORIG_DATE >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
                   - CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Sun%' THEN 7 ELSE 0 END
    AND ORIG_DATE <  DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)
                   - CASE WHEN DATENAME(dw,Orig_Date) LIKE 'Sun%' THEN 7 ELSE 0 END
    AND (Orig_Party_Number LIKE '52%'
         OR
         Orig_Party_Number = '55180')
  GROUP BY Orig_Party_Number,[User_ID]

... and that produces the following output...

Extension Name  Sun Mon Tue Wed Thu Fri Sat Total
5201      User1 1   0   1   0   1   0   0   3
5202      User2 2   0   2   1   1   1   1   8
55180     User5 0   0   0   1   0   0   0   1

Omigosh!!! THAT's almost exactly what you want for the report! Too bad we have to have an "application" convert it to HTML ... or do we?

If we convert all of the single quotes in the query to 2 single quotes, wrap the whole query in single quotes to make it a string literal, and drop it into a very underutilized system stored procedure like this...

   EXEC dbo.sp_MakeWebTask 
        @OutputFile = 'C:\Temp\HTMLTest.html',
        @FixedFont    = 1,
        @ColHeaders   = 1,
        @LastUpdated  = 1,
        @HTMLHeader   = 2,
        @UserName     = 'user name to run as',
        @WebPageTitle = 'HTML Test Directly from SQL Server' ,
        @ResultsTitle = 'HTML Test Directly from SQL Server',
        @Query = '
 SELECT 
        Orig_Party_Number AS Extension,
        [User_ID] AS Name,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sun%'' THEN 1 ELSE 0 END) AS Sun,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Mon%'' THEN 1 ELSE 0 END) AS Mon,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Tue%'' THEN 1 ELSE 0 END) AS Tue,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Wed%'' THEN 1 ELSE 0 END) AS Wed,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Thu%'' THEN 1 ELSE 0 END) AS Thu,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Fri%'' THEN 1 ELSE 0 END) AS Fri,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sat%'' THEN 1 ELSE 0 END) AS Sat,
        COUNT(*) AS Total
   FROM YourTableName
  WHERE Call_Classification <> ''I''
    AND ORIG_DATE >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
                   - CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sun%'' THEN 7 ELSE 0 END
    AND ORIG_DATE <  DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)
                   - CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sun%'' THEN 7 ELSE 0 END
    AND (Orig_Party_Number LIKE ''52%''
         OR
         Orig_Party_Number = ''55180'')
  GROUP BY Orig_Party_Number,[User_ID]'

It will create a file at the path and file name of @OutputFile (which may also be a URL). And, if you open that file in Internet Explorer?.... ... here's what you get...

HTML Test Directly from SQL Server

Last updated: 2006-06-23 23:30:17.483

Extension	Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total
`5201`	`User1`	`1`	`0`	`1`	`0`	`1`	`0`	`0`	`3`
`5202`	`User2`	`3`	`0`	`2`	`1`	`1`	`1`	`1`	`9`
`55180`	`User5`	`0`	`0`	`0`	`1`	`0`	`0`	`0`	`1`

Yeeeee-Haaaaaaa! "Wez don need no stinkin' application" AND... that's without making a template! If you lookup sp_MakeWebTask in "Books OnLine", it tells you how to make a template where you can control everything from the justification in the cells to the background color to the line styles, cell spacing, etc, etc.

One more thing... if you run it again, tomorrow, it simply overwrites the target file! How easy is that?

So, forget your VBScript file and schedule this bad boy as a job on SQL Server... create it, schedule it, forget it... just like any other app...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian Pastre SSC-Addicted Points: 494 More actions · Answer 4

Awesome Jeff! Enjoying the weekend off and will get to it on Monday. I will post my results.

Thanks Again!

Brian

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 5

Thanks Brian... looking forward to it. Enjoying my weekend, too! Slept the whole day! First time in more than a year!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Lyons SSC Enthusiast Points: 146 More actions · Answer 6

Ed Lyons

SSC Enthusiast

Points: 146

June 25, 2006 at 9:23 am

#645747

Elegant solution. Thank you.

-- Ed Lyons

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 7

You bet, Ed. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 8

Thought I'd take a bit more time to show what you can do with a template (this also correct's a minor logic error in the WHERE clause of the web task).

Here's some updated data...

--===== Create a test table for this problem
 CREATE TABLE YourTableName
        (
        RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        [User_ID] VARCHAR(32), 
        Orig_Party_Number VARCHAR(5),
        Orig_Date DATETIME,
        Call_Classification CHAR(1)
        )

--===== Populate the test table with some simple data
 INSERT INTO YourTableName
        ([User_ID],Orig_Party_Number,Orig_Date,Call_Classification)
 SELECT 'User1',5201,'20060621 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060621 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060622 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060622 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060625 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060625 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060625 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060625 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060626 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060627 12:05','0' UNION ALL
 SELECT 'User1',5201,'20060627 12:00','I' UNION ALL
 SELECT 'User1',5201,'20060627 12:05','0' UNION ALL
 SELECT 'User2',5202,'20060621 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060621 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060623 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060623 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060624 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060624 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060624 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060624 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060626 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060626 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060627 12:00','O' UNION ALL
 SELECT 'User2',5202,'20060627 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060628 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060628 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060629 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060629 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060630 12:05','O' UNION ALL
 SELECT 'User2',5202,'20060620 12:05','O' UNION ALL
 SELECT 'User4',4202,'20060701 12:05','O' UNION ALL
 SELECT 'User4',4202,'20060702 12:05','O' UNION ALL
 SELECT 'User5',55180,'20060701 12:00','O'

Here's what the template looks like (See @TemplateFile in the web task)...

<html>
<!--************************************************************************>
<head>
<!--========================================================================>
<!-- Define local styles by element>
<!--========================================================================>
<style type = "text/css">
    body        {
                font-family:verdana;
                text-align: center;
                }

    caption     {
                font-size:  150%;
                font-family:times;
                }
                
    tr          {
                border:     1px solid black;
                background: white;
                color:      black; 
                font-weight:normal;
                font-size:  80%;
                }

    th          {
                border:     1px solid white;
                background: black;
                padding:    0.0px 3.0px 0.0px 3.0px; 
                text-align: center;
                color:      white; 
                font-weight:bold;
                font-size:  120%
                }
                
    td.left     {
                padding:    0.0px 3.0px 0.0px 3.0px; 
                text-align: left;
                }
                
    td.right    {
                padding:    0.0px 3.0px 0.0px 3.0px; 
                text-align: right;
                }
    td.rightbold{
                border-left-width:  5px;
                border-right-width: 5px;
                padding:    0.0px 3.0px 0.0px 3.0px; 
                text-align: right;
                font-weight:bold;
                }
                
</style>

<!--========================================================================>
<!-- Window header>
<!--========================================================================>
<title>
    HTML test using template file
</title>

</head>    
<!--************************************************************************>
<!--************************************************************************>
<body>
<!--========================================================================>
<!-- Document header>
<!--========================================================================>
<h1>Rep Weekly Sales Calls</h1>
<h3>Created by SQL Server Using a Template File</h3>
<table border="0" cellspacing="0">
        <%begindetail%>
            <tr>
                <td class="left">For Week Starting: <%insert_data_here%></td>
            </tr>
        <%enddetail%>
</table>
<hr>
<!--========================================================================>
<!-- First Table Definition>
<!--========================================================================>
<p>
    <table border="1" cellspacing="0">
        <!--======= Table Caption =======>
        <caption>Total Call Count by Day for Each Sales Rep</caption>

        <!--======= Table Header Row =======>
        <tr>
            <th>Extension</th>
            <th>Name</th>
            <th>Sun</th>
            <th>Mon</th>
            <th>Tue</th>
            <th>Wed</th>
            <th>Thu</th>
            <th>Fri</th>
            <th>Sat</th>
            <th>Total</th>
        </tr>

        <!--======= Table Detail Rows =======>
        <%begindetail%>
            <tr>
                <td class = "left"      > <%insert_data_here%></td>
                <td class = "left"      > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "right"     > <%insert_data_here%></td>
                <td class = "rightbold" > <%insert_data_here%></td>
            </tr>
        <%enddetail%>
    </table>
</p>
</body>
<!--************************************************************************>
</html>

Here's the SQL Script (could easily be changed into a sproc)...

--===== Generate the web page
   EXEC dbo.sp_MakeWebTask 
        @OutputFile   = 'C:\Web\HTMLTest.html',
        @TemplateFile = 'C:\Web\HTMLTest.tpl',
        @DBName = 'Belution',
        @Query = '
 SELECT CONVERT(VARCHAR(12),
                DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
              - CASE WHEN DATENAME(dw,GETDATE()) LIKE ''Sun%'' THEN 7 ELSE 0 END
        ,107)
 SELECT 
        Orig_Party_Number AS Extension,
        [User_ID] AS Name,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sun%'' THEN 1 ELSE 0 END) AS Sun,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Mon%'' THEN 1 ELSE 0 END) AS Mon,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Tue%'' THEN 1 ELSE 0 END) AS Tue,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Wed%'' THEN 1 ELSE 0 END) AS Wed,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Thu%'' THEN 1 ELSE 0 END) AS Thu,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Fri%'' THEN 1 ELSE 0 END) AS Fri,
        SUM(CASE WHEN DATENAME(dw,Orig_Date) LIKE ''Sat%'' THEN 1 ELSE 0 END) AS Sat,
        COUNT(*) AS Total
   FROM YourTableName
  WHERE Call_Classification <> ''I''
    AND ORIG_DATE >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
                   - CASE WHEN DATENAME(dw,GETDATE()) LIKE ''Sun%'' THEN 7 ELSE 0 END
    AND ORIG_DATE <  DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)
                   - CASE WHEN DATENAME(dw,GETDATE()) LIKE ''Sun%'' THEN 7 ELSE 0 END
    AND (Orig_Party_Number LIKE ''52%''
         OR
         Orig_Party_Number = ''55180'')
  GROUP BY Orig_Party_Number,[User_ID]'

Can't get the output to display in this window because of the template and styles and the restrictions this site has but I don't think you'll be disappointed...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Yeah, sp_makewebtask is great. We're using it for most checks and reports... started with it some time ago because it is easy to incorporate the HTML result into a mail (using xp_smtp_sendmail). If it isn't too big - up to 64kB - it can be inserted into the body, larger files are sent as attachment.

Brian Pastre SSC-Addicted Points: 494 More actions · Answer 10

Jeff,

You're correct. I wasn't dissapointed. This works even better that I could've imagined. I thank you for your time and effort.

Thanks again!

Brian

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 11

Thank you for the feedback...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)