Doing Unions With Uneven Column Amounts
Mar 14, 2006
I'm creating a program that allows users to submit a report on equipment at regular intervals. If a piece of equipment has a problem, it is given a job entry that refers back to the report for various information.
However, there will be times when a problem is noticed, and someone wants to submit it immediately; these are made into extra jobs.
To this end, I have three tables:
Reports
Jobs
ExtraJobs
Because ExtraJobs cannot be associated with a report, they have their own table, which holds information that would otherwise be grabbed from both Reports and Jobs. While there are seperate submission forms for regular jobs and extra jobs, I would like them to appear on the same query result when a user looks at submitted jobs or reports.
What I'm currently trying to do is this:
Code:
SELECT*
FROMReports LEFT JOIN Jobs
ON Reports.reportID = Jobs.reportID
UNION ALL
SELECT ExtraJobs.*
FROM ExtraJobs
This won't work because the first half of the union has an extra column (reportID) that the second half does not. Is there any way to add in a value for that non-existant column (say, ExtraJobs.reportID = -1) to make sure that both sides have an equal number of columns?
If worst comes to worst, I could add a reportID column to ExtraJobs and have it set to -1 for everything, but I'd like to keep from adding fat, if at all possible.
View 2 Replies
ADVERTISEMENT
Mar 21, 2008
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
View 4 Replies
View Related
May 26, 2006
Hi guys
Code:
SELECT C.customerID, quantity, unitprice, (SELECT quantity * unitprice), OD.productID,
FROM customers C
INNER JOIN Orders O
ON C.customerID = O.customerID
INNER JOIN [order details] OD
ON O.orderID = OD.orderID
ORDER BY C.CustomerID
The Output looks a little like this:
Code:
customerID quantity unitprice productID
---------- -------- --------------------- --------------------- -----------
ALFKI 15 45.6000 684.0000 28
ALFKI 21 18.0000 378.0000 39
ANATR 1 28.8000 28.8000 69
ANATR 7 9.2000 64.4000 19
ANATR 10 34.8000 348.0000 72
ANTON 24 16.8000 403.2000 11
ANTON 15 46.0000 690.0000 43
AROUT 25 3.6000 90.0000 24
AROUT 16 19.0000 304.0000 36
BERGS 16 15.5000 248.0000 44
BERGS 15 44.0000 660.0000 59
BLAUS 3 10.0000 30.0000 21
BLAUS 21 34.0000 714.0000 60
BLONP 35 12.5000 437.5000 31
BLONP 15 19.5000 292.5000 57
BOLID 24 17.6000 422.4000 4
BOLID 16 15.6000 249.6000 57
BONAP 40 36.8000 1472.0000 43
BONAP 20 7.7500 155.0000 75
BONAP 8 30.0000 240.0000 7
BONAP 20 6.0000 120.0000 13
BONAP 20 25.0000 500.0000 6
BONAP 20 23.2500 465.0000 14
BONAP 10 9.2000 92.0000 19
BOTTM 16 24.8000 396.8000 10
BOTTM 9 46.0000 414.0000 43
BOTTM 30 4.5000 135.0000 24
BOTTM 21 49.3000 1035.3000 62
BOTTM 15 2.5000 37.5000 33
I would like to add the totals from each customerID and then show the customerID once with the final total amount.
The above SELECT statement was the closest I could come.
The help is always appreciated!
Justin
View 5 Replies
View Related
Nov 17, 2007
Hi everyone.
A delimited file is being sent to us from another company. The file is supposed to have 10 columns in each row. We are going to process the file using SSIS (2005)
Question - how do we handle the file if some of the rows are bad - are missing one or more columns?
If my package reads the file using a flat file source, when I run the package with a file where some of the rows have fewer than the expected 10 columns, my package abends on the flat file source task.
All we can think of doing is writing .net code to process the file as the first step of the package or even outside the package, to remove bad rows from the file before it hits SSIS.
Browsing through a couple of threads here similar to mine, it appeared to me that MS staff responded by stating the functionality of the flat file source may be enhanced in the future to handle this?
Thank you, Glen
View 4 Replies
View Related
Jan 20, 2006
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |
The
Quick
Brown
Fox
Jumps
Hello
World
NULL
NULL
NULL
This
is
a
test
NULL
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |
The
Quick
Brown
Fox
Jumps
Hello
World{CR}{LF}This
is
a
test
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
View 64 Replies
View Related
Jul 18, 2000
I know the answer must be somewhere, but for the moment every place I look ...
How many tables can be unioned together (I think I remember that 6.5 had a limit of 16) in SQL7?
Thanks,
Judith
View 2 Replies
View Related
May 6, 2004
hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono
View 4 Replies
View Related
Aug 11, 2004
Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:
CREATE Procedure spGetReturnCheckForCriteria
@SearchCriteria VARCHAR(8000),
@SortOrder VARCHAR(8000),
@PageSize INT
AS
-- Declare vars
DECLARE @SQLStatement NVARCHAR(4000)
DECLARE @bldSQLStatement VARCHAR(8000)
DECLARE @retValue INT
-- Initialize vars
SET @SQLStatement = ''
SET @bldSQLStatement = ''
SET @retValue = -1
-- Sanity Check(s)
IF (@PageSize IS NULL OR @PageSize < 1)
-- Paging Size can not be Null, nor less than One
BEGIN
SET @RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
RETURN
END
-- Build the Paging SQL Statement
SET @bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @bldSQLStatement = @bldSQLStatement + CAST(@PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @bldSQLStatement = @bldSQLStatement + '
ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,
CASE
WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
ELSE ReturnCheck.UpdatedDate
END AS ReturnCheckTimeStamp
FROM TaxPayment
INNER JOIN DistributionPoint
ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
INNER JOIN ReturnCheck
ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@SearchCriteria IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' WHERE ' + @SearchCriteria
-- Add Sort Order
IF (@SortOrder IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' ' + @SortOrder
-- Set the SQLStatement
SET @SQLStatement = @bldSQLStatement
-- Execute the Paging Query
EXECUTE @retValue = sp_executeSQL @SQLStatement
GO
Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.
View 3 Replies
View Related
Oct 2, 2001
I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
View 1 Replies
View Related
Oct 2, 2001
I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
View 1 Replies
View Related
Oct 7, 2004
Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...
SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName
UNION ALL
SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName
Thanks for any advice
Aaron
View 4 Replies
View Related
Jul 20, 2007
I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.
Is there such an issue I've missed?
Mike
View 6 Replies
View Related
Aug 31, 2007
How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?
View 4 Replies
View Related
Dec 9, 2013
I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35
[code]....
View 4 Replies
View Related
May 1, 2006
I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.
When I run the two views as below it works fine
Select * from V1
Union
Select * from V2
I then created another view V3 of the above union as
Create view V3
As
Select * from V1
Union
Select * from V2
Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables
View 1 Replies
View Related
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
View Related
Nov 20, 2003
I was wondering what is the best solution to store large amounts of text in a SQL 2000 field. This text will be entered into a multiline textbox.
ex) what data type?......should I use BLOBs?
Thanks,
Trey
View 4 Replies
View Related
Sep 21, 2012
I have two tables. One is Invoice_tbl, with one account per customer.
This table has 3 fields; CustomerID, InvoiceAmount, InvoiceID.
Then second table is, Payment_tbl, with 2 fields; InvoiceID and PaymentAmount. The Payment table can have multiple payments from each customer.
With Access, i would run a QUERY(call it PaymentTotal) against Payment_tbl, then do a "GroupBy" on InvoiceID and SUM on the "Amount" field.
I then would create a NEW query against Invoice_tbl and INNER JOIN on Payment Total.
How would i do this with SQL?
View 6 Replies
View Related
Dec 30, 2013
I have a view table A with 4 columns dateseq,SalesAmount, customerseq, CostofGoods and customerdescription.
Fact table B that has 4 columns dateseq,SalesAmount, Costofgoods, RebateAmount, and Customerseq.
My code is like this
select
ta.customerdesctiption,
sum(TA.salesamount) as salesamount,
sum(TB.RebateAmount) as salesamount
from
TableA TA
left join
tableB TB
on TA.dateseq = TB.dateseq
where
ta.dateseq like '%2013'
I would like to get
Customer A | 10,000
Customer B | 3,000
etc
What I get is:
Customer A | 20,000
Customer B | 6,000
etc
View 2 Replies
View Related
Mar 31, 2008
Hi,
I was wondering if any one could help me, I need to store large amounts of data in my database, at present I have it set to nvchar (8000), I've looked around and noticed you can use text which stores up to 2 million, but is slow in displaying the information.
Any ideas or points in the right directions would be great.
Thanks
View 6 Replies
View Related
Sep 8, 2005
Does anyone have ideas on the best way to move large amounts of databetween tables? I am doing several simple insert/select statementsfrom a staging table to several holding tables, but because of thevolume it is taking an extraordinary amount of time. I consideredusing cursors but have read that may not be the best thing for thissituation. Any thoughts?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254055.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877392
View 4 Replies
View Related
Jul 20, 2005
We are looking to store a large amount of user data that will bechanged and accessed daily by a large number of people. We expectaround 6-8 million subscribers to our service with each record beingapproximately 2000-2500 bytes. The system needs to be running 24/7and therefore cannot be shut down. What is the best way to implementthis? We were thinking of setting up a cluster of servers to hold theinformation and another cluster to backup the information. Is thispractical?Also, what software is available out there that can distribute querycalls across different servers and to manage large amounts of queryrequests?Thank you in advance.Ben
View 10 Replies
View Related
Jun 24, 2005
I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.
View 11 Replies
View Related
May 8, 2007
Greetings
I need to be able to graph roughly about 150 employees/ supervisor and their monthly cell phone usage in minutes. I understand that I will need to group this on say one graph for every ten employees so it doesn't look messy and cluttered. I have read some threads here but they dont seem to work for me.
So again each supervisor has 100+ subordinates and I need to graph theie phone usage by month
thoughts???
km
View 2 Replies
View Related
Mar 20, 2007
this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.
Should this be just one long string-- or does ssrs have another way to format this
thanks as always
KM
View 2 Replies
View Related
Mar 3, 2006
I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:
1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5
Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?
Would parameterized updates be faster?
Any help will be appreciated.
View 3 Replies
View Related
Feb 8, 2008
Hi,
I have a series of SSIS packages, all of which are ultimately executed by a parent package.
I'm consitently getting "OutOfMemory" errors when working with the packages which is temporarily solved by closing Visual Studio and re-opening the package(s)... This solution is short lived however as the OutOfMemory error occurs quite quickly after re-opening, often after doing nothing other than altering a variables default value and attempting to save the package.
The average size of the packages in question (.dtsx files) is around 7,000kb with the largest being 12,500kb. The total size of all the solution's packages is ~75,000kb.
The Processes tab in Task Manager shows a Mem Usage counter for devenv.exe *32 of around 20,000kb when Visual Studio is first opened however, when a single ~6,000kb dtsx file is opened this counter jumps to +300,000kb and when the entire solution is opened (When the parent package is executed), the Mem Usage counter for devenv.exe *32 is a massive +800,000kb!!!
Is this normal SSIS behaviour or do I have a major problem? Any tips or suggestions as to how to resolve this issue would be gratefully received.
FYI, "SELECT @@VERSION" gives me "Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) "
My Server is Windows Server 2003 R2 Enterprise x64 SP2 with 8GB of RAM.
Thanks in advance.
Leigh.
View 7 Replies
View Related
May 9, 2006
How can I check for Null for the amounts if no records are returned in either select. Basically it errors out if one or both of the Amounts return no records. I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me
SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(
SELECT pd.Amount as pd1_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999'
UNION
SELECT pdd.Amount as PD2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999'
) as PDC_Main
View 3 Replies
View Related
Mar 6, 2008
I have been looking into mirroring a large amount of small databases approx 150 databases.
As I understand this won't be feasible because of the way mirroring threading works, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=441900&SiteID=1
As I understand it for every database being mirrored sql will ping the mirror second, causing a network bottleneck?.
Also that the amount of threads generated for each mirrored database will cause also cause a bottleneck?
At the moment our database servers are under very little pressure and as an estimate use about 10% of the resources allocated to them such as CPU utilization, memory, disk IO and network. Our server hardware is Dual Quad core Xeons with 4 - 8 gig of memory and variety of 10k SCSCI raid configurations from raid 5 or 1,0 and sql 2005 32bit.
Ive done some calculations on the log file generation rate compared to network bandwidth there is more than enough network bandwidth.
Has anybody had any luck in mirroring many small databases?
My concerns is how much traffic is caused by the pinging of the mirror for each database?,
How many threads will the mirroring cause and what is the max amount of threads sql can handle?
How much memory will be consumed by each one of these mirroring threads?
View 1 Replies
View Related
Aug 12, 2005
I am running into a problem inserting large amounts of text into my table. Everything works well when I test with a few simple words but when I try to do a test with larger amounts of text (ie 35,000 characters) the appropriate field is left blank. The Insert still performs (all the other fields recieve their data, but the "Description" field is blank. I have tried this with both "text" and "ntext" datatypes. I am using a stored procedure with input parameters. As I mentioned, the query goes off flawlessly with small amounts of data (eg "Hi there!") but not with the larger amount.I check and the ntext field claims to be able to accept 1073741823 bytes of data. Is there some other thing I should consider with large amounts of text?
View 6 Replies
View Related
Apr 20, 2001
p.s. my email was incorrect in the last mail.
Hi all,
is there a sql 2k thread. Am interseted in finding out what the largest database size of a sqlserver database people have worked with.
We have a 1.2 Terabyte db with about 150-200 million new rows being processed everyday. Would like to share some thoughts on this with other people who are working with this much data and what they are doing with it.
bhala
----------------------------------------
Please check us out at: http://www.bivision.org/bivision
View 2 Replies
View Related
Jan 5, 2012
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code:
user_id, amount, status
1, £10, S
2, £20, P
3, £30, P
3, £40, E
Users:
Code:
user_id, name
1, user A
2, user B
3, user C
And I want to produce a summary table like this:
Code:
S P E Total
user A £10 £10
user B £20 £20
user C £30 £40 £70
What I have is:
Code:
SELECT Users.name,
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E')))
FROM Invoices
LEFT JOIN Users ON Users.user_id = Invoices .user_id
GROUP BY Invoices.user_id, Users.name
ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
View 9 Replies
View Related
Dec 18, 2014
Getting Incorrect Syntax near the keyword 'and'
This table returns multiple records for an Invoice.
Based on the transactiontype_desc the Amount_Paid_DC is a different value. Trying to add up the amounts based on the transactiontype.
select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt,
case transactiontype_desc when 'Sales Invoice' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
group by debtornumber, Invoicenumber
View 6 Replies
View Related
