Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Putting it all together in one table

Author  Topic 

jrobin747
Starting Member

48 Posts

Posted - 2013-08-19 : 17:44:23
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 00:09:03
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

48 Posts

Posted - 2013-08-20 : 09:39:06
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
   

- Advertisement -