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
ADVERTISEMENT
Jun 14, 2014
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
View 2 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
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
View Related
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
Jun 14, 2006
Hi
I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns.
I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table.
What all the steps i need to create in SSIS to get this results ?
Thanks
Kumaran
View 2 Replies
View Related
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
Feb 22, 2008
My current report has around 40 subreports corresponding to tests executed by the user. There is a different sub report for each of the tests executed. I first developed the report by just putting the subreports directly on the main report. The problem is if some of my tests are not executed by the user I would like the subreports to not appear in the report.
I have tried doing this two different ways. First I tried creating a table and attaching that table to a dataset that I created that returns all the tests that were executed by the user. I then set up a filter that specified that the name for a specific subreport had to equal the test name. I then dropped the sub report for that particular test into the table. The idea is there would only be one row when data is available and no rows when no data is available so the subreport would not appear. When the data is available the subreport appears great. When it is not available and there is no item in the dataset for that test I get an error from SQL that states there was an internal reporting error and to check the SQL server log for more information. I know my table is set up correctly because when I replace the subreport with text the text will appear only if data is available for that test and the text does not appear if no data is available. I get no errors until I add the subreport and remove the data from the database so the filter does not return any items. Is there a reason why I would be getting this error? I am still pretty green using reporting services so I do not even understand how to get the log from the server to determine why this error is occuring.
Secondly I have tried to use an expression to hide the subreport using the visibility tab for the report. I was able to get this to work by creating a dataset that returns 1 if a specific test has been executed and no rows if not. I then created an expression that would return true if the dataset returned 1. This worked okay in hiding my subreport but I would then have to create 40 datasets, one for each test. Is there anyway to write an expression that would return true if a dataset contained a row with a specific test name? I have not been able to figure this out.
Can someone either help me fix the problem I am having with the table approach or write an expression that would work in the visibility setting for each sub report that did not require 40 datasets? Or is there another approach that I am not even thinking of that might work?
Thanks so much,
Josh
View 6 Replies
View Related
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
Aug 11, 2014
i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
EG Existing groups :
GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998
If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.
Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')
Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
--Existing tasks
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)
-- New tasks
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')
This gives us 6 unassigned tasks and a uneven group assignment
GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2
This means the new tasks will be assigned like this
TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3
View 5 Replies
View Related
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Nov 18, 2003
OK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
View 1 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
Mar 28, 2014
I given my requirement below:
select * from table where (here if account_no = '123456' then name <> 'KING')
View 7 Replies
View Related
Feb 5, 2015
I want to add an exception to the WHERE clause below.
Something like:
IF @IDHuis = 'WH' THEN
add the extra condtion:
AND D.AfdelingZPT <> 'A01'
-- HRS VAST
INSERT INTO @T (JAAR, WEEK, HrsVast)
[Code] .....
View 9 Replies
View Related
Jun 23, 2008
Hi ALL,
Well, i have one view:
create view vwdate
as
select custno, custname, city, date
from salestab
where date = ????
well, this question mark...i have to fill..
like i want if month is june then i want for whole may month results of custno, custname and city..
if month is july then i want results for whole june month..
means for previous month results i want whatever the date is for that particular month..
Example:
if date='06/15/08'
then results will be
custno custname city date
---------------------------------
1 AAAA NJ 05/01/08
2 SDS CT 05/04/08
........................ 05/18/08
.......................... 05/30/08
if date='06/30/08' OR '06/1/08' then also results will be same..
so i want for previous month results..
so can anyone help me to get the correct condition in where clause?
Thanks ALL..
View 2 Replies
View Related
Nov 7, 2007
Hello
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField
FROM myTable
WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Kind regards
Jens Ivar
View 2 Replies
View Related
Apr 6, 2008
I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join.
Table 1 -> TRAFFIC (Time, SourceIP)
Table 2 -> IP_ROSTER (IP2OctetRange, Administrator)
I've tried the following, but it does not seem to work:
SELECT TOP 100
SOURCE_IP,
r.IP2OctetRange,
r.Administrator
FROM TRAFFIC
LEFT JOIN IP_ROSTER AS r
ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'
View 8 Replies
View Related
Nov 20, 2013
I have a column with serial numbers 1, 2,3,4,....etc...I want to have P1 for first 4 serial numbers to be displayed in a separate column beside Serial number column and for the next four serial numbers (5,6,7,8) it should be P2 and like that I want till P13 and for next again it should start with P1(I mean after P13 it should give again P1 instead of P14) and continue the same process.
Column name of Serial number is [S.No]
table name for example is #temp1
View 3 Replies
View Related
Aug 16, 2007
Hi,
I am facing a challenge and hope some one can help me with the query.
I have a school. School have classrooms. Classrooms are divided into various sections (Section A, Section B and so on) . Sections have subsections. Every student is allocated a rollnumber in that section.(Subsection is just for dividng the sections. it has no other use.) Now the student is given a choice to specify his own roll( DesiredRoll) in that section. If two children select the same rollno, then the system internally allocates a trackingno.(There can be multiple roll no as these are allocated manually by admin). Let me demonstrate this:
So when the first entry is made in the db, and let us say that the section to be allocated is A, RollNo is 1 and the DesiredRoll is 1
Student 1 Section A Subsection 1 Roll No 1 DesiredRoll 1 TrackingNo 0
The second entry is made in the db and let us say the section to be allocated is A, RollNo is 2 and the DesiredRoll is 2
Student 2 Section A Subsection 1 Roll No 2 DesiredRoll 2 TrackingNo 0
Now let us say there is a 3rd entry the section to be allocated is A, RollNo is 3 but the DesiredRoll is 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 1
Let us say there is a 4th entry is made, the section to be allocated is A, RollNo is 4 but the DesiredRoll is again 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 2
Similarly this logic will work for different sections. How will I write a query so that I can detect this scenario and increment the tracking no or allocate a tracking no of 0 if there is a new entry made in that section
The structure of the 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
)
END
GO
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (1, 1, N'A', 1, 1, 1, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (2, 1, N'A', 1, 2, 2, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (3, 1, N'A', 1, 3, 1, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (4, 1, N'A', 1, 4, 1, 2)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (5, 1, N'A', 12, 3, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (6, 1, N'A', 12, 4, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (7, 1, N'B', 5, 1, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (8, 1, N'B', 5, 2, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (9, 1, N'B', 5, 3, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (10, 1, N'B', 10, 1, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (11, 1, N'B', 10, 2, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (12, 1, N'B', 10, 3, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (13, 1, N'B', 11, 1, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (14, 1, N'B', 11, 2, 0, 0)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (15, 1, N'B', 11, 3, 0, 0)
Thanks
View 4 Replies
View Related
Aug 6, 2015
create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4) ,(1,1,2,2015,3);
create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');
I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this
that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked
I want to update above information means unchecked alredy check data should delete and new check data add , already check data update
I have use merge statement but problem is that at single time only one row execute that is check because data come from data gridview.
View 5 Replies
View Related
Nov 8, 2005
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
View 3 Replies
View Related
Jan 28, 2015
I have a table where I need to concatenate and sum based on conditions given below .
CREATE TABLE #DimOrder
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[OrderID] [INT] NOT NULL,
[SalesRegion] [VARCHAR](5) NULL,
[OrderType] [INT] NOT NULL,
[Code] ....
Example
SELECT 1296 AS OrderID, 2547 AS ProjectID,364 AS ProductID,6 AS FormatID,66 AS MEdiaTypeID,'Fast Track,Fast Track' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'LTO4_800' AS MEdia ,41.67000 AS aPPLIEDRATE
IF OrderID,PrjectID,ProductID,FormatID,MediaTypeID AND AppliedRate AND ProductComment IS different , THEN I want TO concetenate ProductComment AND sum AppliedRate
Example
SELECT 1207 AS OrderID, 2351 AS ProjectID,364 AS ProductID,6 AS FormatID,32 AS MEdiaTypeID,'no charge,final volume' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'3590E' AS MEdia ,55.56000 AS AppliedRate
View 2 Replies
View Related
Jul 20, 2005
HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....
View 1 Replies
View Related
Mar 7, 2007
I have one main report which has 5 subreport. i dont want to show all the sub reports all the time. i want to suppress any subreport based on some conditioned. can i do it in case of the SSRS. How?
View 2 Replies
View Related
Sep 16, 2008
How can I update only one row doing an update query?
For example:
update from table1
set category = 'C'
where country = 'Brazil'
One want to update only one row with country = 'brazil'
View 5 Replies
View Related
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
Jun 17, 2015
I have SQL statement like this ..
CREATE PROCEDURE [dbo].[GetProspectBySearch]
@StartRowIndex AS INT,
@MaximumRows AS INT,
@ModuleType AS VARCHAR(50) = NULL,
@ProspectCategory AS VARCHAR(50) = NULL,
@ProspectsWithoutContact AS BIT = NULL
AS
BEGIN
IF @Status = 'All'
BEGIN
SET @Status = NULL
END
[code]....
1) How do I can write count and paging statements in to single statement ?
2) There is parameter @ProspectsWithoutContact is using for differentiation for count and paging statements. If @ProspectsWithoutContact is "1" then this clause ( AND ContactPerson = NULL AND TelNo = NULL AND FaxNo = NULL) will be added in to both count and paging statements. How do I can write this in to single statement with out IF ELSE ?
write normal SQL with out dynamic SQL ?
View 3 Replies
View Related
Aug 15, 2015
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.
View 4 Replies
View Related
Jan 30, 2015
My requirement is below.enhancing the T- sql query as I was told not to use SSIS.
Insert data from Table A to Table B with conditions:
1. Truncate gender to one character if necessary.
2. Convert character fields to uppercase as necessary.
3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.
In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '
default outputname :Consider Res Address; Outputname:Consider mail address.
and mapped as:
(Table A) mail_street_address---street address(Table B)
(Table A) mail_city----------------City(Table B)
(Table A) mail_Zip----------------Zip(Table B)
(Table A) mail_state-------------state(Table B)
(Table A) res_street_address--street address(Table B)
(Table A) res_city---------------City(Table B)
(Table A) res_Zip----------------Zip(Table B)
(Table A) res_state--------------state(Table B)
I want to do the same with T-sql code too:
I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):
Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix
[code]....
View 2 Replies
View Related
Feb 10, 2015
I have a table in which I would like to change the color of some of the cells based on a condition.
For Example:
IDStartEndDowntime
12/9/15 13:28:412/9/15 13:28:46#VALUE!
22/9/15 13:29:032/9/15 13:29:120:00:17
32/9/15 13:29:172/9/15 13:29:210:00:05
42/9/15 13:29:272/9/15 13:29:310:00:06
When the value of Downtime is greater than 0:00:10 I would like to have the background of that cell to be Red and its font bold. How to accomplish this in query?
View 3 Replies
View Related
Apr 12, 2006
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi
View 5 Replies
View Related
Nov 22, 2015
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),
[Code] ...
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
[Code] ...
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
Based on this, the desired output is below:
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
[Code] ...
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
order by case when pr.status=2
then 1 when pr.status=4 then 2
when pr.status=3 then 3
when pr.status=1 then 4 end ,pr.sdate
)pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
View 7 Replies
View Related
