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


ADVERTISEMENT

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 View Related

Problem With Reordering Of Rows

May 17, 2005

I am using MSSQL 2000, I need to display a cv page in which a person projects are displayed the way he enters them into the database and he should be able to reorder the projects according to the proirity.  I have no clue how to do this ??? I am a newbie in asp.net,C# field.
I searched info on google and forums but didn't find anything related to my understanding. I'am displaying all his projects using a Projects table in which i have four columns ProjectID, ProjTitle, ProjDirector, ProjDescription. i want the user to be able to reorder theprojects row by moving a task up or down based on priority.Any help will be greatly appreciated .. I am really stuck and frustated  with this task ..please help me friends..
Thank you very much

View 2 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

JOIN Based On LIKE Condition

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

Column Value Based On A Condition

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

Incrementing Based On Condition

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

How To Write Trigger Based On Certain Condition

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

Group By Clause Based On Condition

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

SQL 2012 :: Concatenate And Sum Based On Condition

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

Returns TABLE Based On A Condition

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

Suppressing Subreport Based On Some Condition

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

Transact SQL :: Updating Only One Row Based On Where Condition

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

T-SQL (SS2K8) :: Insert Columns Based On Condition

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

Insert Columns Into Table Based On Condition?

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

Change Cell Color Based On Condition

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

Insert Into Temp Table Based On If Condition

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

Transact SQL :: Get Records Based On Condition In Server

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

How To Make Table Row Invisible Based On Certain Condition

Apr 16, 2008



HI

I have the following scenario in my report.


-The data is displayed in a table
-The table groups by one field
-Each table row calls a subreport
-There is about 6 paramaters in the report
-The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown.
-So i use a custom vbscript funtion to determine if a specific value was selected or not.
This functionality is working fine.

My problem is if the user does not select all the values in the multi select then i want to make the row invisble
and remove the whitespace so that there is not a gap between the other subreports which is shown.

I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.

How can i make the row invisible if the vbscript function that is called returns a false value?

Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)

The Function returns a true or false.

Thanks.




View 3 Replies View Related

Reporting Services :: Sum Values Based On IIF Condition

Oct 9, 2015

I am using SQL server 2012 and Report builder 3.0 to build my report. I have build a  report which product the following table

What I try to achieve is to add 2 calculated field at the bottom of the table which will represent 2 Total value based on a condition

OutTotal field should show the SUM of the Quantity only for ItemStatus=0
InTotal field should show the SUM of the Quantity only for ItemStatus=1

IMPORTANT : I cannot group my data because I need to shown them in a time Wise flow

I have try to insert the first field and define an expression but it gives an #Error. The expression I used is as below

=SUM(IIF(Fields!ItemStatus.Value = 0, Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value,0))
 
The Quantity filed in my table is calculated from expression Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value

View 2 Replies View Related

Getting Column Names And Its Values Based On Condition

Sep 26, 2007



Hi,
I have a table as follows
Table Master
{

Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}

and the data in the table is as follows

Table Master
{

Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}



I want to retrive column names and its values wheb the ID matches to some value.

Can any one please let me know how to do this?
Thanks alot
~Mohan

View 3 Replies View Related

Showing An Image Based On A Boolean Condition

Apr 21, 2008

I have a table object attached to a dataset,

One of the columns is based on a boolean value(bit field) from the database.

I have a check picture and an unchecked picture.

How can I show the selected image based on the result?

links or ideas welcome

View 3 Replies View Related

SQL Server 2012 :: Inserting New Columns Based On Condition

Feb 25, 2014

I have a very simple query like the following…

SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)

From this query, its returning me a result set of something like below:

--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------

RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER

Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…

Something for example (I am not trying to write a condition: my question is how to write it),

if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.

Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…

See the attachment for better formatting...

---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH

View 7 Replies View Related

SQL Server 2012 :: How To Exclude Row From Result Set Based On Condition

Sep 15, 2015

Lets say I have a set of columns as follows:

Table.Name - Table.ID - Table.Code

I want to write a query that will cycle through the results and if it comes across another record that has a matching Table.ID I want to exclude that row from the result set.

I am not all too familiar with how to use either a Case or If..Else Statement within a Sql statement that would accomplish this.

View 7 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

Transact SQL :: Insert Missing Record Based On A Condition

Sep 29, 2015

How do I get the below scenario:

EmpID DepID
12 2
17 3
17 2
13 3

Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.

Desired Output:

EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2

View 5 Replies View Related

How To Generate Condition Based Aggregate/summary Data

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

How To Make Subreport Visible Based On Parameter Condition?

Aug 29, 2007



Hi,

I have a subreport added to the main report and I want to make this report visible only when the parameter value is met.

Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports.

Any help is greatly appreciated.

Thanks,
Sirisha

View 3 Replies View Related

Return Difference Of Two Stamps In Table Based On Some Condition

Aug 10, 2015

I have table that contains below data

CreatedDate                ID             Message
 2015-05-29 7:00:00      AOOze            abc
 2015-05-29 7:05:00      AOOze            start
 2015-05-29 7:10:00      AOOze            pqy
 2015-05-29 7:15:00      AOOze            stop
 2015-05-29 7:20:00      AOOze            lmn   

 and so on following the series for every set of same ID with 5 entries for each ID

I need to Find Maximum interval time for each ID and for condition in given message (between message like Start and Stop) I used below query and it works fine

select Id, max(CreatedDate) AS 'MaxDate',min(CreatedDate) AS 'MinDate',
DATEDIFF(second,min(CreatedDate),max(CreatedDate)) AS 'MaxResponseTimeinSeconds' from Table where Id in (
SELECT distinct Id
from Table
where Message like  'stop')
group by Id

Above query displays max response time for ID A00ze as 20 minutes, but stop message has occured at 7.15. I would need to modify the query to return max response time  as 15 min(from 7.00 to 7.15).

Difference of starttime(where A00ze id started) and stoptime(where stop string is found in message).

View 7 Replies View Related

Adding A Column Name To A Table In Each Of The Databases Based On A Condition

Oct 3, 2007

i have the folowing databases DB1,DB2,DB3,D4,DB5........

i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.


can someone show me the script to do it?


Thanks.

View 3 Replies View Related

Inserting Values Intoto Only Column Based On A Condition

Sep 21, 2007



Hi,
I have at table as foolows
Table Cat3
{

ID,
Update datetime
}


and also have a master table as follows

Table Master
{

ID,
Cat1 Datetime,
Cat2 datettime
}


My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows
Table Master
{

ID,
Cat1 Datetime,
Cat2 datettime,
Cat3 Datetime
}

I would like to insert the data to this column. The sample output is as follows

Before insertng the data of table ;Cat3' into the Master table
Table Master
{

Id Cat1 Cat2
---------------------------------
1 D1 D2
2 D2 NULL
3 D3 D4
}


And The Cat3 table data is as follows

Table Cat3
{
ID Update
--------------------------
1 D5
3 D6
4 D7

}

The final putput of the master table should be as follows

Table Master
{

Id Cat1 Cat2 Cat3
------------------------------------------------------
1 D1 D2 D5
2 D2 NULL NULL
3 D3 D4 D6
4 NULL NULL D7
}



Can any one please let me know the query to achieve this

Thank you very much for your time and support

~Moahn

View 10 Replies View Related

Power Pivot :: Calculated Measure Based On Condition?

Sep 3, 2015

I have a requirement in Power Pivot where I need to show  value based on the Dimension Column value.

If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should displayMy Table Structure is

Table1:-

Table2:-

My Report Output should be look like 

If tried the below option:-

1. Calculated Measure:= If(Table[Category]="CostPrice",[CostValue],If(Table1[category]="SellingPrice",[SalesValue],([SalesValue]-[CostValue]/[SalesValue])))
*[CostValue]:=Calculate(Sum(Table1[Amount]),Table1[Category]="CostPrice")
*[Sales Value]:=Calculate(Sum(Table1[Amount]),Table1[Category]="SellingPrice")

Tried this in both Calculated Column and Measure but not giving me required output.

View 4 Replies View Related

Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table

Feb 16, 2005

Can someone give me a clue on this. I'm trying to insert values based off of values in another table.

I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).

Two questions

1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.

2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.


Thanks in advance, bellow is the code that I have so far:

RB



Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')

View 1 Replies View Related







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