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
ADVERTISEMENT
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
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
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
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
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
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Sep 24, 2014
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
View 0 Replies
View Related
Nov 12, 2014
I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.
View 6 Replies
View Related
Dec 4, 2013
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
View 1 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 25, 2015
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
View 9 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
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
Apr 16, 2014
Is there any way to get the Columns Used in where condition in a procedure using SQL?
View 1 Replies
View Related
Apr 23, 2015
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
View 2 Replies
View Related
Jul 23, 2014
I want to identify rows that go negative but only for 2 cents or more as well as identify rows that 2 or more.
I have this expression that does not work how I want it to work:
CASE
WHEN (SUM(FavUnfavCostChange) < (2/100) THEN 'Less'
WHEN SUM(FavUnfavCostChange) > (2/100) THEN 'More'
ELSE NULL
END AS 'Flag'
But I get:
0.00000815000000000000More -- this is not more than 2 cents, is just a positive number
-0.00094700000000000000Less -- this is not less than 2 cents, is just negative number
-0.00222000000000000000Less -- this is not less than 2 cents, is just negative number
-0.00012250000000000000Less -- this is not less than 2 cents, is just negative number
0.00000000000000000000NULL -- this is zero so null is fine
0.01188576000000000000More -- this is not more than 2 cents, is just a positive number
View 3 Replies
View Related
Oct 6, 2015
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'
[code]....
i am trying to build a dynamic where "or" clause finding difficulties.
View 7 Replies
View Related
Jun 26, 2015
I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.using temp table in the example , so you can copy the t-sql and run as is.
CREATE TABLE #TABLE1(ID INT)
INSERT INTO #TABLE1 VALUES (1), (2) , (3) , (4)
DECLARE @ID varchar(8000) = '2,4'
[code]....
View 4 Replies
View Related
Oct 12, 2015
I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?
**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.
example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia
example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America
Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N
View 3 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
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
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
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
Sep 19, 2006
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
----------- -------------- ----------------------- -----------------------
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
View 6 Replies
View Related
Nov 8, 2015
I have a text file which has rows 7 rows.I want to insert the data into SQL table using ssis In text file we have a column which has values as Y or N...I wanted to take only those rows which are Y...But we have only 6 rows in SQL table.It does not have the column with Y or N.
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