Correlated And Aggregate Sql Query
Aug 8, 2007
I have a table similar to the following:
ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦
---------------------------------------------------------------------------
Important - Each person can have more than one entry.
What I am trying to do is get the last percentage that each person obtained.
The only way I have been able to do this is by the following:
SELECT * FROM myTable
LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1
ON myTable.NameID = t1.NameID
WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
View 8 Replies
ADVERTISEMENT
Nov 10, 2006
Ajaya writes "SERIAL DATE STATUS
123 10/02/06 PENDING
123 01/06/06 DECLINE >
345 08/30/06 APPROVE>
345 10/05/06 APPROVE
567 08/14/06 APPROVE>
567 08/22/06 APPROVE
678 10/17/06 OTHER STATUS
678 10/17/06 APPROVE>
678 10/24/06 APPROVE
789 10/04/06 DECLINE
789 10/06/06 OTHER STATUS
789 10/06/06 APPROVE>
I am looking for a sql which extract
EARLIEST DATE IF STATUS = APPROVE OR DECLINE, ONLY NEED TO CONSIDER THESE TWO STATUSES.
for example serial = 789, I want to select the last row, since it is approved status.
The arrow in the right side , means i want to select that row from my sql.
If both statuses(decline and approve) is falls for a serial number, then I want to select the row with approved status with earliest date. example sir = 789
Please help"
View 1 Replies
View Related
Dec 27, 2007
I'm using Transact SQL for SQL 2000 and I'm having difficulties with a correlated sub-query.
I have a table called 'Results' like this...
Company_ID Product Spend Flag_Top25
1 Product A $100
1 Product B $250
1 Product C $450
I want to create a flag to identify if a companies spending for each product is within the Top 25% of spending within the product category across all products.
For example, the code below would identify the Top 25% of Companies with spending on Product A.
SELECT TOP 25 PERCENT Company_ID
FROM Results
WHERE Product = 'Product A'
ORDER BY Spend DESC
I'm trying the following correlated sub-query to get this done and it is not working.
UPDATE Results A
SET A.Flag_Top25 = 'Top 25% Customer'
WHERE A.Company_ID in (
SELECT TOP 25 Percent B.Company_ID
FROM Results B
WHERE B.Spend > 0
and B.Product = A.Product
ORDER BY B.Spend DESC
)
View 2 Replies
View Related
Aug 10, 2014
I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.
The desired output is:
Yellow Blue
11/23/201311/19/2013
11/19/210310/01/2012
10/01/210210/08/2010
10/08/201012/14/2007
The actual output is:
Yellow Blue
11/23/201311/19/2013
11/19/210311/19/2013
10/01/210211/19/2013
10/08/201011/19/2013
11/23/201310/01/2102
11/19/210310/01/2102
10/01/210210/01/2102
10/08/201010/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.
Here is the SQL:
SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
[Code] .....
View 9 Replies
View Related
Mar 31, 2015
I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.
How can I convert this
SELECT TOP 5
(SELECT SUM(lt2.col3)
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result
FROM dbo.MyTable1 t1
... to an inner join or a sql2012 window function?
By the way, I just added the TOP 5 myself while testing. It's not in the main query.
View 9 Replies
View Related
Feb 2, 2007
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
View 6 Replies
View Related
Jun 17, 2007
Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can’t get it right.
When I added “PO_number? to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks
View 4 Replies
View Related
Dec 21, 2007
I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
View 4 Replies
View Related
Jul 11, 2005
I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
View 3 Replies
View Related
Feb 13, 2014
I would like to find the first transaction_date with the criteria below and return all transactions after. i would need to use the seqn number since many of the transactions could occur on the same day.
SELECT c.MEMBER_TYPE
, c.DATE_ADDED
, h.ID
, h.ACTIVITY_TYPE
, h.TRANSACTION_DATE
, h.UF_1
, min (h.seqn)
FROM
[code]....
View 3 Replies
View Related
Feb 29, 2008
I have two tables ItemHistory and ItemStock.
I would like to write a query which checks last years history and let us know if we have enough items in stock this year for a given span date.
First, It should get all items from @ItemHistory where WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007'
and then checks if corresponding items are found in @ItemStock,
and then returns all the ItemID where sum(@ItemStock.Quantity) < sum(@ItemHistory.Quantity)
Thank You.
Here is the DDL and DML
DECLARE @ItemHistory TABLE (
ItemID INT,
Quantity INT,
DateSold DATETIME )
INSERT INTO @ItemHistory
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 26, 12, '2007-01-10' UNION ALL
SELECT 28, 06, '2007-01-10' UNION ALL
SELECT 29, 06, '2007-01-10' UNION ALL
SELECT 30, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 32, 12, '2007-01-10' UNION ALL
SELECT 33, 01, '2007-01-10' UNION ALL
SELECT 33, 06, '2007-01-10' UNION ALL
SELECT 36, 01, '2007-01-10' UNION ALL
SELECT 52, 12, '2007-01-10' UNION ALL
SELECT 83, 01, '2007-01-10' UNION ALL
SELECT 36, 12, '2007-01-10' UNION ALL
SELECT 37, 01, '2007-01-10' UNION ALL
SELECT 38, 12, '2007-01-10' UNION ALL
SELECT 17, 01, '2007-01-10' UNION ALL
SELECT 17, 08, '2007-01-10' UNION ALL
SELECT 12, 20, '2007-02-20' UNION ALL
SELECT 26, 10, '2007-02-20' UNION ALL
SELECT 30, 08, '2007-02-20' UNION ALL
SELECT 31, 12, '2007-02-20'
DECLARE @ItemStock TABLE (
ItemID INT,
Quantity INT )
INSERT INTO @ItemStock
SELECT 12, 20 UNION ALL
SELECT 12, 10 UNION ALL
SELECT 14, 48 UNION ALL
SELECT 17, 24 UNION ALL
SELECT 19, 36 UNION ALL
SELECT 19, 72 UNION ALL
SELECT 20, 72 UNION ALL
SELECT 26, 24 UNION ALL
SELECT 28, 12 UNION ALL
SELECT 29, 12 UNION ALL
SELECT 30, 12 UNION ALL
SELECT 31, 18 UNION ALL
SELECT 32, 20 UNION ALL
SELECT 32, 68 UNION ALL
SELECT 33, 10 UNION ALL
SELECT 35, 18 UNION ALL
SELECT 36, 46 UNION ALL
SELECT 36, 40 UNION ALL
SELECT 37, 30 UNION ALL
SELECT 38, 10 UNION ALL
SELECT 38, 33
View 3 Replies
View Related
Jan 4, 2008
Hi all. Here's my question:
I have a database table that contains rows containing defect data. The two columns I'm interested in are device and severity. Device is the name of an item that contains the defect, and severity can be one of three values. I'd like to query the table so it shows the device, and the number of instances of each of the three values for that device. For example, the result table should look like this:
device Severity 1 Severity 2 Severity 3
Widget 12 15 24
In this example, There are 51 rows containing Widget as the device. 12 have severity 1, 15 have severity 2, and 24 have severity 3.
Seems like it should be a pretty simple query, but I'm kinda new to SQL and I'm having trouble. Help would be much appreciated.
Thanks.
View 5 Replies
View Related
May 26, 2004
Hi all,
I need to build a query that aggregates the orders that have the same items,ex:.
ORDERS:
ID NUMBER
------------ ----------
1 123
2 456
3 789
ITEMS:
ORDERFK NAME
------------ --------
1 A
1 B
2 A
2 C
3 A
3 B
the result should be:
TOT_ORDERS:
NEW_ID QUANT
------------ ---------
1 2
2 1
NEW_ITEMS:
TOT_ORDERFK NAME
----------------- --------
1 A
1 B
2 A
2 C
Some ideia?
ThX
NeuralC
View 6 Replies
View Related
Jun 22, 2005
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
TableName: Employees
EmployeeID............Date.............PayRate
-------------------------------------------
....1001...............1/24/03............12.50
....1002...............2/28/03............12.75
....1003...............5/14/03............10.50
....1002...............3/15/04............15.00
....1001...............6/22/04............14.00
....1002...............3/16/05............14.00
The result set should look like:
EmployeeID...........Date.............PayRate
-----------------------------------------
...1003................5/14/03............10.50
...1001................6/22/04............14.00
...1002................3/16/05............14.00
View 1 Replies
View Related
Jan 15, 2014
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
View 3 Replies
View Related
Jul 23, 2005
I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?
View 2 Replies
View Related
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, BillableLastYear is a number, aggregated (sum) from child units.
The second factor, BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
,[UnitID]
,[UnitName]
,[Unit2Name]
,[Unit3Name]
,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Oct 17, 2006
I currently have the following query:
Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers
This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.
Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.
[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.
Here is the query I started to build before I ran into serious trouble:
Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName
This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...
Have I done an okay job of explaining the problem?
The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.
Thanks for any advice!
Zoop
View 3 Replies
View Related
Jun 1, 2008
I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success
What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:
SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME
Thank you. The above query actually targets a result table from another query, but that should not matter.
View 11 Replies
View Related
Mar 14, 2001
Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.
Here's what I'm trying:
select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id
but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).
I'm new to SQL and could really use the help.
Thanks
View 1 Replies
View Related
Mar 13, 2012
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
SAMPLE:
CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743
DESIRED RESULTS:
CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711
View 7 Replies
View Related
Dec 1, 2014
I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).
declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
[Code] ....
--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL
[Code] ....
View 8 Replies
View Related
Aug 4, 2015
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from A where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
View 8 Replies
View Related
Jan 13, 2008
To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero?
Thanks
View 1 Replies
View Related
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
Jan 21, 2008
I've set up a correlated SQL query but i'm not entirely sure i've got it right.
The purpose of it is to get all the unapproved hours for a TimeCode where the CodeOwner matches @UserName.
It gets the unapproved hours for every code rather than the ones for the UserName.
SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved,
(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner
FROM lward.tblHours
WHERE Approved is NULL The Username in tblHours is used for storing who is logging some hours. CodeOwner is the Owner of a paticular time code.tblHoursID, TimeCode, Date, Hours, Username, Comments, ApprovedtblCodesCodeID, CodeName, CodeDescription, CodeOwner, CodeCategoryThe only relationship is CodeID and TimeCode, CodeID is the primary key and TimeCode is the foreign.
View 7 Replies
View Related
Jun 6, 2008
HiI was trying an example of subquery Create Table #Temp( PK_ID int identity(1,1), sName Varchar(50) )Create Table #TempAddress( PK_ID int identity(1,1), PersonID int, Address Varchar(100))Insert Into #Temp Values('Karan')Insert Into #Temp Values('Gupta')Insert Into #Temp Values('Karan')Insert Into #Temp Values('Karan')Insert Into #TempAddress Values(1,'Address1')Insert Into #TempAddress Values(2,'Address2')Insert Into #TempAddress Values(3,'Address3')select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)Drop Table #TempDrop Table #TempAddressBut I am getting an errorMsg 4145, Level 15, State 1, Line 29An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'. Am i doing something wrong.Kindly adviceRegardsKaran
View 1 Replies
View Related
Apr 28, 2000
Hello,
I need to come up with a query (I really don't want to use cursors) that will perform the following:
I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.
Here are the pertinent fields: userid,message_id,message_date
I really appreciate your help,Rob
View 2 Replies
View Related
Nov 8, 2007
This really a question of approach more than anything else.
The situation is as follows:
I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.
So that...Given the date range 11/1/2007 to 11/2/2007 and the data set
Code:
11/1/2007 CompanyA req1 req2 req3 req4
11/2/2007 CompanyA req1 req2 null req4
11/1/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyC req1 null req3 req4
Only the two rows for CompanyB would ultimately be delivered.
On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?
View 1 Replies
View Related
Feb 10, 2005
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoit
View 6 Replies
View Related
Mar 24, 2007
Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.
My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.
View 8 Replies
View Related
Dec 17, 2007
I am trying to perform an update based on the following query
select o.order_id,
from orders o, accounts a
where a.account_id=r.account_id and
a.event_type IN (2003, 3057, 4003, 7005)
order by o.order_id desc
I have tried several combinations but to no avail.
Can anybody provide a solution?
Thanks in advance
Declan
View 11 Replies
View Related
