Summing Different Combinations Of Selections
May 7, 2008
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)
Thanks in advance
I.Shaame
View 9 Replies
ADVERTISEMENT
Jan 14, 2008
Hi,
I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane
I need to generate all 2 pair combinations of the names(i,e)
(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)
Can some one give me a generalized query for this problem?
Thanks,
Prakash.P
View 4 Replies
View Related
Apr 26, 2006
here is my question
the way our companies are setup are as follows
company A is DatabaseA
Company B is DatabaseB
this was done to ensure company specific data.
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.
thanks.
View 1 Replies
View Related
Oct 12, 2007
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");
Thanks
View 5 Replies
View Related
Feb 23, 2005
Hi,
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.
this SQL statment returns something like this:
------------------------------------------------
course1 | 5 stars | 10/02/05
------------------------------------------------
course1 | 5 stars | 03/02/05
------------------------------------------------
course2 | 4 stars | 01/01/05
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,
-------------
injureFish
View 2 Replies
View Related
Apr 11, 2006
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.
Thanks in advance!
Alan
View 14 Replies
View Related
Jul 31, 2004
I want to start with a table that has 4 records:
-Self
-Supervisor
-Peer
-Direct Rep
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)
-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer
How would I do this in an SQL Query? Thanks for your help!
View 1 Replies
View Related
Jul 9, 2006
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))
The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
The desired output should look like:
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
Thanks in advance.
View 3 Replies
View Related
Sep 6, 2013
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...
View 1 Replies
View Related
Oct 26, 2007
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
View 6 Replies
View Related
Oct 31, 2007
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.
View 3 Replies
View Related
Oct 14, 2005
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
View 18 Replies
View Related
Mar 29, 2007
Hello,
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...
Any help would be appreciated...
Thank you,
David
View 2 Replies
View Related
Jun 28, 2006
Hi All,
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?
Using RS 2000
thanx,
99
View 4 Replies
View Related
Mar 29, 2013
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.
View 5 Replies
View Related
Jul 23, 2005
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.
View 4 Replies
View Related
Jan 11, 2008
I need a function or a routine in sql for making all possible combinations of strings with given letters.
means if I give a string 'ab' the function should return
'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.
return data can be a single list or the list elements can be printed to screen one by one.
I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.
Please help.
View 4 Replies
View Related
Aug 7, 2006
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)
View 4 Replies
View Related
Mar 24, 2008
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
View 4 Replies
View Related
Aug 27, 2015
Below query:
Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)
Insert into @table
Select 1,123
union all
[Code] ...
--Expected output concatenation of consumer numbers all permutation and combinations having same
customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7
View 4 Replies
View Related
Jul 2, 2007
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.
View 3 Replies
View Related
Sep 22, 2006
I currently have a form that has different options in a list box such as:Status Codes
View 3 Replies
View Related
Jun 12, 2007
Hello
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?
Cheers
View 1 Replies
View Related
May 31, 2006
I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.
I was curious what is the best way to perform this in a Stored Procedure?
Can I use a UNION -- Not sure if this is the best choice
or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?
View 3 Replies
View Related
Jan 11, 2005
Hi,
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:
http://search1.workopolis.com/jobshome/db/work.search_cri
View 1 Replies
View Related
Aug 25, 2015
enable or shortcut key for Auto Database or Table View while coding. When I wrote sample code like below
SELECT * FROM VENKAT.
When I type V then I need to know there is Venkat Database with pop Message below.
View 2 Replies
View Related
Apr 28, 2008
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.
View 5 Replies
View Related
Jul 20, 2005
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]
View 1 Replies
View Related
Apr 30, 2008
Hi,
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.
Sample input:
Timesheet table
===============
pr_code|ts_hours
QWER.01|6
QWER.01|7
QWER.02|3
QWET.01|2
Project table
=============
pr_code|pr_master
QWER.01|QWER
QWER.01|QWER
QWER.02|QWER
QWET.01|QWET
QWER|QQQQ
QWET|QQQQ
QWEY|QQQQ
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
Any help most appreciated.
View 5 Replies
View Related
Jun 6, 2008
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
View 5 Replies
View Related
Oct 23, 2013
Let suppose that we have a table which look like this
BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00
I would like to sum a prices day by day and output to be like this
BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00
To point I’ve reached myself is a query:
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.
View 2 Replies
View Related
Feb 5, 2008
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.
View 3 Replies
View Related
Jul 20, 2006
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
View 4 Replies
View Related
