Transact SQL :: Working Of Aggregate In Sub-query Using Exists?
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
ADVERTISEMENT
Nov 18, 2015
I am using SQL Server 2008 - and what I want to do is set my variable @dh. Â If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. Â How can I do that?
Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int
Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'
[Code] .....
View 3 Replies
View Related
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
View 3 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
Mar 31, 2004
I need to drop a table if exist ?? how can i do that ??
thanx !
View 3 Replies
View Related
Oct 22, 2015
(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??
View 11 Replies
View Related
Sep 2, 2015
I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month).Â
For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015.Â
DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)
[Code] ....
the output for the above example should be
C123Â P123Â 7/31/2015
 70+10=80
is this possible?
View 10 Replies
View Related
Aug 5, 2015
How can I aggregate this result into 1 row? (I got it from a UNION ALL)
Article        Assort1   Assort2
50095811Â Â Â K1Â Â Â Â Â Â Â Â Â Â Â NULL
50095811Â Â Â NULLÂ Â Â Â Â Â Â Â K3
I would like to have
Article        Assort1   Assort2
50095811Â Â Â K1Â Â Â Â Â Â Â Â Â Â Â K3
View 3 Replies
View Related
Aug 6, 2015
SSMS does not like mine! Â THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID
View 6 Replies
View Related
Aug 17, 2015
I need to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table
View 3 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
May 21, 2015
Have this table
ACCOUÂ NAMEÂ Â Â Â Â NAMEÂ TODATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â IDÂ Â Â Â EDUCATÂ Â Â EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647Â MILUCON Empl1 1900-01-01 00:00:00.000Â 9751Â VCA-VOLÂ 2018-02-11 00:00:00.000
Need to get it like
ACCOUÂ NAMEÂ Â Â Â Â NAMEÂ TODATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â IDÂ Â Â Â EDUCATÂ Â Â EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647Â MILUCON Empl1 1900-01-01 00:00:00.000Â 9751Â VCA-VOLÂ 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
View 11 Replies
View Related
Aug 7, 2015
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? Â This is what I have thus far, and it works as it should UNTIL I add in the line ofÂ
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after. Â
;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),
[Code] .....
View 4 Replies
View Related
Oct 2, 2015
This is my query, from my nonono table I need to return the most recent date from attempted (column name) Â How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
View 3 Replies
View Related
Oct 23, 2015
I am trying to bulk update about 50,000 rows in a SQL table. Â The values that the table MUST contain are:
1-3 days
4-7 days
8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days
4 - 7 days
8 - 10 days
What would be my best methodology of removing the space between numerics only? Â I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
View 8 Replies
View Related
Feb 18, 2010
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies
View Related
May 13, 2015
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+
[Code] .....
I think I probable need to include a group by but can't figure out the correct syntax.
View 12 Replies
View Related
May 26, 2015
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
View 11 Replies
View Related
May 25, 2015
I have a table which has 2 columns and the data is like below
API_Number       Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group B
4567Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group C
7896Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group D
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group E
 I wanted to find the API numbers which are repeating in different groups. In the output I want
 API_Number          Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group B
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group E
View 5 Replies
View Related
Jan 21, 2010
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
View 5 Replies
View Related
May 6, 2015
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
View 5 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Oct 29, 2015
Usual way to check if file exists
DECLARE @File_Exists INTÂ
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUTÂ
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INTÂ
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backup
ul', @File_Exists OUTÂ
print @File_Exists
0
If use xp_subdirs like:
 EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists,
Msg 22006, Level 16, State 1, Line 3
xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
How to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
View 8 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store   Type   Sales
11 Â Â Â Â Â A Â Â Â Â 1000
23 Â Â Â A Â Â Â 1980
23 Â Â Â B Â Â Â 50
5 Â Â Â Â B Â Â Â 560
I want to filter the store in "where clause" Â where
1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= Â (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 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
Oct 21, 2015
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. Â The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. Â That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
[Code] ...
The return data looks like this:
3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0
[Code] ....
I want it to look like this:
3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0
View 8 Replies
View Related
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
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
