I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
now i need to create a proc that runs through DatabaseA gets all the tables, creates DatabaseC and insert all the Tables,Procedures, and data when the user creates a new company in our software interface.
if you could just help me cycle through the objects i can manage the rest.
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?
Code Block // Set the custom properties of the source. srcDesignTime.SetComponentProperty("AccessMode", 2); srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");
ok, i'm building a page to display a list of courses, a user rating and 'last visited' date.
I have 3 tables - course (a list of all courses) review (a list of all ratings) visit (user visits to each course)
I've put together an SQL statment that returns everything i need, however its not quite right. SELECT course.courseID, course.courseName, course.courseURL, avg(review.fldRating) AS fldAverage, visit.visitDate FROM course
LEFT OUTER JOIN review ON course.courseId = review.fldcourseId
LEFT OUTER JOIN visit ON course.courseId = visit.courseId and visit.userId = 2
GROUP BY course.courseId, course.courseName, course.courseURL, visit.visitDate ORDER BY course.courseId, visit.visitDate DESC
The problem lies with the fact that each time a user enters a course a new record is inserted into the visit table - so the visit table will show how many times a user has entered a course and on which dates.
because the user has entered course 1 twice, the list is now showing 2 course1's - how can I change the statemtent to only select the most recent user visit, but still keep the complete list of courses?
I'm a bit of an SQL novice, so appologies if I've not explained this very well, Thanks in advance,
Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column
for example:
table 1
Alan Andrews 1 main st 07465 John Andrews 1 main st 07465 Erick Andrews 1 main st 07465
I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.
So is there a way of doing this in SQL?
It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only. Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, sum(A.pd_clm_amt) clm_amt, sum(A.pd_med_amt) med_amt, sum(A.pd_exp_amt) exp_amt, A.lst_updt_timestamp from bal_load_stg A group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp having A.lst_updt_timestamp in ( (select max(lst_updt_timestamp) from bal_load_stg B where A.cust_alias_nm_id = B.cust_alias_nm_id and A.carrier_cd_id = B.carrier_cd_id and A.acct_nbr_id = B.acct_nbr_id))
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj 1 good 2 so so 3 bad
Noun table looks like e.g.
ID Noun 1 apple 2 orange 3 banana
And the result set would look like
This is good apple. This is so so apple. This is bad apple. This is good orange. This is so so orange. This is bad orange. This is good banana. This is so so banana. This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides. Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1) My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections. I'm hoping someone has a suggestion on how I might avoid the users having this problem.
I am using SQL 7 with an MS Access 2000 MDB front end, using bound formswith ODBC linked tables. In one form, the user needs to be able to check abox to select one or more records. This is accomplished with a local tablecontaining two fields: the primary key value of the SQL table and a booleanfield used for the check box.Since the local table used to contain the boolean field is local to the MDBfile, the result is a heterogeneous join in the underlying form query, whichdegrades performance. I would like to have the entire query be based on backend SQL data. However, each user needs to be able to make a unique set ofselections, without other users' selections affecting theirs.An idea I have is to port the selections table to the back end with anadditional field for machine name; create a view of the main table joined tothe selections table; link the view to the front end; and base the form onthe SQL: "Select * From MyView Where MachineName='MyMachine'".However, I wonder if there's a better approach. Any ideas would beappreciated.Thanks,Neil
I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...
Don't think this can be done, but a user request, so thought I should ask anyway...
I have report with 11 filters (yes, I know thats overkill, but we aren't using analysis services) and the user has asked, if he wants to run it with the same selection criteria every month, can he do that without having to re-select them all?
We need to store land title information about properties in variousAustralian states, but each state maintains it's own land titleregistry and use different columns (well actually differentcombinations of the same columns). For example:Victoria store:TorrensUnitTorrensVolumeTorrensFolioQueensland store:TorrensCountyTorrensLotTorrensPlanTorrensParishTorrensUnitTorrensVolumeTorrensTitleRefThere are 11 different columns and they are used in 8 differentcombinations depending on the state.Since we need to store information about land in different states I seetwo possible solutions:1. A sparse table containing the 11 columns with a CHECK constraint toenforce the valid combinations.2. A table for each state containing only the columns relevant to thestate with a foreign key relationship to the table containing thecommon columns.I'm not sure if the data type and length is consistent between statesyet (waiting to find this out) but assuming that it is which of theseapproaches is going to be the most rigorous? I'm leaning towards (2)but I don't like the feel of a table per state.
Hello All,I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?Any help appreciated.MoP.S. stored procedure look like this:------------------- @centreid int, @startdate varchar(20), @enddate varchar(20) ASSELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@startdate) = 0 OR CentreID = @CentreID)AND (DataLength(@startdate) = 0 OR datecreated >= @startdate)AND (DataLength(@enddate) = 0 OR datecreated <= @enddate)
Hello all, I have an odd requirement. I have a column with a systemgenerated username that is a 6 character, alphanumeric, field. Theseusernames are randomly generated by code. I need to create a storedprocedure that will return all combinations that are not alreadybeingused. Maybe the result of still trying to wake-up from a longweekend.But, I cannot think of an easy way to do this.Any help would be greatly appreciated.Best regards,rbr
I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.
For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.
What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.
If I need to submit this in another forum please let me know.
Anyone know if it is possible to limit the number of selections in a multi value parameter? Eg: There are 50 values in the drop down combo, but I want the user to be able to select a maximum of 10?
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs ( @OrderList varchar(500) ) RETURNS @ParsedList table ( OrderID int ) AS BEGIN DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID <> '' BEGIN INSERT INTO @ParsedList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1)
END END RETURN END GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
I have a report that includes two multi-valued parameters. In the Default Values section, I choose 'from query' and select dataset and value field. In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field. When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected). How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.
I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]
I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.
Problem: I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.
Intended output: For named projects QWER, QWET & QWEY:
QWER|16 QWET|2 QWEY|0
I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:
SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2 FROM Timesheet LEFT OUTER JOIN Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN Projects p1 ON Timesht.PR_Code = p1.PR_Code WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY')) GROUP BY p1.PR_Master
This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.
I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!
=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1
SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice FROM Table1 WHERE BillDate BETWEEN (SELECT Min(BillDate) FROM Table1) AND (SELECT Max(BillDate) FROM Table1) GROUP BY BillDate
but this doesn’t work- summing everityng
I don’t know how to indicate in first row of query SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice a WHERE clause for every day separately.
As my name shows I am about read to pull my hair out on this and will take any help that I can get. I have a table with the following values field1,field2,field3 a | p | 1 a | n | 1 a | p | 2 b | p | 2
b | p | 2 b | n | 3
I am grouping by first column a p 1 n 1 p 2 ------------------- a 3 1
b p 2 p 2 n 3 ------------------ b 4 3 What I want to do if it have a value of p I want the value in one column if it has a value of n I want it in another column. The columns are not a problem, I use a iif statement iif( field2 = p, value, 0) iif ( field2 = n, value, 0)
the problem comes when I try to total the columns. I was trying to use the =sum(field3) in my group total. the above example is what I want to see the below example is what I get. a p 1 n 1 p 2 ------------------- a 4 4
b p 2 p 2 n 3 ------------------ b 7 7
I hope this makes some since to someone out there that can help me out. I am getting kind of thin in the hair department so I cannot afford to loose any more.
I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):
date prod class qty 060101 a101 1a 100 060101 a101 1a 100
I would like to have the following:
date prod class qty
060101 a101 1a 200
Any other suggestions would be greatful!! Thanks in advance
