SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Putting it all together in one table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 08/19/2013 :  17:44:23  Show Profile  Reply with Quote
I have to re-create a grid like application that was originally done in Excel. What the grid showed a dash board that displayed the names of the sales person along the left and across the top it had various categories.

Here is the SQL part.

I did various queries to do the following:
Sales Team Members
Days without a sale
Daily Applications
Weekly Applications
Sum of Weekly Leases Funded
Sum of Weekly Leases Submit
Count of Leases Funded

Some of the queries needed where clauses but not with the same date rage. After some work I was able to correctly get the proper results.

My next task is to combine the information (queries) together.

I thought the first step was to create a temp table and insert the queries into the temp table. The problem I get is I get errors like the INSERT statement contains more items than the insert list. Or I run into the problem of declared variables for my date range.

I've been given very little direction as to get this going. I was able to do one INSERT INTO with one of my queries.

I know that I will be doing a stored procedure at some point and will be doing some other things.

I did the following and was able to get the first three columns filled in the temp table. After that I kept getting errors.

CREATE TABLE #TEMPDASHBOARD
(
UserID INT,
SalesteamID INT,
SalesName VARCHAR(50),
SalesTeamName VARCHAR(50), --TeamDescription from SalesTeam
DaysWithOutSale INT,
DailyAppsIn INT,
WeeklyAppsIn INT,
WeeklyLeasesIn INT,
LeasesFundedCount INT,
LeasesDollarsFunded INT,
LeaseDollarsSubmitted money,
)

DECLARE @x_strStartDate VARCHAR (10),
@x_strEndDate VARCHAR (10)

SET @x_strStartDate= '03/10/2013'
SET @x_strEndDate= '08/10/2013'

INSERT INTO #TEMPDASHBOARD (SalesName, SalesteamID, UserID)

(
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.SalesteamID, usr.UserID

FROM Users usr
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
LEFT JOIN MerchantApplication mapp ON mapp.Assignedto = usr.UserID
LEFT JOIN MerchantAppStatus mas ON mas.MerchantAppID = mapp.ApplicationID

WHERE STM.activeStatus=1
GROUP BY usr.UserID, usr.FirstName, usr.LastName, stm.SalesteamID
)

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 08/20/2013 :  00:09:03  Show Profile  Reply with Quote
what is the total size of (usr.FirstName + '' + usr.LastName) ?
In the CREATE TABLE statement there is extra comma at the end... Remove that comma
CREATE TABLE #TEMPDASHBOARD
(
UserID INT,
SalesteamID INT,
SalesName VARCHAR(50),
SalesTeamName VARCHAR(50), --TeamDescription from SalesTeam
DaysWithOutSale INT,
DailyAppsIn INT,
WeeklyAppsIn INT,
WeeklyLeasesIn INT,
LeasesFundedCount INT,
LeasesDollarsFunded INT,
LeaseDollarsSubmitted money,
)

After that post exact error message if any...

--
Chandu
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 08/20/2013 :  09:39:06  Show Profile  Reply with Quote
There are only 27 active sale team members. The temp table was made and saw the results. It showed up correctly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000