[SQL Server] Really confused newbie - Perhaps a Temp Table query? |
- Really confused newbie - Perhaps a Temp Table query?
- Performing calculations on subtotals
- Creating an SQL Temp Table?
- Move Database From One Server to Other
- View results into string
- Extract only the last three days
- Create Insert data script for views
- Trying to make my lookup table unique
| Really confused newbie - Perhaps a Temp Table query? Posted: 05 Mar 2013 12:58 AM PST Hi Guys,I'll basically do my best to describe what I'm trying to accomplish, and give you the query I have so far (which isn't correct). I have several different tables. In this case, three: ERTutAccounts, ERTutPositions, and ERTutSecMast.What I'm trying to accomplish is a table where one result set lives. Within this table I'd ideally like to have:Portfolio | PortfolioID | # Of Securities | Total Market Value of PortfolioI've gotten as far as Column 1, Column 2, and Column 4, but am unable to add the 3rd column (counting the # of securities in each portfolio).My query is as follows:SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV From ERTutAccounts, ERTutPositions WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioIDThis query above does the job, but when I add in the Securities...SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV, ERTutSecMast.SecID AS '# of Securities' From ERTutAccounts, ERTutPositions, ERTutSecMast WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID AND ERTutPositions.SecID=ERTutSecMast.SecID GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, ERTutSecMast.SecIDWhen I do this, it will list each SecID individually and repeat the PortfolioID over and over again.I understand this is confusing, and I attempted to make it concise as possible. As you can tell, I'm an extreme newbie who has been using SQL for about three days now. Any input is appreciated! |
| Performing calculations on subtotals Posted: 05 Mar 2013 04:47 AM PST Hello: I'm a new user to SQL Server 2012 Standard. I have a table with information similar to what's found in the spreadsheet attached. I would like to get the total Pay calculated not just by department (e.g. total of CNA is 70.31) but then also have a total of the subtotals of some departments (but not all). For example, I'd like the total of LPN and RN together (both of which are types of nurses, so I want a 'nurse total') but not CNA. Is this possible to do through a SQL query, or another way in SSMS? I've read something about Calculated Members and cubes, is that the direction I have to head in, or can it be handled in SSMS? For the record, I'm using Yellowfin to run reports off of the data in SS 2012 and I can't find a way to create these calculations in YF, so I'm hoping to do it on the server end. I hope this is clear. thank you in advance. |
| Posted: 05 Mar 2013 05:04 AM PST Hi guys,I had reached out to everyone earlier today to ask for help, and I was able to take a lot away from that, so thank you in advance. I'm looking to turn the query that I created into a "Temp Table".I've searched far and wide on the internet and can find little to no literature on it. Can anyone help me get started with creating a temp table?The previous query was as follows:SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV, COUNT(B.SecID) AS [# of Securities] From ERTutAccounts A INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID GROUP BY A.Portfolio, B.PortfolioID HAVING COUNT(B.SecID) > 70 OR SUM(B.MarketValue) > 30000000 Before attempting to build the above query into a temp table, I simply attempted a really trivial table to no avail. Can someone tell me what I'm doing wrong and the right place to begin?CREATE TABLE #Test (PortfolioID int, Portfolio varchar(200)) INSERT INTO #Test (PortfolioID, Portfolio) SELECT PortfolioID, Portfolio FROM ERTutAccounts WHERE PortfolioID = 1 |
| Move Database From One Server to Other Posted: 04 Mar 2013 07:32 PM PST Hi All,What I am trying to do is that I am trying to copy database from Other Sever. I am coping the database from source server and connected it my server. Everything is working fine but at the last it is showing me error like below:SqlServer Agent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)Could please let me know the remedy?Thanks in advance! |
| Posted: 05 Mar 2013 02:15 AM PST I have a table where I need to take any number of financial distributions and stack the accounts into a string that can be used as an array.SELECT POPRCTNM, ACTINDX FROM POP30390 //this would for example return say 3 to 6 rows of data per POPRCTNM each with different ACTINDX numbers and related values (in the database but not requested)So for example:RCT0117382 3061RCT0117382 3061RCT0117382 46What I want would be RCT0117382 3061 3061 46I then would be able to look at ITEMS on the receipt and if they are of a certain class, check if the expected ACTINDX is in the list used (so I can identify problems from improper human processes).So, how would I create a table that groups on the PO Receipt Number and has a string field that I can then use crystal to convert to a searchable array for specific account indexes? |
| Extract only the last three days Posted: 04 Mar 2013 06:20 PM PST Extract only the last three dayshi all, hope in your help.this is my procedure for export in txt file the values of the table in db sql server 2008.the table is on a remote server and the field [myDateString] is nvarchar 255 and I'm not admin.I need extract only the last three days for the table, in this moment extract all current year.Can you help me ?thank you[code] EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM tbl WHERE YEAR(CAST(SUBSTRING([myDateString], 7, 4) AS DATETIME)) = 2013;" queryout "\\myserver\public\tkt.txt" -T -c -t;'[/code] |
| Create Insert data script for views Posted: 04 Mar 2013 03:23 PM PST Dear AllFor one table I need to create insert statemetn with data. For this I used SSMS-Task -Generate script - Data only it creates inserts will all the rows.But i need only few selected rows.Hence created a view and tried the same thing but it does not generate insert script with data.I am doing somthing wrong?Regards |
| Trying to make my lookup table unique Posted: 04 Mar 2013 01:50 PM PST I am running the following query, what I am trying to do is only have in my reference table the first 3 unique columns, without loosing the other column values, I am not interested in the other columns being grouped but SqlSever is forcing me to group by them all with the following error. Msg 8120, Level 16, State 1, Line 6Column 'lookuptable.Software_Subcategory' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.So I have had to group all columnsAny Ideasdrop table lookuptable1select Software_Name_Raw, Software_Version_Raw, Software_Publisher_Raw, Software_Category, Software_Subcategory, MSDN_Flag, CDL_Flag, Source, Pending_Classification_Flag, Auto_Classification_Flag, Software_Classification_Version, Manual_Deletion, Load_Date into lookuptable1from lookuptablegroup by Software_Name_Raw, Software_Version_Raw, Software_Publisher_Raw, Software_Category, Software_Subcategory, MSDN_Flag, CDL_Flag, Source, Pending_Classification_Flag, Auto_Classification_Flag, Software_Classification_Version, Manual_Deletion, Load_DateAS YOU CAN SEE FROM MY RESULTS BELOW I ONLY NEED THE FIRST 3 COLUMNS BELOW for reference BUT I STILL NEED ALL THE DATA SO BASICALLY 1 OF THE ROWS COULD BE DELETED from the first 2 ROWS. as the first 3 columns match select * from lookuptable1 where software_name_raw = 'Acrobat' order by software_name_rawACROBAT,6.X,Adobe Systems, Inc.,Licensable,Edition Unknown,FALSE,FALSE,TAD4D-Matched-DRN,No,Yes,0023,NULL,2012-09-08 16:41:38.000ACROBAT,6.X,Adobe Systems, Inc.,Licensable,Edition Unknown,FALSE,FALSE,TAD5A-matched,DRN,No,Yes,0023,NULL,2012-09-08 16:41:38.000ACROBAT,7.x,Adobe Systems, Inc.,Licensable,Edition Unknown,FALSE,FALSE,TAD4D-Matched-DRN,No,Yes,0023,NULL,2012-09-08 16:41:38.000 |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment