The following subquery returns 1024 records with a single count column. I am trying to return multiple columns with 1024 records. When I make changes to return multiple columns I get 17,000 records instead of 1024 records. I have very litle experience with subqueries. Does anyone have any suggestions? SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Machine Count', A.hotfixID AS 'Last Contact' FROM (SELECT HF.computerid, HF.hotfixID FROM hotfixdata HFD INNER JOIN hotfix HF ON HF.hotfixid = HFD.hotfixid AND HFD.ignore <> 1 LEFT OUTER JOIN Computers AS C ON C.ComputerID=HF.ComputerID WHERE INSTR(C.os,"microsoft")>0 AND HF.installed <> 1 AND HF.Approved = 1 GROUP BY HF.hotfixID, HF.ComputerID) A;

Please don't respond to my questions anymore.i didn't post a question for some punk kid to use profane language.Also if you have no idea what the requirements are why would you reply with such a stupid reply. Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.

.

The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed? /* PATCH MANAGEMENT */ DELIMITER $$ #MachineType means Server or Workstation as determined by the word server in the os field DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$ CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30), ClientName varchar(30)) BEGIN SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts' FROM (SELECT HF.computerid, HF.hotfixID FROM hotfixdata HFD INNER JOIN hotfix HF ON HF.hotfixid = HFD.hotfixid AND HFD.ignore <> 1 LEFT OUTER JOIN Computers AS C ON C.ComputerID=HF.ComputerID LEFT OUTER JOIN locations AS L ON C.LocationID=L.LocationID LEFT OUTER JOIN Clients AS CL ON C.ClientID=CL.ClientID JOIN agentcomputers AS AC ON C.ComputerID=AC.computerid JOIN mastergroups AS MG ON AC.groupid=MG.groupId #Enter Case When here seperating different where clauses. WHERE CASE WHEN Criteria = 'Workstations Missing Patches' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND HF.installed <> 1 AND HF.Approved = 1 AND LocationID = L.Name AND ClientName = Cl.Name :mad: AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations with Empty Patch Tabs' THEN HF.hotfixid IS NULL AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = Cl.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations Pending Reboots' THEN (C.Flags & 1024) = 1024 AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = C.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Total Patch Workstations' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND LocationID = L.Name AND ClientName = CL.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Servers Missing Patches' THEN INSTR(C.os,"server")>0

I am trying to run the following code in MySQL without using a procedure or function. I keep getting a syntax error. Does anyone see anything wrong with this code. Am I able to run this code without using a procedure or function. DECLARE @Machine varchar(30); SET @Machine = @MachineType; IF @Machine = 'server' THEN SELECT COUNT(*) AS 'Total Servers' FROM agentcomputers AS A WHERE A.groupid IN('521','637'); ELSEIF @Machine = 'bdr server' THEN SELECT COUNT(*) AS 'Total BDR Servers' FROM agentcomputers AS A WHERE A.groupid =521; ELSEIF @Machine = 'non-bdr server' THEN SELECT COUNT(*) AS 'Total Non-BDR Servers' FROM agentcomputers AS A WHERE A.groupid =637; ELSEIF @Machine = 'workstation' THEN SELECT COUNT(*) AS 'Total Workstations' FROM agentcomputers AS A WHERE A.groupid =638; ELSEIF @Machine = 'laptop' THEN SELECT COUNT(*) AS 'Total Laptops' FROM agentcomputers AS A WHERE A.groupid =650; ELSEIF @Machine = 'monitored computer' THEN SELECT COUNT(*) AS 'Total Monitored Computers' FROM agentcomputers AS A WHERE A.groupid =1; ELSEIF @Machine = 'new computer' THEN SELECT COUNT(*) AS 'Total New Computers' FROM agentcomputers AS A WHERE A.groupid =648; ELSEIF @Machine = 'managed service' THEN SELECT COUNT(*) AS 'Total Managed Services' FROM agentcomputers AS A WHERE A.groupid =522; ELSEIF @Machine = 'allmachines' THEN SELECT COUNT(*) AS 'Total Machines' FROM agentcomputers AS A WHERE A.groupid IN('1','521','522','637','638','648','650'); /* THESE IF STATEMENTS RETURN THE COUNTS FOR THE MOBIL DEVICES */ ELSEIF @Machine = 'phone' THEN SELECT COUNT(*) AS 'Total Mobil Phones' FROM mobiledevices AS M INNER JOIN mobilemastergroups AS MM ON MM.groupID = M.deviceType WHERE M.deviceType=2; ELSEIF @Machine = 'tablet' THEN SELECT COUNT(*) AS 'Total Mobil Tablets' FROM mobiledevices AS M INNER JOIN mobilemastergroups AS MM ON MM.groupID = M.deviceType WHERE M.deviceType=3; ELSEIF @Machine = 'ios' THEN SELECT COUNT(*) AS 'Total Mobil iOS Devices' FROM mobiledevices AS M INNER JOIN mobilemastergroups AS MM ON MM.groupID = M.deviceType WHERE M.deviceType=4; ELSEIF @Machine = 'android' THEN SELECT COUNT(*) AS 'Total Mobil Android Devices' FROM mobiledevices AS M INNER JOIN mobilemastergroups AS MM ON MM.groupID = M.deviceType WHERE M.deviceType=5; ELSEIF @Machine = 'allmobil' THEN SELECT COUNT(*) AS 'Total Mobil Devices' FROM mobiledevices AS M; END I

I have the developers edition of SQL SERVER 2008r2. The only choices showing up in my BIDS project types is Business Intellegence. Does anyone know how to get VB.net or Visual C# choices to show up?? :confused:

After running a report in SSRS 2005 I am trying to export it into excel and I am getting an error "Index is out of range. Must be non-negative and less than the size of the collection. Parameter name:index" When I remove the "Page break at start" or the "Page break at end" grouping property, it fixes the problem, but I need this property on because that is how the report gets seperated onto seperate Excel worksheets by the groups I have set up. Any suggestions??? :doh:

Stated

As I ststed I am already reading 5 books and studying as hard as I can. I have no free time. I am only trying to get study sheets from a professional not take short cuts. I guess you suggest I stay in this underpaid paid possition that I can't even pay my rent with for another five years while I set up projects on my own to explore.

Have been working with and studying Microsoft SQL Server, SSRS and SSIS. I am reading about six books at the same time plus using them at work. I am close to interviewing for a Mid level job. I wish to hire a good T-SQL developer to put together study sheets which will include snipets of real world code in the major areas of TSQL such as stored procedures, funtions, loops, CASE and much more. I need to go in strong on the coding side but don't get much experience coding at work. Reading the books helps but its not like the real world. ANY Suggestions. :-D

Will this work if you need to join several tables from different databases which would mean different data sources?

Does anyone know the best way to join fields from several tables onto a single SSRS report for SSRS 2005 and SSRS 2008? :doh:

--------------------------------------------------------------------------------

I have spent weeks on a SSRS reporting services 2005 report. I set up a subscription to email automatically. Managers with Outlook 2003 can view the report perfectly. Managers with Outlook 2007 get a truncated and garbled email report from Report Manager. Has anyone come across this before???

I am running a report in SQL SERVER 2008 that I inherited from a past co-worker in another state. The code is accessing two different servers, but SQl Server management studio seems to be only recongzing only one server/database/table at a time. Is it possible to access two servers while running one report in SSMS/ Below is the server names and databases:

ELMO.[HBS Patient Information].dbo.ECS_Plan_Information E (NoLock)
(Getting invalid oblect name when I hover over this)

[167.211.200.214].Billing03990.dbo.pclaim P

Ed English

I exported my results data into Excel 2007 (784,000 records on one column) so that I can Import it back into SQL Server 2008 as a table. When the data comes back from Excel it comes in as a float which I convert to a nvarchar(255) or varchar(50), but any record that has a letter in it shows up as NULL in SQL Server 2008. I have tried all combinations. Can anyone suggest anything? Ed

I am a student of SQL Server. I have been using the SQL Server 2008R2 Enterprise trial version. The trial version has now expired. I just purchased the SQL Server 2008R2 developers version for $50.00. My question is should I install the Developers version over the trial version or try and delete the trial version first before installing the Developers version. :-D

Do you mean to put all the code I have written into a sub query ????

Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help? /* This template is an aging report for private pay open balances*/ USE AR SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR, --CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE, --CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE, --DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE, CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [0-30 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [31-60 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [61-90 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [91-180 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [181-360 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [OVER 360 DAYS] FROM dbo.tblAROPEN AS ARO INNER JOIN dbo.tblCLMHDR AS CLM ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH WHERE ARO.INS_CO_AR IN ('0','000') AND ARO.TOTALAMOUNT >0 GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH --ORDER BY ARO.TOTALAMOUNT DESC