Can Anyone Show Me How To Combine These Two SQL Queries Into One
Jan 29, 2004
Hello-
i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.
here is the 1st query:
SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"
Here is the 2nd query:
SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"
I know there has to be someway to combine these into 1. The issue I have is they are in different databases.
Hello, I have these variables on my page: userid = "10101" RequestHost = "example.com" What would be the best way performace wise to first check if the userid 10101 exists in my sql server db. If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table. If these both exist I would then like to query some additional data. I was hoping its possible to combine this into one query somehow. I dont think this is the best solution: sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'" Conn.Open() dr = sqlcommand.ExecuteReader if dr.hasrows then sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'" dr2 = sqlcommand2.ExecuteReader if dr2.hasrows then sqlcommand3.CommandText = 'Select Additional Data dr3 = sqlcommand3.ExecuteReader 'read values conn.close else conn.close 'do something end if else conn.close 'do something end if Thanks Very Much!
I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query
Query 1
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, dbo.Job.ChangeDate FROM dbo.Job LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid WHERE dbo.job.CompanyJobId = 3505048 ORDER BY dbo.Job.CompanyJobId
Query 2
SELECT case dbo.SourceType.CompanySourceTypeId when 'PR' then SUM(dbo.ProductionEvent.Quantity) end AS Ttl_Qty FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR' GROUP BY dbo.SourceType.CompanySourceTypeId
I have tried this but it doe not work:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, dbo.Job.ChangeDate, Ttl_Qty FROM dbo.Job LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid WHERE dbo.job.CompanyJobId = 3505048 and where Ttl_Qty =
(SELECT case dbo.SourceType.CompanySourceTypeId when 'PR' then SUM(dbo.ProductionEvent.Quantity) end AS Ttl_Qty FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR' GROUP BY dbo.SourceType.CompanySourceTypeId)
I wonder if anyone can help. All I am trying to do is combine three SQL SELECT queries into one. They are:
SELECT COUNT(ISNULL(Result, 0)) AS Win FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Win'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Lose FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Lose'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Draw FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Draw'AND ([MatchType] = @MatchType)
As you can see they are all doing pretty much the same thing. I have experimented by using GROUP and HAVING but end up with no results. Sorry if this is obvious but I am new to SQL!
I have a table that has two dates in it, a date opened and a dateclosed. I would like to create one query to give me the number ofrecords that have been opened each month plus, and this is the hardpart the number of those records that have been closed each month. Ican get the result with two seperate queries but have been unable toget it combined into one query with three values for each month, i.e.,the month, the number opened and the number of those that were openedin the month that have been subsequently closed.Here's my two queries. If anyone can help I'd appreciate.SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableWHERE (DateClosed IS NOT NULL)GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableGROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)TIABill
Hi,I have 2 queries that I need to join. I have the following tables:attendancelog :headeridreportmonthattlogstuds:headeridsidclass:sidclassstatusyearcodelogdatecdatemidThe result must return all the classes that appear in query2 but notin query1.I am not sure how to join the 2 queries.Help will be appreciated :-)ThanksQUERY1select sid fromattlogstuds studsinner joinattendancelog attlogon studs.headerid=attlog.headeridwhere reportmonth=10query2-- students learning excl. studs left before 1th oct.select class.SID from classleft outer JOIN ( select * from class where yearcode=26 and status=6and ( logdate <'20041001' or CDate< '20041001' )) c6 ON c6.sid = class.sid and c6.mid=class.midand c6.cdate >= class.cdatewhere class.yearcode=26 and class.status in (3,5) andclass.cdate<'20041101' and c6.sid is null
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id where o1.status = 'Open -> Assigned' and o2.status = 'Assigned -> Responded' and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query select org.name, NULL AS correct, count(order) AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total org1 324 NULL org1 NULL 423
How can I combine them, or maybe doing it a better way?
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */ USE Database
/* these params are on the page in drop down boxes*/ DECLARE @ProductID int; DECLARE @BuildID int; DECLARE @StatusID int;
/* static params for this sample */ SET @ProductID = -1; SET @BuildID = -2 SET @StatusID = -3
/* the query that will build the datagrid. currently this runs and produces three different result sets. How do I combine these statements so they produce a single set of results? */
IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal.
The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.
Here's the first query ... select RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL(vpi.FirstName,''))) Employee, convert(varchar,vpi.FromEffectiveDate,101) PositionStart, convert(varchar,vpi.ToEffectiveDate,101) PositionChange, convert(varchar,vpi.PositionStartDate,101) PositionStartDate, vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescription from vhrl_positioninfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode
Here's the second query ... select [Age] = convert(varchar, [Years]) + ' Years ' + convert(varchar, [Months]) + ' Months ' + convert(varchar, [Days]) + ' Days', * from ( select [Years] = case when BirthDayThisYear <= Today then datediff(year, BirthYearStart, CurrYearStart) else datediff(year, BirthYearStart, CurrYearStart) - 1 end, [Months]= case when BirthDayThisYear <= Today then datediff(month, BirthDayThisYear, Today) else datediff(month, BirthDayThisYear, Today) + 12 end, [Days]= case when BirthDayThisMonth <= Today then datediff(day, BirthDayThisMonth, Today) else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today) end, Birth = convert(varchar(10) ,Birth, 121), Today = convert(varchar(10), Today, 121) from ( select BirthDayThisYear = case when day(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth) then dateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) else dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth) end, BirthDayThisMonth = case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth) then dateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) else dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth) end, * from ( select BirthYearStart = dateadd(year, datediff(year, 0, Birth), 0), CurrYearStart = dateadd(year, datediff(year, 0, Today), 0), BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0), CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0), * from ( select birth = convert(datetime, fromeffectivedate) , Today = case when convert(datetime, toeffectivedate) = '3000-01-01' THEN convert(datetime, convert(int,getdate())) else vpi.toeffectivedate end
from vHRL_PositionInfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode
) aaaa ) aaa ) aa )a
Here's the sample data ... vpi table ... LastName FirstName FromEffectDate ToEffectDate PosStartDate PosReason PosCode Doe John 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack Smith Tom 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC
This is what the results should look like ... John, Doe 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack Pack Line Operator 6 Years 11 Months 16 Days John, Doe 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC Descaler Operator 6 Years 6 Months 19 Days
I know the date calculation piece adds 5 additional fields to the end, but they are not needed for the final report. Any help would be greatly appreciated! Thank you! Jena
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) < 6 group by investor, full_date, month_end_summary, category, loan_count order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) =0 order by investor, full_date
Can they be combined into one query to produce one result table??
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ComboBox1.DataSource = MyDB_DevDataSet.DataSetName. Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True" Dim sql As String = "SELECT * FROM CompanyMain"
Try this script to see what queries are taking over a second.To get some real output, you need a long-running query. Here's one(estimated to take over an hour):PRINT GETDATE()select count_big(*)from sys.objects s1, sys.objects s2, sys.objects s3,sys.objects s4, sys.objects s5PRINT GETDATE()Output is:session_id elapsed task_alloc task_dealloc runningSqlText FullSqlTextquery_plan51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,sys.objects s3, sys.objects s4, sys.objects s5 SQL PlanClicking on SQL opens the full SQL batch as a .txt file, including the PRINTstatementsClicking on Plan allows you to see the .sqlplan file in MSSMS========Title: Using a VB Script to show long-running queries, complete with queryplans.Today (July 14th), I found a query running for hours on a development box.Rather than kill it, I decided to use this opportunity to develop a scriptto show long-running queries, so I could see what was going on. (ReferenceRoy Carlson's article for the idea.)This script generates a web page which shows long-running queries with thecurrently-executing SQL command, full SQL text, and .sqlplan files. The fullSQL query text and the sqlplan file are output to files in your tempdirectory. If you have SQL Management Studio installed on the localcomputer, you should be able to open the .sqlplan to see the query plan ofthe whole batch for any statement.'LongestRunningQueries.vbs'By Aaron W. West, 7/14/2006'Idea from:'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp'Reference: Troubleshooting Performance Problems in SQL Server 2005'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxSub Main()Const MinimumMilliseconds = 1000Dim srvnameIf WScript.Arguments.count 0 Thensrvname = WScript.Arguments(0)Elsesrvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)If srvname = "" ThenMsgBox("Cancelled")Exit SubEnd IfEnd IfConst adOpenStatic = 3Const adLockOptimistic = 3Dim i' making the connection to your sql server' change yourservername to match your serverSet conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' this is using the trusted connection if you use sql logins' add username and password, but I would then encrypt this' using Windows Script Encoderconn.Open "Provider=SQLOLEDB;Data Source=" & _srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"' The query goes heresql = "select " & vbCrLf & _" t1.session_id, " & vbCrLf & _" t2.total_elapsed_time AS elapsed, " & vbCrLf & _" -- t1.request_id, " & vbCrLf & _" t1.task_alloc, " & vbCrLf & _" t1.task_dealloc, " & vbCrLf & _" -- t2.sql_handle, " & vbCrLf & _" -- t2.statement_start_offset, " & vbCrLf & _" -- t2.statement_end_offset, " & vbCrLf & _" -- t2.plan_handle," & vbCrLf & _" substring(sql.text, statement_start_offset/2, " & vbCrLf & _" CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _" ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _" END) AS runningSqlText," & vbCrLf & _" sql.text as FullSqlText," & vbCrLf & _" p.query_plan " & vbCrLf & _"from (Select session_id, " & vbCrLf & _" request_id, " & vbCrLf & _" sum(internal_objects_alloc_page_count) as task_alloc, " &vbCrLf & _" sum (internal_objects_dealloc_page_count) as task_dealloc " &vbCrLf & _" from sys.dm_db_task_space_usage " & vbCrLf & _" group by session_id, request_id) as t1, " & vbCrLf & _" sys.dm_exec_requests as t2 " & vbCrLf & _"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _"where t1.session_id = t2.session_id and " & vbCrLf & _" (t1.request_id = t2.request_id) " & vbCrLf & _" AND total_elapsed_time " & MinimumMilliseconds & vbCrLf & _"order by t1.task_alloc DESC"rs.Open sql, conn, adOpenStatic, adLockOptimistic'rs.MoveFirstpg = "<html><head><title>Top consuming queries</title></head>" & vbCrLfpg = pg & "<table border=1>" & vbCrLfIf Not rs.EOF Thenpg = pg & "<tr>"For Each col In rs.Fieldspg = pg & "<th>" & col.Name & "</th>"c = c + 1Nextpg = pg & "</tr>"Elsepg = pg & "Query returned no results"End Ifcols = cdim filenamedim WshShellset WshShell = WScript.CreateObject("WScript.Shell")Set WshSysEnv = WshShell.Environment("PROCESS")temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & ""filename = temp & filenameDim fso, fSet fso = CreateObject("Scripting.FileSystemObject")i = 0Dim cDo Until rs.EOFi = i + 1pg = pg & "<tr>"For c = 0 to cols-3pg = pg & "<td>" & RTrim(rs(c)) & "</td>"Next'Output FullSQL and Plan Text to files, provide links to themfilename = "topplan-sql" & i & ".txt"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-2)f.Closepg = pg & "<td><a href=""" & filename & """>SQL</a>"filename = "topplan" & i & ".sqlplan"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-1)f.Closepg = pg & "<td><a href=""" & filename & """>Plan</a>"'We could open them immediately, eg:'WshShell.run temp & filenamers.MoveNextpg = pg & "</tr>"Looppg = pg & "</table>"filename = temp & "topplans.htm"Set f = fso.CreateTextFile(filename, True, True)f.Write pgf.CloseDim oIESET oIE = CreateObject("InternetExplorer.Application")oIE.Visible = TrueoIE.Navigate(filename)'Alternate method:'WshShell.run filename' cleaning uprs.Closeconn.CloseSet WshShell = NothingSet oIE = NothingSet f = NothingEnd SubMain
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob
I have a table with multiple rows with the same ID. a) How do I combine all columns into one row with the same ID? b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls? Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
Combine 3 Databases, Not tables. Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database. I've discovered I can use something like this to get to each DB in a query... 1 USE [database1]2 SELECT table1.Name, table1.Location, table1.Date, table2.Blog3 FROM table2 INNER JOIN4 table1 ON table2.ID = table1.ID5 ORDER BY table1.Date and then just repeat for database2 and database3. Ok, fine, rah rah. My question is how do I "merge" all of these into 1. No data on each db will be identical, at all, ever so that is not a concern. I just need all the data from db1, 2 and 3 into masterDB. Ideas? Direction?
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls. Here is the stored Proc: SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*) FROM CALL_LOG_MASTER WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL) GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) It returns a table: MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10 1 2 15 My question is: is it possible to combine the Month and Date column into one column. e.g. Date Total Number of Calls==== ==============1/1 101/2 15 Please Help, Thanks in advance :)
I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
-- Create the CDONTS NewMail object. EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified IF @Body IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Body', @Body IF @hr <> 0 GOTO ObjectError END
IF @Cc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc IF @hr <> 0 GOTO ObjectError END
IF @Bcc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc IF @hr <> 0 GOTO ObjectError END
IF @HTMLFormat <> 0 BEGIN EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0 IF @hr <> 0 GOTO ObjectError END
-- Loop through the ; delimited files to attach CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0 BEGIN SELECT @StrEnd = CASE charindex(';', @Attachments) WHEN 0 THEN len(@Attachments) ELSE charindex(';', @Attachments) - 1 END SELECT @Attachment = substring(@Attachments, 1, @StrEnd) SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send. DELETE #FileExists INSERT #FileExists EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1) BEGIN RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment) RETURN 1 END
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
I have tried the following union query which combines the two RS's then sorts the data: SELECT Top 2 ID, Song, Hits FROM Table1 UNION SELECT Top 2 ID, Song, Hits from Table2 Which would return the first two records from each then sort them like this: 2 - tb1SONG 2 - 1459 1 - tb2SONG 1 - 412 1 - tb1SONG 1 - 356 2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this: 3 - tb2SONG 3 - 2035 2 - tb1SONG 2 - 1459 4 - tb1SONG 4 - 965 5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated. Thanks
SELECT bms_id,email_address,COUNT(*) INTO #temp FROM emp_db WHERE email_address IS NOT NULL GROUP BY bms_id,email_address ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*) FROM #TEMP GROUP BY bms_id ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
