Is it possible - ASP Free

ASP Free Forums Sponsor:

December 13th, 2004, 02:29 AM

daveevans

Registered User

Join Date: Dec 2004

Posts: 7

Time spent in forums: < 1 sec
Reputation Power: 0

Is it possible

I am quite new to sql and have a quick question. I have some data in my mssql table with prices in and would like to add these prices together to display in my asp page. Sort of: select all amounts spent on a certain day and add them all together and print them to the page? Does anyone have any ideas?

Thanks in advance

December 13th, 2004, 12:33 PM

Lauramc

SQL Slarentice

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,783

Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095

You could do something like this:

SELECT
sales.DATE,
SUM(sales.TOTAL)

FROM
sales

WHERE
DATEPART(month, sales.DATE) = DATEPART(month, GETDATE()) AND
DATEPART(year, sales.DATE) = DATEPART(year, GETDATE())

ORDER BY
sales.DATE DESC

This would get all the sales by day for the current month of the current year.

I hope this helps

Comments on this post

Currie disagrees: Thanks for all the great help Laura!

December 16th, 2004, 03:46 PM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

Quote:

Originally Posted by Lauramc

Mind if I ask a related question? What if you had another SUM(sales.TOTAL) statement in the same SQL String...but you want to use another set of WHERE reqirements? Is that possible?

December 16th, 2004, 03:59 PM

Lauramc

SQL Slarentice

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,783

Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095

Well, I think it would be if you do a nested select statement. Using my previous example, you could try:SELECT
sales.DATE,
(SELECT SUM(sales.TOTAL) FROM sales HAVING SUM(sales.TOTAL) >= 500)

FROM
sales

WHERE
DATEPART(month, sales.DATE) = DATEPART(month, GETDATE()) AND
DATEPART(year, sales.DATE) = DATEPART(year, GETDATE())

ORDER BY
sales.DATE DESC

But this does not always make a lot of sense to do. If you can explain the problem, I might be able to give you a more relevant example.

December 16th, 2004, 04:13 PM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

Thanks for the suggestions Lauramc, perhaps I can explain what I'm trying to do...

I have 2 tables, Agent & CallQueue. Basically all agent contains is Agent names and Agent ID's. CallQueue contains Agent ID's, Call Start time/date & Call End time/date.

So what I'm trying to do...using a DataReader, is pull the AgentName from Agent, based upon the Agent ID in CallQueue. Then I take the differences in the Call Start & Call End times in Call Queue (to get the total Call Length) and I would like to create a daily total & a monthly total PER agent. I have a Calendar control that the user clicks to select a date. So basically I need a SQL QRY that uses two distinct WHERE requirements. One to create a SUM by pulling dates based upon the selected date & month thus a daily total. The second to create a SUM by pulling dates based upon only the month...thus a monthly total. OR I am trying to use something just like you've suggested above...

Code:

    
    SELECT 
       Agent.AgentName,
	SUM(DateDiff(s,CallStartDate,CallEndDate)) AS DailyTotals,
       SUM(DateDiff(s,CallStartDate,CallEndDate)) AS MonthlyTotals
    
    FROM
       Agent,
       CallQueue, 
       (
    		  SELECT 
    			  CallStartDate, 
    			  CallEndDate 
    		  
    		  FROM
    			  CallQueue
    
    		  WHERE
 			 DatePart(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & "
       )
    
    WHERE
       Agent.AgentID = CallQueue.AgentID
       
       AND
    
       DatePart(d,CallStartDate) = " & AgntTotals.SelectedDate.Day & " 
    
       AND
    
       DatePart(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & "
    
    GROUP BY
       Agent.AgentName

Hopefully that made more sense...

December 16th, 2004, 05:09 PM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

What about using the UNION keyword? (I'm only in my second week of using SQL so I'm discovering these things as I go) Would this be the same result?

SELECT
Agent.AgentName,
SUM(DateDiff(s,CallStartDate,CallEndDate)) AS DailyTotals

FROM
Agent,
CallQueue

WHERE
Agent.AgentID = CallQueue.AgentID
AND
DatePart(d,CallStartDate) = " & AgntTotals.SelectedDate.Day & "
AND
DatePart(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & "

GROUP BY
Agent.AgentName

UNION

SELECT
Agent.AgentName,
SUM(DateDiff(s,CallStartDate,CallEndDate)) AS MonthlyTotals

FROM
Agent,
CallQueue

WHERE
Agent.AgentID = CallQueue.AgentID
AND
DatePart(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & "

GROUP BY
Agent.AgentName

When I use this everything compiles, and it actually works!! But Instead of having 3 columns, like I want (1 for name, 2 for daily total, 3 for monthly total) I have just 2 columns ...with the first appearance of a name being the daily total and the second appearance of a name as the monthly total. Hmm

December 16th, 2004, 05:58 PM

Lauramc

SQL Slarentice

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,783

Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095

Let me see if I have this right.... You need to first figure out the difference between the agent's call start time and call end time. Perhaps you can do an update statement to get this data.

So first you might make sure you have a Call_Length column in your CallQueue table. Then you could create a stored procedure to update the table's call_length column and call that procedure before you process the totals. Then you can refer to the call_length column to get the total call length for your sums.

It looks to me like you need two totals, daily and monthly. If you did something like I mention here, you would just say:

SELECT
agent.agentname,
[Daily_Total] = (SELECT SUM(call_length) FROM callqueue WHERE callqueue.StartDate = " & AgntTotals.SelectedDate.Date & ")"",
[Monthly_Total] = (SELECT SUM(call_length) FROM callqueue WHERE DATEPART(m,callqueue.StartDate ) = " & AgntTotals.SelectedDate.Month & ")"",

FROM
agent

JOIN
callqueue ON
agent.id = callqueue.agentid

GROUP BY
agent.agentname

Note: I did not verify the exact syntax, you should modifiy it to fit your VB code (is that what you are using?)

As for the UNION command, it combines two tables. It would show all the distinct results of both tables, and both tables must have the same structure (column names and data types). I have not as of yet had to use it though it seems to be a fast way to combine tables if you want distinct results from tables set up the same way.
Does this help?

Oh one other thing.. watch out when using the DATEPART command. If you have a database with more than one year's worth of data for example, and you get data for the current month, you also have to specify the current year. Otherwise you will get the total for every "December" in the database.

December 16th, 2004, 06:06 PM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

Thanks so much for the great help Lauramc, that is exactly what I am trying to do. I do indeed need TWO different sums (I need two sums PER Agent also by the way), and that's why I'm having the trouble...since each sum requires different WHERE conditions in the SQL statement. You are correct indeed when you say things would be alot easier if I just have a column 'CallLength' in the CallQueue, but I don't think I can make this happen. Unfortunately, I am unable to add another column to my CallQueue table, (have to work with what I have) thus I'm just left with creating the CallLength and displaying it on my own.

I've been told several times to try and accomplish this through Stored Procedures...but again, I've only been struggling through SQL for about 2 weeks now so I haven't yet got around to learning about Stored Procedures.

Your code:

Code:

 SELECT
     agent.agentname,
 [Daily_Total] = (SELECT SUM(call_length) FROM callqueue WHERE callqueue.StartDate = " & AgntTotals.SelectedDate.Date & ")"",
 [Monthly_Total] = (SELECT SUM(call_length) FROM callqueue WHERE DATEPART(m,callqueue.StartDate ) = " & AgntTotals.SelectedDate.Month & ")"",

is quite interesting. Are the [Daily_Total] & [Monthly_Total] just variables I set? Say as integers? Or are they part of a Stored Procedure? I've tried this approach before, but I have several Agents, thus at the end I have a GROUP BY call, thus this SubQuery setup returns more than one value...and VisualStudio.NET complains. Sorry for all the clarification I'm asking for...your help is excellent though and I appreciate it very much!

December 16th, 2004, 06:20 PM

Lauramc

SQL Slarentice

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,783

Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095

The [Daily_Total] = (.....) is just a way to set the column name. You do not need a variable, but the result set will show the "new" column names.

As for the stored procedures, I use Access 2000 to write them, but you can also use SQL Server to do it. In SQL Server, use your Enterprise manager to locate your server. Clicking on the plus sign should bring up a few things like "Diagrams", "Tables", "Views", "Stored Procedures".... If you right click on stored procedures, it will allow you to add a new procedure. Start your SQL with the words "Create Procedure" then put in the name of the procedure that you will refer to later (such as UpdateCallQueue). This way you can go into the query analyzer (or any query interface) and run the SQL by typing the name (UpdateCallQueue, for example). As for adding a column, you can do that by using the ALTER TABLE command:

ALTERTABLE callqueue ADD[call_length] int NULL (for example). Performing this once in the query analyzer would add that column. You must add it each time the table is re-created though, so if you drop the table, be sure to add it again.

I just now noticed that you are doing this for multiple agents at a time. That does complicate things a little bit. I can help you, but am wondering... will you always be doing this for every agent?

#10

December 16th, 2004, 06:32 PM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

Sorry things are so convoluded. I pretty much am not allowed to alter the database in any form...at least at this stage. Thus why I can't add the CallLength Column to the CallQueue Table. Perhaps I'm just not conceptualizing this properly...when I read in the Table with the DataReader, do I pretty much have a 'copy' of the table that I can then alter?

But back to what you were saying...I pretty much just have an .aspx page that displays info from my SQL database for laymen around the office so they don't have to actually go into the database itself and look at stuff.

So pretty much the page is simple...with nothing more than my datagrids and tables with agent info...and finally a calendar. When a user clicks on the calendar to select a date...a table is present which displays all current agents in the first column, each respective agents daily total (based upon the date selected on the calendar) in the second column and finally each agents monthly total (based upon the month of the selected date in the caledar) in the third column.

Calls happen continously, thus there's a refresh button on the page which, when clicked, should recalculate everything. So I guess 'Yes' I am doing this every time for every agent. I'll work on Stored Procedures, since perhaps that is the key here.

Also, I just use the AS keyword to set the Column names...just as you used [Daily_Totals] & [Monthly_Totals] correct? Such as :

SUM(DATEDIFF(d,CallStartDate,CallEndDate)) As Daily_Totals

Here is something along the line of basically what I think should work

Code:

Time_Totals_Qry = "SELECT SUM(DateDiff(s,CallStartDate,CallEndDate)) AS DailyTotals,SUM(DateDiff(s,CallStartDate,CallEndDa  te)) AS MonthlyTotals FROM CallQueue,(SELECT CallStartDate,CallEndDate FROM CallQueue WHERE DATEPART(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & ") GROUP BY CallQueue.AgentID"

Thus I am pulling the first SUM from CallQueue (fairly straightforward) and I am pulling the second SUM from the SubQuery (SELECT CallStartDate,CallEndDate FROM CallQueue WHERE DATEPART(m,CallStartDate) = " & AgntTotals.SelectedDate.Month & ")

But I'm not sure if I'm allowed to use SubQueries after the keyword FROM...

Thanks again Lauramc! I really do appreciate your continued help. I'm gonna sign off for the night, I'll see if I can do some Stored Procedure research tonight. Thanks again.

#11

December 16th, 2004, 07:17 PM

Lauramc

SQL Slarentice

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,783

Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095

Yes your use of "AS" is also acceptable.

Now assuming that you will do this for all agents in the agent table you will likely need a cursor. You would be better off doing this part in a stored procedure since the code is lengthy.

You can create a copy of the table. I would see if you can do that such as:

Code:

 
ifexists(select*from dbo.sysobjects where id =object_id(N'[callqueuecopy]')and OBJECTPROPERTY(id, N'IsUserTable')=1) droptable[callqueuecopy]
SELECT * INTO callqueuecopy FROM callqueue " Then do the ALTER TABLE command to add the call_length column.  Use an update command to update the call length.

Create a table for you agent totals using a similar statement to the one I have above, making sure to add the columns daily_total and monthly_total. For the sake of example, I am calling this new table AgentTotals. This table will have four columns; agentid, agentname, daily_total, monthly_total.

Then do this in the stored procedure window:

Code:

 
 
CREATE PROCEDURE CalculateAgentTotals (@Date as datetime) As  --note this is to create a procedure that accepts a date as a parameter
 
DECLARE @AgentID int
DECLARE @AgentName varchar(50)
DECLARE @DailyTotal int
DECLARE @MonthlyTotal int
 
DECLARE GetAgentTotals CURSOR FORWARD_ONLY FOR
SELECT
	agentid
FROM
	agent
 
OPEN GetAgentTotals
 
FETCH NEXT FROM GetAgentTotals INTO @AgentID
WHILE @@FETCH_STATUS = 0

BEGIN
 
SELECT @AgentName = (SELECT agentname FROM agent WHERE agentid = @AgentID)
 
SELECT @DailyTotal = (SELECT SUM(callqueuecopy.call_length) FROM callqueuecopy WHERE callqueuecopy.start_date = @Date AND callqueuecopy.agentid = @AgentID)
 
SELECT @MonthlyTotal = (SELECT SUM(callqueuecopy.call_length) FROM callqueuecopy WHERE DATEPART(m, callqueuecopy.start_date) = DATEPART(m, @Date) AND DATEPART(y, callqueuecopy.start_date) = DATEPART(y, @Date) AND callqueuecopy.agentid = @AgentID)
 
INSERT INTO AgentTotals
(
	agentid,
	agentname,
	daily_total,
	monthly_total
)
VALUES
	@AgentID,
	@AgentName,
	@DailyTotal,
	@MonthlyTotal
	
 
FETCH NEXT FROM GetAgentTotals INTO @AgentID
END

CLOSE GetAgentTotals
DEALLOCATE GetAgentTotals

OK now one caveat... I am not entirely sure if all this will be exact once you code it. You will have to fiddle with it a bit I am sure. Also your SQL login for the page will have to have read/write privileges. Good Luck!

#12

December 17th, 2004, 10:18 AM

Currie

Contributing User

Join Date: Nov 2004

Location: Seattle, WA

Posts: 41

Time spent in forums: 6 h 13 m 28 sec
Reputation Power: 4

Thanks so much Lauramc, I'll do my best to get this Stored Procedure working.

Thanks again for all your continued help & advice!! I really appreciate it.

#13

December 17th, 2004, 01:44 PM