Database Discussion Boards

Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?) [modified]

devvvy13-Jul-10 0:37

devvvy

13-Jul-10 0:37

Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?)

C:\somedir\bcp.exe DEV.dbo.SomeMixedData in C:\somedir\output\output_abc_mixed.txt -n -S SomeSErver -T

Starting copy...

0 rows copied. << Just can't get it working without format file.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

When open with Notepad my sample file resembles:

20100701,1.110000e+000,2.220000e+000
20100702,3.330000e+000,4.440000e+000
20100703,5.550000e+000,6.660000e+000

And my simple table:
CREATE TABLE [dbo].[SomeData](
[Col1] [varchar](255) NULL,
[Col2] [varchar](255) NULL,
[Col3] [varchar](255) NULL
)

dev

modified on Tuesday, July 13, 2010 7:02 AM

sum of a column upto the current row for each row

indian1439-Jul-10 8:31

indian143

9-Jul-10 8:31

Hi All,

How to get sum of a column upto the current row for each row into a different column in SQL Server.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: sum of a column upto the current row for each row

Eddy Vluggen9-Jul-10 8:59

Eddy Vluggen

9-Jul-10 8:59

Hi,

There's multiple ways of calculating a running total. This article[^] shows three of those solutions.

I are Troll Suspicious | :suss:

Re: sum of a column upto the current row for each row

CitrusTech9-Jul-10 9:03

CitrusTech

9-Jul-10 9:03

Not sure how this differs from your earlier question http://www.codeproject.com/Messages/3527850/Complex-Query.aspx[^]

You're looking for running totals.

Data Quality Tools and Data Profiling software

Re: sum of a column upto the current row for each row

Bernhard Hiller11-Jul-10 22:10

Bernhard Hiller

11-Jul-10 22:10

Not a full solution, but a step towards it:
Let us assume a table "Table1" with following columns and values:

ID val total
 1  12 <NULL>
 2  23 <NULL>
 3   2 <NULL>
 4  14 <NULL>

Now you can update the "total" column for each row with a query like the one below for ID=3:

UPDATE    table1
SET       total = tmp.result
FROM      (SELECT   MAX(ID) AS maxid, SUM(val) AS result
                    FROM          Table1
                    WHERE      (ID <= 3)) TMP
WHERE     table1.id = tmp.maxid

Next, replace "3" by a parameter and run the query for every ID value...

Re: sum of a column upto the current row for each row

Mycroft Holmes11-Jul-10 23:21

Mycroft Holmes

11-Jul-10 23:21

bhiller wrote:
run the query for every ID value...

What with a cursor or a while loop, what happens if he has 100k IDs. This is a bad solution, using the running total tools will give a much better result.

Never underestimate the power of human stupidity
RAH

TNS Ping (.NET Library)

mrwatkin9-Jul-10 5:00

mrwatkin

9-Jul-10 5:00

I'm currently maintaining a Visual Basic project that makes use of tnsping. In order to do a tnsping, it uses a ProcessStartInfo instance to make the call as if it were using the command prompt(DOS). I was wondering if a library exists (Oracle.DataAccess maybe?) or if there's something built into the .NET libraries that will allow me to do the tnsping without the ProcessStartInfo. Could anyone point me in the right direction?

Thanks in advance for all of your answers.

sql procedure

Thanusree Duth9-Jul-10 1:22

Thanusree Duth

9-Jul-10 1:22

SELECT DISTINCT ISNULL(CONVERT(VARCHAR,DR_TimeIn,103),'NOT CHECKED')AS DATE,
DR_EmployeeID AS EMPID ,
EM_FirstName +' '+ isnull(EM_MiddleName,' ' )+' '+isnull(EM_LastName,' ') AS EMPNAME,
FROM SG_Daily_Register

to accept the null values of lastname and firstname...I used the 'isnull' which is not correct..Pls correct the procedure

Re: sql procedure

CitrusTech9-Jul-10 2:26

CitrusTech

9-Jul-10 2:26

Which database are we using here?

SQL Server does have an isnull function. For oracle checkout nvl. And for mysql I found this discussion of ifnull http://forums.mysql.com/read.php?98,198593

Data Quality Tools and Data Profiling software

Re: sql procedure

User 27431629-Jul-10 2:40

User 2743162

9-Jul-10 2:40

Try COALESCE instead of ISNULL

modified 19-Nov-18 21:01pm.

Re: sql procedure [modified]

Аslam Iqbal10-Jul-10 5:40

Аslam Iqbal

10-Jul-10 5:40

SELECT DISTINCT DECODE(DR_TimeIn, NULL,'NOT CHECKED',DR_TimeIn) as Date,
.............;

If you are using Oracle DECODE if the best.
OR for other Database Server:

SELECT  IIf(TimeIn is null,'NOT CHECKED',TimeIn) AS [Date],
........;

Re: sql procedure

Jeremy Hutchinson14-Jul-10 7:43

Jeremy Hutchinson

14-Jul-10 7:43

It looks like you just forgot to wrap EM_FirstName in an isnull. Otherwise I don't see why that wouldn't work with MSSQL.

sql function

Thanusree Duth8-Jul-10 20:13

Thanusree Duth

8-Jul-10 20:13

CONVERT(VARCHAR(5), (DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600))
+ '':'' +
CONVERT(VARCHAR(5), (SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60))
+ '':'' +
CONVERT(VARCHAR(5), (DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60))

When I used this.I got output as 0: -13: -16(negative values)
But I will have to get output as 0:13:16
how to remove negative sign from datetime in sql?

Re: sql function

Mycroft Holmes8-Jul-10 23:07

Mycroft Holmes

8-Jul-10 23:07

I may have done you a disservice, seems you have changed to datetime, well done.

Not sure if it will work with datetime but you might try ABS()

Never underestimate the power of human stupidity
RAH

Re: sql function

J4amieC8-Jul-10 23:48

J4amieC

8-Jul-10 23:48

you've still not read the forum guidlines, you've still not learnt to use descriptive subject lines, you've still not learned to keep it in 1 thread.

You'll learn nothing from this response so i'll just paste the correction. Im not going to bother testing it, so check the syntax and try to understand it.

CONVERT(VARCHAR(5), ABS((DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60)))

Re: sql function

Luc Pattyn8-Jul-10 23:49

Luc Pattyn

8-Jul-10 23:49

Maybe if you swap both datetime arguments in DATEDIFF, your problem is gone?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: sql function

Jeremy Hutchinson14-Jul-10 7:52

Jeremy Hutchinson

14-Jul-10 7:52

It looks like you're trying to get the hour:minute:second difference between two datetime values. The simplest way to do that is subtract them, then convert that value to char and apply the appropriate formatting.

declare @t1 datetime, @t2 datetime

select @t1 = GETDATE()

--Select @t2 = @t1 + 1:02:03 (hh:mm:ss)
select @t2 = DATEADD(hour, 1, dateadd(minute, 2, dateadd(second, 3, @t1)))

select convert(varchar, @t2-@t1, 108)

You can find other formatting options here[^]

sql function fot removing negative sign

Thanusree Duth8-Jul-10 18:57

Thanusree Duth

8-Jul-10 18:57

0:-13:-16
Please help me to remove the negative sign.I have to get output as
0:13:16

Re: sql function fot removing negative sign

CitrusTech8-Jul-10 19:30

CitrusTech

8-Jul-10 19:30

Are you asking for something as simple as string functions such as SQL Server's Replace()?

Depending on which database you're using, I'd google "your_database string functions"...

Data Quality Tools and Decision Tree software

Re: sql function fot removing negative sign

Mycroft Holmes8-Jul-10 23:04

Mycroft Holmes

8-Jul-10 23:04

So are you still storing your date data as strings? Or have you done the sensible thing and changed it to a datetime format.

Sorry I already know the answer otherwise you would not be asking this question.

CHANGE YOUR DATE DATA TO DATATIME FORMAT!

Never underestimate the power of human stupidity
RAH

Sum for Trend

indian1438-Jul-10 16:51

indian143

8-Jul-10 16:51

Hi All,

I have a report in which, I have to show the trend values like
sum for the same columns I have to show yesterday, 1week ago and 2weeks ago and 1month ago.

Like for example I have 2 columns and one date column. Now I have to write a query in such a way that
the sum(col1), sum(col2) for 1 day ago, 1 week ago and 1month ago in the same row.

Can anybody please help me in writing this query. Any link or any code snippet will be very much helpfull.

Thanks in advance.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: Sum for Trend

Mycroft Holmes8-Jul-10 23:00

Mycroft Holmes

8-Jul-10 23:00

Break it into 4 queries, 1 for each time period doing the appropriate aggregation for each query, include the common key. Then join the 4 queries into 1 using the common key. I would use table vars to hold the temp data.

BTW I would not put my company name in your sig, it can be incorrectly construed (look it up).

Never underestimate the power of human stupidity
RAH

auto pagination in sql

Ebube8-Jul-10 2:34

Ebube

8-Jul-10 2:34

Hi how do i make sql statement to page the rows six per result

Re: auto pagination in sql

Eddy Vluggen8-Jul-10 3:19

Eddy Vluggen

8-Jul-10 3:19

Hi,

There's an example over here[^] Smile | :)

I are Troll Suspicious | :suss:

Complex Query

indian1437-Jul-10 12:29

indian143

7-Jul-10 12:29

Hi All,

I have to write a query in SQL Server 2000/2005/2008 in such a way that, "the sum of 1st Column 2nd row and 2nd Column 1st row is placed in 2nd column 2nd row".
Any kind of link or any suggestion will be very very helpfull.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Last Visit: 31-Dec-99 18:00 Last Update: 11-May-24 14:30

Refresh

ᐊ Prev 1...597 598 599 600601602 603 604 605 606 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.