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.
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 MembersDays without a saleDaily ApplicationsWeekly ApplicationsSum of Weekly Leases FundedSum of Weekly Leases SubmitCount of Leases FundedSome 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.UserIDFROM 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.ApplicationIDWHERE STM.activeStatus=1GROUP 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 commaCREATE TABLE #TEMPDASHBOARD(UserID INT,SalesteamID INT,SalesName VARCHAR(50),SalesTeamName VARCHAR(50), --TeamDescription from SalesTeamDaysWithOutSale INT,DailyAppsIn INT,WeeklyAppsIn INT,WeeklyLeasesIn INT,LeasesFundedCount INT,LeasesDollarsFunded INT,LeaseDollarsSubmitted money,)After that post exact error message if any...--Chandu |
|
|
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. |
|
|
|
|
|
|
|