Count Of Related Records??
Jul 23, 2005
Hi,
I was wondering if it was possible to build a query that will include a
column that will provide a count related records from another table.
Although there is a way to achieve this through programming in the
front end, I would like to know if it possible to achieve the same
thing through a SQL statement alone.
For example, say you have two related tables, Invoices and
InvoiceItems. InvoiceID is the primary key of Invoices.
Invoices table
InvoiceID PO_Num CompanyID
-------------------------------
1 37989 3
2 87302 4
3 78942 3
InvoiceItems table
ItemID InvoiceID PartNo Qty
---------------------------------------
1 1 ABA 3
2 1 ASLKDJ 2
3 1 9LF 8
4 2 IEPOW 18
5 2 EIWPD 3
6 2 DSSIO 1
7 2 EIWP 5
8 2 DC93 4
9 3 85LS0 8
Then a query that has the Invoices table plus a count of InvoiceItems
for each InvoiceID would generate this:
InvoiceID PO_Num CompanyID ItemCount
-------------------------------------------------
1 37989 3 3
2 87302 4 5
3 78942 3 1
Does anyone have any ideas how this would be done?
Thank you.
View 1 Replies
ADVERTISEMENT
Oct 31, 2007
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)
example of data
Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17
If anyone could help it would be much appreciated.
View 6 Replies
View Related
Mar 8, 2006
Plz help me out making a SELECT for my imagegallery.
Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all.
Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId}
In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath]
To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle]
To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select
And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID()
-But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
View 4 Replies
View Related
Sep 8, 2006
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter
Set counter = currentpage
If counter > lastpage
Goto task exit loop
Else perform task - (insert string into table)
Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int
DECLARE @Current string
DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage
SET @Count= dbSL.tblPaging._colPagingCount
SET @Last= dbSL.tblPaging._colLastPage
WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN
PRINT 'Hello World'
SET @Count = @Count +1
END
Any help much appreciatted.
Thanks
nme
View 1 Replies
View Related
Mar 4, 2005
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
View 3 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
Oct 17, 2007
Hi
I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it.
Thanks
View 4 Replies
View Related
May 30, 2008
Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6
View 14 Replies
View Related
Jan 30, 2014
I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code:
DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
View 4 Replies
View Related
May 29, 2008
Oh hai
This is closely related to this problem where Ryan & Peter helped me out loads (thanks again guys):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989
I now have a slightly different problem. Instead of returning the cumulative counts of people related to a business unit in the hierarchy I now need to return the actual people records. I had some code, derived from the above, that worked fine until I loaded some new data. It took a while but eventually I figured out the (now obvious) reason for the error. I have tried several changes to the query and not got anywhere. Please could someone have a look and see if you can suggest the required alteration(s)?
USE tempdb
go
---------------------------------------------------------------
--Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
(
unit_codeVARCHAR(10)NOT NULL
, parent_codeVARCHAR(10)NULL
, CONSTRAINT pk_orgPRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
)
GO
CREATE TABLE dbo.org_peeps
(
unit_codeVARCHAR(10)NOT NULL
, person_codeINTNOT NULL
, CONSTRAINT pk_org_peepsPRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_org_peeps_other_org_peepsFOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
)
GO
CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
SELECT org.unit_code
, org.parent_code
, org_peeps.person_code
FROMdbo.org
LEFT OUTER JOIN
dbo.org_peeps
ONorg_peeps.unit_code= org.unit_code
GO
INSERT INTO dbo.org (unit_code, parent_code)
SELECT 'a', 'y'UNION ALL
SELECT 'b', 'y'UNION ALL
SELECT 'c', 'z'UNION ALL
SELECT 'y', 'z'UNION ALL
SELECT 'z', NULL
--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'a', 1UNION ALL
SELECT 'c', 3
---------------------------------------------------------------
SELECT'This works great....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Add data to INTERMEDIATE levels of organisation
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'y', 4UNION ALL
SELECT 'y', 5UNION ALL
SELECT 'y', 6
---------------------------------------------------------------
SELECT'Since there is now data in the intermediate tables there are ' +
'duplicate outputs....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
---------------------------------------------------------------
SELECT'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTDISTINCT
parents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
DROP VIEW dbo.org_peeps_parents
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
DROP TABLE dbo.org
ENDJust paste and run the code and hopefully it should be reasonably clear. If you need any further info please let me know
View 5 Replies
View Related
Sep 24, 2007
Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
View 6 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
Apr 15, 2008
Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!
View 11 Replies
View Related
Apr 30, 2008
SQL Server 2005.
Schema is not fixed at this stage - small project, schema can be adapted if neccessary.
This needs to be produced in SQL Server - there is no client application.
Hi
I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.
I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".
There is a further table with a related FK declared which associates people with business units.
Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).
I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here:
http://www.dbforums.com/showthread.php?t=1629366
To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.
Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ):
DECLARE @table TABLE
(
unit_codeTINYINT
, parent_codeTINYINT
, headcountINT
, PRIMARY KEY CLUSTERED (unit_code)
)
INSERT INTO @table (unit_code, parent_code, headcount)
SELECT 1, 4, 10 UNION ALL
SELECT 2, 4, 130 UNION ALL
SELECT 3, 10, 93 UNION ALL
SELECT 4, 10, 7 UNION ALL
SELECT 10, NULL, 1
Required output:
unit_codecumulative_headcount
------------------------------------
110
2130
393
4147
10241
Many thanks
View 5 Replies
View Related
Nov 19, 2015
I need a boost to understand how to get started on solving this SQL Query.
TicketId Seq StatusDateTime
T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000
I need to find the total StatusDateTime for each TicketId
I need to find the average StatusDateTime for all TicketIDs
Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
View 6 Replies
View Related
Sep 29, 2015
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
View 9 Replies
View Related
Jul 20, 2005
Terminology question:Is there a term for a set of records related directly or indirectly by keyvalue in several tables? For example, a single invoice record and its lineitem records -or- a single customer, the customer's orders, the order linesfor those orders, the customer's invoices, and the invoice lines for thoseinvoices.I'm thinking the term might be graph, but I'm not at all certain of this.Thanks,Steve J
View 17 Replies
View Related
Sep 24, 2007
Thanks for your time,
How to calculate & save a Parent status [qcStatus varchar(30)] and Alert [alertFlag bit] in dbo.a1_qcParent
based on comparison of its Child records in dbo.a3_qcItems2Fix columns [itemComplete bit] and [alertFlag bit]
Where a1_qcParent[a1_id] = a3_qcItems2Fix[a1_ID]
- Parent CLOSED: if all children [itemComplete] are True
- Parent OPEN: if any child [itemComplete] is False
- Parent ALERT: True if any child row [alertFlag bit] is True
Using sql_Datasource in webpage, but more comfortable in sql... After-Trigger?
Can Parent columns have calculated formula referencing the child table? Please help.
View 1 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Jul 20, 2005
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
Nov 18, 2007
i have this function
it return 0 but the sql statement in the sql query return the right number?how is that
i want to get the number of records any other idea or fix?
Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
Conn.Open()
dr = cmd.ExecuteReader()
dr.Read()userQuota = dr("rec_count").ToString
Conn.Close()
End Function
View 4 Replies
View Related
Jan 14, 2008
Hello All,
I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names. I have tried this SQL statement in the SQL Express and it works very great.
SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName
However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Can you please help me to fix this error?
Thank you, Vic.
View 2 Replies
View Related
Mar 5, 2004
I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row.
Example:
1|"Jane Doe"|"23 Western ave"|...
2|"Jamie Delom"|"5 East Street|...
3|"Nat Girshon"|"5678 Main Street|...
.
.
Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?
View 14 Replies
View Related
Jan 24, 2007
count(foundedamount>0)
does not seem work, how can I count all record and fundedamount>0?
THX
View 7 Replies
View Related
Jan 20, 2008
Hi
i am just an beginner with sql and i am quite sure this will be a simple question
i am looking for a methode to count the number of records how can i do this?
greetz
View 5 Replies
View Related
Mar 4, 2007
I have the following: Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct Dim numRows = dF.Rows.Count Dim dS As Data.DataTable = ResourceAdapter.CountQuery Dim sumRows = dS.Rows.Count DataList1.DataSource = dF DataList1.DataBind() LblCount.Text = numRows LblSum.Text = sumRows numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?Diane
View 5 Replies
View Related
Nov 23, 2007
Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()
I am getting an error that that's no data in the table.
Any suggestions?
View 1 Replies
View Related
Feb 14, 2008
Hello, I am having problems with this query below:
1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
View 1 Replies
View Related
Feb 26, 2008
hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
View 6 Replies
View Related
May 13, 2008
Using linq what would be the most efficient method of counting the number of users in the users table?
View 2 Replies
View Related
Jan 30, 2004
I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?
Thanks!
Justine
View 4 Replies
View Related
Dec 5, 2005
Hi
Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...
i have this so far, and it dosent give me anything...
col1 is varchar 254
SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)
ex of output
place1 100
place2 50
place3 25
...
place11 500
a query that only gives me the place11 number is enough
thx in advance //Mr
View 1 Replies
View Related
