If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.
I have a categories table and a products table, the products are associated with a category, and my current select statement looks like this..
SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName FROM dbo.tbl_Products INNER JOIN dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID GROUP BY dbo.tbl_Categories.CatName
this give me ..
TotQty CatName 1 Books 2 DVD
I wonder how I can change the select statement so I retrieve a result like this..
TotQty CatName Description 1 Books Oliver Twist 2 DVD Dire Straits 2 DVD Elvis
I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.
Example : ======= Details table is as follows HeaderID DetailsID 1 1 1 2 1 3 2 4 3 5 3 6 3 7 3 8
SELECT H.TransDate, H.SupplierName, D.DetailsID FROM Header H, Details D WHERE H.HeaderID = D.DetailsID AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)
As I am dealing with very huge data it is taking very long time to execute.
Anyone know how to set total = 0, when the sum of that record is 0? Because when sum(total) = 0, the record is not selected anymore.
Like a stat table
Name | Total ---------------- David | 0 John | 10 Sammy | 5 Micheal | 0 Joe | 3 ----------------
When displaying those record using SELECT statement, only John | 10 Sammy | 5 Joe | 3 will be display, and i have to do a checking if the record not here, then assign 0 to them.
I wonder SQL can help to SELECT the records, so no extra work will done on the checking part. Expert please advice.
Dear All I have the following table structure and following data create table emp(empid char(4),empname char(10),empsal money,empsalPercent money,empsalRuningTot money) empid empname empsal empsalpercente001 tom 20.00 8.69e002 *** 40.00 17.39e003 harry 15.00 6.52e004 masum 25.00 10.86e005 rana 30.00 13.04e006 dipak 60.00 26.08e007 rana 40.00 17.39 now would like to make the following 1) want to sort first emp table based on empsalPercent field on descending order physicallyProb : am only able to select the same thru descending order but am unable to make it physical shortingselect * from emp order by empsalPercent desc 2) after make descending order for empsalpercent I want to make RunningTotal for the EmpsalPercent column pls helpsamir
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _ & " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _ & " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _ & " Order By DateClosed DESC ")
SET @RowCnt = 1 SET @date = CONVERT(CHAR(10),GETDATE(),110) SET @ArchPath = '\D$EDATAWorkFoldersSendSendData' SELECT @TotalRows = count(*) FROM table1 --select @ArchPath
WHILE (@RowCnt <= @TotalRows) BEGIN SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt --PRINT @AccountNumber --test SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = ''' + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c' --PRINT @sql EXEC master..xp_cmdshell @sql SELECT @RowCnt = @RowCnt + 1 END
I want to get some combined data from both tables, so right now I am joining them at the SessionStartTime column, which is a primary key in the first and a foreign key in the second table, something like this:
Code: SELECT DlIndexTable.SessionStartTime, DlTextDataTable.Channel01data FROM DlIndexTable LEFT JOIN DlTextDataTable ON DlIndexTable.SessionStartTime = DlTextDataTable.SessionStartTime WHERE DlIndexTable.SessionStartTime BETWEEN '2006-10-13 16:40:08.790' AND '2012-03-01 17:54:30.930' ORDER BY DlIndexTable.SessionStartTime, DlTextDataTable.ChTimestamp
The trouble is that this query, exactly as requested, gives me all the entries from the second table matching the first, while I really would like to pick just one row (preferably, the first chronologically - by ChTimestamp) so that the first column (SessionStartTime) has distinct entries in the resulting table. What would be the simplest way of doing that? Performance is not a big priority over simplicity since the first table could have only a few hundred rows (maybe a couple of thousand), while the second will be real tiny.
my table isCustomer Customer Id--------- ----------------Mary 1Jhon 2Anna 3OrderId CustomerId Product ProductDesc------- --------- --------- -----------1 1 video bla bla2 1 tv bla bala3 2 video bla bla4 2 cd bla blaI want to see-------marry tv bla blaJohn cd bla blaanna
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
Hello folks, I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.
Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends........:eek: :eek: :eek:
Query 1 -- select last record SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,* FROM Living_Situation )t WHERE Seq=1
I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.
Tables include:-
1. REQ 2. RFQ 3. PO 4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO) 5. DOCUMENT_STATUS (contains status of document, REQ_CREATE) 6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT) 7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)
Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.
I realised the multiple lines generated due to the following:-
One to many relationships: A. RFQ - 1 or more PO B. PO - 1 or more PO_REVISON
I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.
Table A brings one record but table B brings two records for record in table A.
What I need is join both tables but I must bring (always) the max created record from table B if there are more than one record for the one in table A. Is this clear?
I dont want to use GROUP BY beacuse it is a very big query with lot of tables and it will be a mess.
I will give an example: TABLE A
ID NAME 1 PETER
TABLE B
ID_TABLEA CREATED BROTHER 1 1-1-08 JOHN 1 2-1-08 PATRICK
If I join both tables I will have two records. What I need is to have the max created record from table B even if one or more records are joined. I only need one match from table B for each record of table A. Any suggestion? Thanks in advance.
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment WHERE [TicketID] = t.TicketID ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID
Thanks in advance for you help.SQL Server 2000I have a complex join, but a simple example will do the trick so...table1 (placement records, possibly many per case,highest ID beingmost recent)----------placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Numbertable2 (Organizations Table, Many Placements can be at 1 organization)----------OrganizationID(pk) * OrgName * OrgTypetable 3(Case Table, each Case can have many placements)----------CaseID(pk) * StaffName * CreationDatenow my query...SELECT t1.placementID,t1.caseID,t2.OrgNameFROM table1 as t1INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationIDWHERE exists (select distinct max(placementID),CaseIDfrom t1 group by caseID)GROUP BY t2.OrgName,t1.PlacementID,t1.CaseIDmy results-------------placementID CaseID OrgName1 1 oneOrg2 1 two org3 1 three org4 2 another org5 3 yet another orgmy desired results------------------PlacementID CaseID OrgName3 1 three org4 2 another org5 3 yet another orgas you can see i get all records, but what i want is to see only thelast placementID for each case so i dont want duplicate caseID but Ido need the orgName, and yes the query works correctly without the orgname but as soon as i add orgName to the select statement I getduplicate CaseID's, How do i eliminate duplicate CaseID's and get onlythe MAX(placementID) for each Case and the OrgNameplease advise, getting desperate.thanks again so much for the help
SELECT U.UserFamilyName, F.FamilyName FROM Users U LEFT JOIN User_Friends F ON U.UserID = F.UserID WHERE U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID, dbo.tblProducts.ProductName, dbo.tblProducts.ProductID FROM dbo.tblProducts LEFT OUTER JOIN dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only How can I do that?
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box SubRepM1 The name of the subreport is subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but Im having major problems trying to get these totals to work.
hi! i actually have more than question related to the SQL Server 2000 1) On my system I charge users for using an online service (SMS ) and I have a monthly fees charge and monthly allowance usage so if they exceed the limit I can also charge them for over usage this is an example for a rate that I have in my Rate Table
Rate Id = 1 MonthlyFees £10.0 MothlyAllowence = 100 sms/ month Extra usage = 0.12 p for each extra SMS
The system should calculate the monthly invoice from the registration date so if you register in 14 April the invoice will be generated every month at the same day (15) each month.
Now! I need to generate and calculate the invoice …..Where shall I do the calculation? In the Business object Layer or in somewhere else? And for invoicing the client every month I might have 20 clients and each one might have different day so the billing should be auto generated also what properties or methods that can calculate or check monthly related to the day so it will run in the way like the phone bill!! any advice is appreciate it!
