A Coding Challenge From The Blindman.

May 5, 2006

Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.

Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)

insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'

drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.

Who's up for the challenge?

View 14 Replies


ADVERTISEMENT

Question To Blindman

Jun 16, 2004

I posted a question yesterday about text field compare and I can't open the thread. Not sure why. Can you repost your reply to this thread regarding how to compare two text strings (i.e. two company names)? Much appreciated.

View 3 Replies View Related

Special Thancks To Brett, Blindman And All The Others !

Jun 18, 2004

Just a few words to thanck some of the people
who connect each days to help others solve their problems.

I've finished migrating my programs to SQL Server
and it's been quite easier and quicker than I first tought
mainly because there are some
competent-intelligent-helpful-(funny) members on this forum.

So thank you all, thank you BK and BM

Caroline from France

View 10 Replies View Related

Help In Coding

Jun 23, 2006

 Dim objConn As New SqlConnection        Dim objCmd As New SqlCommand        Dim Value As String = EventCmb.SelectedItem.ToString()        objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"        Try            objConn.Open()            objCmd.Connection = objConn            objCmd.CommandType = CommandType.Text            objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter            objCmd.Parameters.AddWithValue("@Value", Value)    ' Add the @value withthe actual value that should be put. This makes it securer            Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.            If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then                ContactLbl.Text = RetVal.ToString()            Else                ' noting was returned            End If        Catch ex As Exception            Throw ex        Finally            objConn.Close()        End Try
There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "
Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."
It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!

View 1 Replies View Related

SQL Coding

Jul 2, 2002

I'm attempting to copy data from an Epicore server with multiple company
databases to another server. All this data will be placed in a single
table on the receiving server with the addition of an database identifier,
so that the receiving department will know, to which company the data
belongs. I have code that will dynamically create the SQL and then
execute it using EXEC (@SQL), and this works just fine acrosss multiple
databases. I've read in my Transact-SQL Programming book that using
EXEC() is not limited to 255 characters, but it still appears that my
dynamically created SQL is being shortened. The insert statement and the
select statement list each column, and here is that code:

SELECT @CMD = "INSERT ##APMaster(Company, timestamp, vendor_code,
pay_to_code, address_name,
short_name, addr1, addr2, addr3, addr4, addr5, addr6, addr_sort1,
addr_sort2, addr_sort3, address_type,
status_type, attention_name, attention_phone, contact_name, contact_phone,
tlx_twx, phone_1, phone_2,
tax_code, terms_code, fob_code, posting_code, location_code,
orig_zone_code, customer_code,
affiliated_vend_code, alt_vendor_code, comment_code, vend_class_code,
branch_code, pay_to_hist_flag,
item_hist_flag, credit_limit_flag, credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag,
restock_chg, prc_flag, vend_acct, tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time,
doc_ctrl_num, one_check_flag, dup_voucher_flag, dup_amt_flag, code_1099,
user_trx_type_code, payment_code,
limit_by_home, rate_type_home, rate_type_oper, nat_cur_code,
one_cur_vendor, cash_acct_code, city, state,
postal_code, country, freight_code, url, note) SELECT '" + @DBName + "' AS
Company, timestamp, vendor_code,
pay_to_code, address_name, short_name addr1, addr2, addr3, addr4, addr5,
addr6,
addr_sort1, addr_sort2, addr_sort3, address_type, status_type,
attention_name, attention_phone, contact_name,
contact_phone, tlx_twx, phone_1, phone_2, tax_code, terms_code, fob_code,
posting_code, location_code,
orig_zone_code, customer_code, affiliated_vend_code, alt_vendor_code,
comment_code, vend_class_code,
branch_code, pay_to_hist_flag, item_hist_flag, credit_limit_flag,
credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag, restock_chg, prc_flag, vend_acct,
tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time, doc_ctrl_num, one_check_flag, dup_voucher_flag,
dup_amt_flag, code_1099,
user_trx_type_code, payment_code, limit_by_home, rate_type_home,
rate_type_oper, nat_cur_code, one_cur_vendor,
cash_acct_code, city, state, postal_code, country, freight_code, url, note
FROM " + @DBName + "..APMaster"

Can anyone provide any insight to my problem?

TIA,
Chris

View 4 Replies View Related

Coding Help

Oct 31, 2001

Hello,

I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.

Thanks in advance,
Anne

begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date)
select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date
from TEAM3B_PULL_TOTAL_TST A
where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST)
and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST)
and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST)
and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE)
end

View 1 Replies View Related

Need Help With Right Sql Coding.

Apr 28, 2003

Hi im an newbee so im not the best but try to get there some day..



CREATE TABLE table_members

(id int(11) NOT NULL auto_increment,
fornamn char(60) default NULL,
efternamn char(60) default NULL,
address char(60) default NULL,
personnr INT(11) default 0,

smeknamn char(60) default NULL,

epost char(60) default NULL, i must be a @ value in it! any ides?

kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!

losenord char(60) default NULL, verlosenord char(60) default NULL,
How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!

PRIMARY KEY (id)
UNIQUE ('smeknamn');

Any ides are good

View 1 Replies View Related

I Need ASP.Net C# Coding

Mar 12, 2007

Help me...

I need C# Language ASP.Net coding...

i've two column... One for msg another for msgLastposted


in label control view msg(last visitor)....... that msg stored in my db.

Example Table Data
---------------------------------------------
MSg|MsgLastPosed

How edit forum |2007-03-12 10:50:25.747
How to Value Change|2007-03-12 10:56:36.373
Sql Command|2007-03-12 11:00:25.047
User Control|2007-03-12 11:02:10.793
How I can uninstall|2007-03-12 13:07:51.233
-----------------------------------------------

In table have many record..

label control display msg based on last visitor time.. after that again msg
changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..

View 1 Replies View Related

SQL CODING

Aug 28, 2007

Hi all,


I want codeing for to find (subtotal & total)the following

Input

productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal
gh 10 10
ab 10 10
Total


output

productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal 15 10
gh 10 10
ab 10 10
Total 20 20

View 3 Replies View Related

SQL Challenge

Oct 4, 2006

I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!  

View 2 Replies View Related

Here's A Challenge For You All.

Sep 2, 2004

Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.

Just before we continue, this is about backing up the database that ServiceIT connects to.

I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.

It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).

What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:

Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server Daemons

View 5 Replies View Related

Challenge...

Aug 2, 2007



How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)???
Like I have a table employee

Employee
(
colA int
colB int
colC varchar

)



If i run the tsql..
it should give me

Employee
(
colA varchar
colB varchar
colC varchar

)

View 5 Replies View Related

Coding Question

Jul 25, 2006

:This is a segement of VBscript for checking lastest Backup in SQLServer 2000:
'temporary variables to useDim sTemp1Dim i, sTemp2
'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2  end If
If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
 

View 3 Replies View Related

Confused. Need Some Help About SQL Coding

Jun 14, 2007

hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.


selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')



selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')


also is it wise to use Group by and having in sub-queries?

View 2 Replies View Related

Coding In Standard SQL ?

Jul 20, 2005

Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve

View 8 Replies View Related

Coding Question

May 16, 2007

I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.



Thanks

Andrew

View 2 Replies View Related

INNER JOIN Challenge

Jan 8, 2008

Hello guy!

I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script


CREATE PROCEDURE [dbo].[spSearchMember]

@username nvarchar(30),
@searchfor char(1),
@firstage int,
@secondage int,
@countryid varchar(50),
@withpicture nvarchar(5)

AS

IF(@withpicture = 'False')

SET @withpicture = '%[0-9]'

IF (@withpicture = 'True')
SET @withpicture = '%[0-9][^-]'

IF(@countryId = '-1')
SET @countryid = '%'

BEGIN
SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age',
c.CountryName, p.PicFileName
FROM Members AS m
INNER JOIN Photos AS p
ON m.PrimaryPictureId = p.PictureId
INNER JOIN Countries AS c
ON m.CountryId = c.CountryId
WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage)
AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username)
ORDER BY m.BirthDate ASC
END


Thank!!!!!

View 5 Replies View Related

Challenge To All DBAs

Aug 26, 2005

I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.
Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.
My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.
The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Annaâ€?; LastName =  “Williamsâ€? returns result in 0.01 sec. Searching for FirstName “Benjaminâ€?; LastName = “Watsonâ€? returns in 5 minutes.
Any kind of help is welcome and will be highly appreciated.
- Santy            
san.rely@gmail.comNote: Edited to fix white on white font. 

View 1 Replies View Related

Query Challenge

Jun 14, 2001

I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:

Membership table.
Contact_ID
1
2
3

Relate table
1 1
1 3
1 4

Relate Item
1 item1
2 item2
3 item3
4 item4

The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"

Here is the query as it exists now:

select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME

create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)

insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP

while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)

update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)

delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END

I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.

Any suggestions appreciated,

Thx,

Dave

View 1 Replies View Related

Who Won The Oracle Challenge?

Mar 27, 1999

On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.

Does anybody know who won the challenge? Did microsoft win $1 million from oracle?

Tom Mack, MCSE
Database Administrator
Advancia Corporation

View 2 Replies View Related

Interesting Challenge

Jul 12, 2005

We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.

This Db has pretty much 0 normalization present.

IOW, your worst nightmare.

Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.

View 9 Replies View Related

Query Challenge

Sep 7, 2004

Hello Everybody,

I am attaching a picture of what the table should look like before and after the transformation. Can anybody help? Thank you in advance.

View 2 Replies View Related

SQL Parsing Challenge!

Nov 9, 2004

Hi guys!

Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?

thanks
nelo

View 2 Replies View Related

Optimization Challenge

Apr 7, 2006

Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .

--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc

--run code : next 15 line as a batch
declare @lp tinyint
declare @col1 tinyint,@col2 tinyint,@col3 tinyint
set @lp=1
while @lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @lp=0
else
begin
select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@col1 and col2=@col2 and col3=@col3
insert into abc values(@col1,@col2,@col3)
end
end

--only distinct values left in trash table
select * from abc

--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tables

View 3 Replies View Related

SELECT CHALLENGE?

Jun 16, 2008

I have a database with three tables: Employees, Assets, and Recovery.

I have the following SELECT statement:

SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:]
FROM Assets INNER JOIN
Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN
Recovery ON Employees.EmployeeID = Recovery.EmployeeID
GROUP BY Employees.EmployeeID

Here is the challenge:

You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.

If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID

With the above SELECT statement it will only return values that have data created in each of the three tables.

How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?

Any help will be appreciated!


losstww

View 7 Replies View Related

Stumped By SQL Challenge

Sep 19, 2005

Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!

View 4 Replies View Related

Challenge: Can You Optimize This?

Jun 6, 2006

This code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1

View 19 Replies View Related

SQL Challenge Urgent, Please Help

Aug 9, 2006

Hello Expert,Here I am asking your help.I have a table with following data:TaskID ParentTaskID TaskName ProjectName1 1 BA Rail2 22 FA Financial3 1 BA.1 Rail4 1 BA.2 Rail5 22 FA.1 Financial6 22 FA.2 FinancialNow I want the following format:ID ParentID Name1 1 Rail2 1 BA3 2 BA.14 2 BA.25 22 Financial6 5 FA.17 5 FA.2I need to create following hierarchy if I could tranform the data theabove way in the Project Dimension:Rail--BA----BA.1----BA.2Financial--FA----FA.1----FA.2Please help and thanks in advance,Soumya

View 2 Replies View Related

Allow Null Value Challenge

Apr 15, 2008

Hi,

I have created a report with the following input parameters


FromDate (mandatory)
ToDate (mandatory)
Installation (mandatory, Multi Value)
Code (mandatory, Multi Value)
SearchCriteria1 (Allow Null Value)

SearchCriteria2 (Allow Null Value)
The Search criteria input parameters allow the user to search for occurences of keywords in the comments field.

The query looks like this:

SELECT installation, day_, code, from_time, to_time, commentFROM LogWHERE code IN (@Code) AND day_ TO BETWEEN @FromDate AND @ToDate AND installation IN (@Installation) AND UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria1)+'%') OR UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria2)+'%')
The problem is it does not work when both the Search criterias are null. I have searched the web for solutions, but not found it. I came across the use of ISNULL(@parameter, field), but in conjunction with = and not LIKE. Tried to alter the last two lines of my query to

UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria1,comment))+'%') OR UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria2,comment))+'%')

and now it seems to run forever until it fails (approx. 20 minutes). If I specify a keyword in one or both of the search criterias it returns the result set within 30 seconds.

Would appreciate som help here.

View 2 Replies View Related

Interesting Challenge..

Aug 2, 2007

I am making a report in Visual Studio..Now my question is,is there anyway that i can add a tab control for my rdl??Hope to here from anyone out there with a know how..Thanks guys!!

View 13 Replies View Related

TSQL Challenge

Nov 11, 2007

Im calling all the TSQL gurus to help me with this problem.

Tables:
Contracts (ContractID,Lastname, Firstname, ContractValue, Date Created
Amendments (AmmendmentID, ContractID, Lastname, Firstname, ContractValue, EffectivityDate, DateCreated)

Amendments table is related to contracts thru ContractID.

Changes to the contract table is made by creating a record to the ammendments table. when this

amendment becomes effective it should take effect on the contract. When correcting the contractvalue

the user must input the amount to be added or subtracted.

For Example:

Current Contract: C1, PETRELLI, PITER, 150.00, created on Jan. 01, 2007
Ammendment: A1, C1, PETRELLI, PETER, 20.00, effective on Jan. 20, 2007, created on Jan. 10, 2007
Ammendment: A2, C1, PETRELLI, PETER, 15.00, effective on Mar. 01, 2007, Created on Feb. 01, 2007

The currect Contract:
As of Feb.01, 2007: C1, PETRELLI, PETER, 170.00
As of Mar.05, 2007: C1, PETRELLI, PETER, 185.00


The Challenge here is this:
How can you make an SQL VIEW that will show the list of Current Contractsand use this view to create a stored procedure (with a parameter @CurrentDate)?
The SP will show the current Contracts based on the supplied parameter.

Example:
ShowCurrentContracts(Jan. 05, 2007)
Current Contract: C1, PETRELLI, PITER, 150.00

ShowCurrentContracts(Feb. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 170.00

ShowCurrentContracts(Jun. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 185.00

View 11 Replies View Related

Stored Procedure Coding

Nov 2, 2004

Hello,

I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:

select * from Table where ...
and Official in (... problem area ...)

What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.

Any ideas? I don't want to use a dynamic query. Thanks,

Brian

View 5 Replies View Related

Beginner's Question To Coding

Oct 10, 2006

ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:

What is the total weight of different colored parts?

ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:

Select sum pweight
from parts
where distinct pcolor

looks horribly wrong so any help is appreciated. here is another one:

What colored part has total weight greater than 8 units?

select pcolor
from parts
where sum pweight > 8

???
i dunno lol.

there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?

View 2 Replies View Related







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