Delete Multiple Rows One At A Time Based On A Condition

Aug 28, 2007



Hi,

I have the following scenario :
CustomerDetail
customerid
customername
status
app_no

[status = 0 means customer virtually deleted]

CustomerArchive
archiveno [autoincrement]
customerid
customername
status


At the end of the month, I have to physically delete customers. I have written two stored procs:

proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0

proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no

It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'

How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.

Vidkshi

View 15 Replies


ADVERTISEMENT

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

How To Delete Existing Data Based On Where Clause Condition

Feb 18, 2014

I have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.

1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.

How i need to proceed on this before 10 records are not getting deleted and new records adding for that one

My Example Code :

MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Randomly Delete Records Based On Some Condition

Mar 19, 2014

create table #sample
(
Name varchar(100),
value int

[code]....

From that I wanted to delete some records based on following condition. randomly select any number of records but sum(value) = 125 and name = xxx

View 2 Replies View Related

Reordering Rows Based On A Condition

Aug 20, 2007

Hi,

I have two tables : Students and StuHistory. The structure of the Student table is as follows :


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Student](
[RID] [int] NOT NULL,
[Class] [int] NULL,
[Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubSection] [int] NULL,
[RollNo] [int] NULL,
[DesiredRoll] [int] NULL,
[TrackingNo] [int] NULL,
[Original_rollno] [int] NULL,
[StudentStatus] [int] NULL
)
END
GO


A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0.
[StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted).

After every fortnight, i have to run a batchquery that does the following:

1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student.

2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno


So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted.


How do i write query for this? I have been struggling.

I thought of posting this as a new post as it was mixed in the previous post.

Script :

INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (3, 1, N'A', 1, 3, 1, 1,0,1)


INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (4, 1, N'A', 12, 1, 1, 0,-1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1)


INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1)


INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 )
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1)


Thanks.

View 22 Replies View Related

T-SQL (SS2K8) :: Delete All Rows Satisfying Certain Condition From Table A And Related Records From B And C

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

Multiple Rows Per Condition

May 31, 2014

I am using the following query to retrieve the top five customers from a MySQL table where 'accmanid' 1:

SELECT accmanid, custid, SUM(billone + billtwo) AS
total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 5

This outputs five rows ordered by the total bill where accman = 1.

Here is my question:

How can I write the query to output five rows for EACH accmanid; Im not sure how to do such without the WHERE clause.

View 2 Replies View Related

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.

Aug 7, 2007

Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:

I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:

(AISC_Shapes_Table)

AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100

(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)

I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:




Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))



It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.

Regards,

Steve G.



View 4 Replies View Related

SQL 2012 :: How To Insert Multiple Rows With Condition

May 19, 2015

Create table #table (id int identity , from_country varchar(20) ,
to_country varchar(20),noofdays int, datetravel datetime )
insert into #table(from_country,to_country,noofdays,datetravel)
values
('Malaysia','India',2,getdate()-99),
('India','Singapore',4,getdate()-88),
('Singapore','China',5,getdate()-77),
('China','Japan',6,getdate()-66),
('Japan','USA',7,getdate()-55)
select * from #table

I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.

Ex :
#table
1MalaysiaIndia22015-02-09 02:04:09.247
2IndiaSingapore42015-02-20 02:04:09.247

[code]...

In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.

View 2 Replies View Related

Deleting Rows From Multiple Tables On A Condition

Oct 10, 2007



Hi,
I have different tables with the same schema as follows

ID Name
-----------------




<Table 1>

ID Name
-------------------
1 Name1
2 Name2
3 Name3


<Table 2>

ID Name
-------------------
1 Name1
4 Name4
5 Name5

I just want to delete the row where ID = 1 from these tables in one query ? Is it possible??

Thanks
~Mohan

View 6 Replies View Related

How To Delete Records Based On Time?

Oct 8, 2003

Hello

I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..

Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat=server>

void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;

conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>

View 6 Replies View Related

Power Pivot :: Measure Count Rows - If Condition - Multiple Tables

Nov 7, 2014

I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:

=CALCULATE(COUNTROWS(incident),
        apparatus[Incident Response Time] >-1 ||
        apparatus[Incident Response Time] <320,
        uv_901APP_TYPE[Description]="Engine",
        uv_901INCIDENT[Top_Category]="Fire"

[code]....

View 13 Replies View Related

How Can I Delete All Rows In A Table At Same Time

Oct 6, 2006

hi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

View 5 Replies View Related

Delete Multiple Rows With 2 Ids

Aug 9, 2006

Hello All,
I have a table:
idSurrogate int identity
id1 int
id2 int

id1 + id2 is a unique index

I need to delete multiple rows from the table given a list of id1 and a list of id2
In other words
@id1List = '10,20,30'
@id2List = '1,3,5'
I need to delete these 3 rows from the table
1) @id1=10 and @id2=1
2) @id1=20 and @id2=3
3) @id1=30 and @id2=5

I am a bit lazy today - can anyone help out with a delete sql stmt

Thanks!

View 11 Replies View Related

Delete Multiple Tables At A Time

May 31, 2008

 
Hi: I have 3 tables namely:
1  Category(CategoryID(int), CategoryName(varchar),
2  SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName)
3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar))
how to delete correspoding subcategories of category from  SubCategory,Productlist  tables using triggers
Ex: Category :TV   Subcategory:ColorTV,Plasma,LCD...Plz Send me the query....
Thanks
 
 

View 1 Replies View Related

Can I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

How To Delete Rows From Multiple Tables In SQL

Oct 23, 2007

I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong.
So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.

Thanks,

View 6 Replies View Related

Transact SQL :: Split Rows Based On Time Difference More Than 1day

Aug 5, 2015

equipmentid downtimestartdate downtimeenddate  dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00            69
b6e4 2015-03-18 13:00 2015-03-20 04:00            39

i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)

equipmentid downtimestartdate downtimeenddate dowtime
a3er          2015-03-15 02:00 2015-03-15 24:00            22 
a3er          2015-03-16 00:00 2015-03-15 24:00            24
a3er          2015-03-17 00:00 2015-03-15 23:00            23

[code]...

View 2 Replies View Related

Combining Multiple Rows Based Off Criteria

Mar 21, 2006

Hello again,

Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:


Code:


Table A

ID DSN VN AX Diag
1111296.54
3212318.00



Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.


Code:


Table B

DSN Patient_id
100000001
200000001



So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.


Code:


Table A

ID DSN VN AX Diag
1111296.54
3112318.00

Note: The second rows DSN changed to 1 from 2




Code:


Table B

DSN Patient_id
100000001
(Duplicate row removed with same patient_id)



The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.

Table A:

DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)

Table B:

Second row with same DSN must be removed.

Any takes, ideas? I need to do this on a couple thousand rows....

Thanks, and im happy to clarify if needed.

View 1 Replies View Related

Multiple Rows Combined Based On A Column

Sep 6, 2011

I have a table with two columns refid and name and it has the following values

1 tom
1 jim
2 bob
1 bob

I need a resultset that would have the following values

1 tom, jim, bob
2 bob

I have tried couple of things one being:

DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist

But I am looking for a resultset with a unique refid and all the names comma separated for that refid.

View 2 Replies View Related

Split A Row Into Multiple Rows Based On Column

Sep 5, 2014

I need to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

View 2 Replies View Related

Calculating Percentages Based On Multiple Rows

Oct 2, 2014

I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.

Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?

Category Example

IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice

1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00

Product Example

1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99

Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,

[Code] ....

I use Microsoft SQL 2008

View 2 Replies View Related

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]

-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.

[Code] ...

*** Getting this error below when executing the code ***

Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.

View 9 Replies View Related

Updating Multiple Rows Based On Sums From Another Table

Apr 12, 2007

Hello All

I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.

I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE rptArFLSalesByCustItemized_sp
@custId pCustID,
@dateFrom datetime,
@dateThru datetime,
@itemIdFrom pItemId,
@itemIdThru pItemId
as
set nocount on

-- define some variables for previous year
declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime

-- set defaults
SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem))
SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem))
SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
SET @LYthru=DATEADD(YEAR, -1, @dateThru)

-- create small temp table to hold customer info
Create Table #tmpArCustInfo
(
custId pCustID,
custName VARCHAR (30),
)
-- populate customer temp table with info
Insert into #tmpArCustInfo
select custId, custName
from tblArCust
WHERE custId = @custId


-- create a temp table to hold the Data for each Item
Create Table #tmpArSalesItemized
(
itemId pItemId,
productLine VARCHAR (12),
pLineDesc VARCHAR (35),
descr VARCHAR (35),
LYQtySold int,
LYTDQtySold int,
QtySold int,
LYTDsales money,
totalSales money,
LastInvDate datetime,
)

-- populate the temp table with all of the inventory items
insert into #tmpArSalesItemized
select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL
from tblInItem ii, tblInProductLine ip
where ip.productLine = ii.productLine
AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru

-- update table with this years quantities
update #tmpArSalesItemized
SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd
where TransId IN (select TransId from tblArHistHeader where custId = @custId)
AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru)
AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
GROUP BY hd.partId
)

-- Return the temp tables results
select * from #tmpArSalesItemized, #tmpArCustInfo

drop table #tmpArSalesItemized, #tmpArCustInfo

return


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.

Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.

Thanks
Jamie

View 1 Replies View Related

Select Rows Based On Multiple Date Conditions

Jun 5, 2008

Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

View 2 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

Select Multiple Rows Based On Date Interval From Table

Apr 7, 2015

I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:

Original:

Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20

Resulting view:

A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01

I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.

View 1 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

Transact SQL :: Get Multiple Rows Based On Comma-separated Ntext List In On Column?

Jun 2, 2015

I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122).  I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.

This obviously works:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')

But this does not:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)

View 5 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved