Saving Output From Multiple Queries

Sep 15, 2005

Greetings,

I have what seems a simple problem....
I want to save the output from the following queries in 1 file in
'SQL Query Analyser', but I can only save each 'grid' separately.
I have about 30 queries in total. 'Messages' tells me the number of
rows affected but I need the data.

So can I save all the grids together at the same time in 1 file ?

Thanks
Mike

PRINT 'HEAT 3510'

SELECT f.persfirstname AS First_Name,
f.perslastname AS Last_Name,
a.rsrchqnumber AS Personal_nr,
b.asgtassignmentid AS Contract_nr,
ISNULL(c.tishcode,'') AS TS_Id,
ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,
d.tpitdaydate AS Payroll_Date,
c.tishactualenddate AS TS_end_date
FROM resources a
INNER join assignments b ON b.asgtrsrcguid = a.rsrcguid
INNER join timesheets c ON c.tishasgtguid = b.asgtguid
INNER join users e ON e.userguid = a.rsrcuserguid
INNER join persons f ON e.userpersguid = f.persguid
LEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguid
WHERE a.rsrchqnumber = 80000147
and b.asgtassignmentid = 0000001234
and c.tishactualenddate > '20050614'
and (d.tpitdaydate > '20050614'
or d.tpitdaydate IS null)
order by d.tpitdaydate

SELECT f.persfirstname AS First_Name,
f.perslastname AS Last_Name,
a.rsrchqnumber AS Personal_nr,
b.asgtassignmentid AS Contract_nr,
ISNULL(c.tishcode,'') AS TS_Id,
c.tishactualenddate AS TS_end_date,
g.thisdaydate AS TSH_date
FROM resources a
INNER join assignments b ON b.asgtrsrcguid = a.rsrcguid
INNER join timesheets c ON c.tishasgtguid = b.asgtguid
INNER join users e ON e.userguid = a.rsrcuserguid
INNER join persons f ON e.userpersguid = f.persguid
INNER join timesheethistory g ON g.thistishguid = c.tishguid
WHERE a.rsrchqnumber = 80000147
and b.asgtassignmentid = 0000001234
and c.tishactualenddate > '20050619'
order by g.thisdaydate

PRINT 'HEAT 3213'

View 3 Replies


ADVERTISEMENT

Saving Queries

Jun 20, 2007

I just started using Visual Studio 2005. How do you actually save your queries to use again? Also, is there a way to export results in a user friendly format?



thanks!

View 6 Replies View Related

SQL Express, Saving Queries?

Jan 30, 2007

I opened up a new project using Visual Basic Express, and selected the SQL object to start creating a database. Well I finished putting all of my tables together and started making the queries but for some reason I can't save them like when I finished making the tables. When I finished making the tables I could just click the disk icon and name the table and it would appear in a drill down menu on the left. However when I do that with the queries it just says it's saving in the lower left corner but it doesn't do anything?

I am new to VB & SQL so any help is appreciated. Thank you.

View 7 Replies View Related

Saving Output To A Text File

Nov 14, 2000

Do anyone knows the syntax for saving the results from a query to a text file
in query analyzer?

Thanks!
Vic

View 1 Replies View Related

Saving The Output From A Stored Procedure

May 7, 2008

Hi,

I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.

Is this possible ?

Sean

View 2 Replies View Related

Trouble Saving XML Output From SQL Task

Sep 7, 2007

I am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task.
Here is the stored procedure
Create Procedure [dbo].[HRGCSpace]
(@HRGSpace nvarchar(max) = ''Output)
as
Begin
Set NOCOUNT On
set @HRGSpace = (select
CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id',
WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id',
FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id',
SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id',
StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id'
from HRGCvCorrigoUnits
order by BuildingName, SpaceName
For XML Path ('Space'), Root('HRGSpaces'))
End
--------------------------------------
Here is the execute from the SQL Task
DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output

If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0
, then use in the SQLTask
General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False,
Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0
the Progress is
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

If I change it so that ResultSet = XML, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."

Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with
"[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!



View 4 Replies View Related

Saving Scripted Output From Enterprise Manager

Aug 14, 2002

Does anyone know where to declare the target directory for scripted output from Enterprise Manager?

Every time I get EM to script Tables I want the output not to go to c:mssql7inn . . but somewhere else. Trouble is I can't find where to set this variable. It won't work in a shortcut - I've tried that, and I can't find an ini file either.

Seems like a simple problem to fix if you just know where to look !

View 1 Replies View Related

SQL Server 2008 :: Create All Procedures Located In A Folder One By One And Saving Output File

May 15, 2015

I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?

View 9 Replies View Related

Redirect The Queries Output

Sep 7, 2004

Hello friends

i m using Sqlserver 2000 and i wish to know that ,
Can i redirect the output of the query into a text/xls/html file !
If possible then please help me !!!!!

Thanks

View 7 Replies View Related

Output Of Queries To A Text File

Oct 27, 1999

Hi all,

How can get the output of a quries / stored procedures in a text file and to append the text file each time quries / stored procedures run

Thanks all

View 1 Replies View Related

SQL 2012 :: Merge Two Queries To Have Only 1 Output?

Nov 2, 2015

We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.

Query 1:

set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))

[code].....

Again, the only difference is the Table the info is coming from...

View 3 Replies View Related

Saving Tables That Are Generated By Queries As HTML File Or Sub-tables

Oct 17, 2006

I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.

The table has columns like Commodity, Unit, Quantity, Value, Month, Country

A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"

The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.

It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.

I wanted to know if there is an alternate way to pull the data from server ?

I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??

OR

Can I write a script that creates a html files for each table for all input combinations save them ?

OR

Is there exists any other solution ?

View 1 Replies View Related

Saving Data Into A Table & Multiple Processors

Mar 10, 2000

Hello,

Yesterday I was looking to the processor usage in the Task Manager of Windows NT when a script of mine was running. The script was an InfoPump Script; which is a tool from the DecisionBase suite from CA (was previously owned by Platinum). This script contains SQL statements that select data from several tables and stores the result into another table. The SQL code used for this looks fine to me.
The query was running on a Compaq Proliant 5500 with 4 500 Mhz Xeon processors, 1 GB RAM, NT Server 4, SP 5, RAID 5. The SQL Server is configured to use all resources and SQL has normal priority on NT.
When the select part was running al four processors were used for about 75% and when the store happens only 1 processor is used for 100%.
Why is the store not spread over all four processors? It only uses one processor and it seems to be a bottleneck.

Stef

View 2 Replies View Related

Saving Multiple Reports From Reporting Services

Oct 31, 2007

Hi everyone, I'm very new to SQL and SQL Reporting Services.
However I've been given a task.

How do I save multiple reporting services reports onto a predetermined location on a users local pc? Are there tools/applications that can be used to push files onto the users local pc?

The application uses .NET but they've created reports using SQL Reporting Services. The user will select which report they want (out of 2). My task is to figure out how can we save those reports that they have selected onto their pc?

Thanks in advance

View 1 Replies View Related

SQL Server 2014 :: Similar Queries Produce Different Output?

Sep 22, 2015

create table #t1 (id int)
create table #t2 (id int)

insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

insert into #t2 values (1)
insert into #t2 values (2)

Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id and b.id is null

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id where b.id is null

View 3 Replies View Related

Reporting Services :: 2014 Enterprise - Multiple Tabs When Saving To Excel?

Oct 28, 2015

We are running 2014 enterprise.  Our users love to see related report sections saved in separate tabs of the same spreadsheet.  Is there a way to control how ssrs will save a report to excel when it comes  to tabs?  

Are subreports a/the way (and only way) to do this? 

By sections I mean they might have a grid, then another grid, then a graph then another graph and so on. 

What property controls the tab name?  What if they want to combine 2 or more sections into one tab?

View 7 Replies View Related

Multiple Queries

Nov 15, 2007

Hi
I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute.
I need to join them up similar to the following but when I do it take forever for them to execute.
select *
from table
where column not in (select column from table2)
It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run.
 Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database?
 Any help would be appreciated.
 Thanks,
Frankie

View 4 Replies View Related

Multiple Queries In The Same Sub...

Aug 11, 2004

*************************************************************
<code> tags added by moderator. Please use <code> and
</code> tags to bracket comments to make code readable.

*************************************************************

I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:

'SQL Insert data'
Dim Conn As SqlConnection
Dim Rdr As SqlDataReader
Dim strConn As String = AppSettings("doConnect")
Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')"
Conn = New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Cmd.ExecuteNonQuery()

'SQL Get Last ID
Dim ConnGet As SqlConnection
Dim RdrGet As SqlDataReader
Dim strConnGet As String = AppSettings("doConnect")
Dim strSQLGet As String = "select MAX(id) as maxid from USERS"
ConnGet = New SqlConnection(strConn)
Dim CmdGet As New SqlCommand(strSQL, Conn)
ConnGet.Open()
If CmdGet.ExecuteScalar() <> 0 Then
RdrGet = CmdGet.ExecuteReader()
While RdrGet.Read()
Session("theID") = RdrGet("name")
End While
Else
Session("theID") = 0
End If

View 1 Replies View Related

Multiple Queries

Jul 23, 2001

Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.

SELECT COUNT(*) AS [NUMBER OF SERVERS]
FROM DBO.OS
WHERE (OSTYPE LIKE '%SER%')

The other statement is

SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS]
FROM DBO.OS
WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')

If any one has any idea's please reply or email me directly. I would greatly appreciate it.

View 1 Replies View Related

Multiple Queries

Jul 23, 2005

I'm writing an ASP page for a project and it requires multiple queries.However, I'm trying to combine multiple SELECT statements, but can'tfigure out a way that actually works.Basically, here are the SELECT statements I want to combine:SELECT * FROM schoolrequest WHERE SLid=10SELECT SLlastName FROM academicofficeWHERE schoolrequest.SLsendToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLbillToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLrequestorId=academicoffice.AOidSELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10AND diaryentry.DEdeletedDate IS NULLThe academicoffice table just contains basic contact info, but needs tobe used 3 times in the query to get specific contact info for 3different contacts(SLsendToId, SLbillToId, SLrequestorId)The diaryentry table contains info entered in by students. TheDEkeyValue is actually the primary id of the schoolrequest table(SLid).Any ideas?Thanks,M

View 4 Replies View Related

TableAdapters With Multiple Queries

Jul 18, 2007

Hello everyone...
I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server.
 Any advice would be greatly appreciated.

View 4 Replies View Related

Multiple Join Queries?

Mar 3, 2008

I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data.  I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.  

View 4 Replies View Related

Help: Need To Combine Multiple IF Queries

Apr 14, 2008

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))

View 12 Replies View Related

Multiple Queries For One Objective

Dec 25, 2014

If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?

I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.

View 8 Replies View Related

Help: Need To Combine Multiple IF Queries

Apr 14, 2008

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))

View 15 Replies View Related

Help Condensing Multiple Queries To One

Mar 30, 2008

Hi,

I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?

I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.

Thanks very much for any help!!
mike123


CREATE PROCEDURE [dbo].[select_mailbox_Count]
(
@userID int
)
AS SET NOCOUNT ON

SELECT count(*) as totalInbox,


(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash



FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0

View 13 Replies View Related

Planning Multiple Queries

Dec 2, 2005

I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim

View 2 Replies View Related

Multiple Sub Queries Not Working Together

Dec 31, 2007



I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C.
If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work.
Here is what the query looks like that I'm trying to run.
Thanks in advance for any help.



select distinct i.personid, i.lastname + ',' + ' ' + i.firstname as student, p.studentnumber, e.grade,

t.name as Term, gs.score, c.name as course, sec.teacherdisplay

from gradingscore gs

JOIN [identity] i on i.personid = gs.personid

JOIN person p ON p.personid = i.personid

JOIN enrollment e on e.personid = p.personid

JOIN term t on t.termid = gs.termid

JOIN v_TermGPA tg on tg.personid = gs.personid and tg.calendarid = gs.calendarid

JOIN v_GpaTermCalc tc ON tc.personid = gs.personid and tc.calendarid = gs.calendarid

JOIN section sec on sec.sectionid = gs.sectionid

JOIN course c on c.courseid = sec.courseid

where gs.calendarid = 20 and t.name = '2nd 6wk' and tc.gpa between 3.0 and 3.49 and

e.enddate is null

--Sub Query to eleminate students with any D's in scores

and p.personid not in

(select distinct gs1.personid from gradingscore gs1

join Term t1 ON t1.termid = gs1.termid

join v_TermGPA tg1 ON tg1.personid = gs1.personid

where gs1.calendarid = 20 AND t1.name ='2nd 6wk'and

tc.gpa >=3.0 AND tc.gpa <= 3.49

and gs1.score IN('D+','D', 'D-')

--Sub Query to eliminate students with more than 1 C of any type in scores.

and gs.personid not in

(select distinct gs2.personid from gradingscore gs2

join Term t2 ON t2.termid = gs2.termid

join v_TermGPA tg2 ON tg2.personid = gs2.personid

where gs2.calendarid = 20 AND t2.name ='2nd 6wk'and tc.gpa >=3.0 AND tc.gpa <= 3.49

and gs2.score IN('C+','C', 'C-')

group by gs2.personid

HAVING count(gs2.score)<=1))

order by i.[student]

View 6 Replies View Related

Making Multiple Queries

Feb 26, 2008

Hi,

I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?

The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?

The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!



string connectionString = ConfigurationManager.ConnectionStrings["ServetteConnectionString"].ConnectionString;

string sQuery = "SELECT * FROM Aktivitetsgrupper where aktivitetsid = " + Request["AktivitetsId"];



OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);

oOleDbConnection.Open();

OleDbCommand command = new OleDbCommand(sQuery, oOleDbConnection);

OleDbDataReader reader = command.ExecuteReader();

int test = 12;

while (reader.Read())

{



rblAktivitetsgrupper.Items.Add(new ListItem(String.Format("{0}, platser", reader["aktivitetsGruppNamn"]), reader["aktivitetsGruppID"].ToString()));



}

View 4 Replies View Related

Insert With Multiple Sub Queries

May 27, 2008

In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.


INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)

View 1 Replies View Related

Automating A Query And The Saving Saving Of Subsequent Results

Dec 13, 2007

Hi Guys,

I am trying to automate a basic task using SQL Server 2005 Express.

Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.

All ideas gratefully received!

View 1 Replies View Related

Manipulating Multiple Count(*) Queries

Dec 15, 2006

Hi,
I'm having problems manipulating the results from multiple count(*) queries.

I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.

I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS

I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3

My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)

... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)

... and again the result was "0"

Please could someone help me!!

Ian

View 2 Replies View Related

Consolidating Multiple Queries Into One Table

Jan 4, 2015

I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?

I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.

Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.

Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.

Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved