How to make the order by date fine enough in SQL

hi,

I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.

Thanks very much.

Then you're trying to do a select order by datedesc, and group by date, within each group you wanna order by timeasc, right? If so you’re trying to do sorting within grouped records. In SQL2005 you can useRANK Functions, for example:

SELECT*,RANK()OVER(PARTITIONBYCONVERT(VARCHAR,CDATE,102)

ORDERBYCONVERT(VARCHAR,CDATE,108))AS RANK

FROM testP

ORDERBYCONVERT(VARCHAR,CDATE,102)DESC

|||Thanks!

How to make an if in the dataflow ?

I have a dataflow where i import 2 files. The one is the file containing a couple of million records. The other file contains rows with summed values on a specific key.

The file with the millions of records is aggregated on the key, sorted, so that the 2 collums from the files can be compared. I then do a mergejoin on the key and now i have temptable with the (key,sum1,sum2). Now there must not be a difference between sum1 and sum2.
I can make a conditional split where i say ([sum1] - [sum2]) > 0.1 so that i get an output with rows where the diffence is more than 0.1.

My question is now, how do i make an action on that. If that task put out a row or more then do something (send mail task, stop further processing) ?

CgplJust add a "RecordCount" transform to your pipeline. So you count "Error Records". In the Control Flow you can change the "Link" between the task and change the "constraint options" to "Expression". There you can check if the value of the variable you used for the RecordCount is greater then 0. If so you can link to a send mail task or whatever you want...

HTH
Thomas|||Thanks but can you point that out in detail ?|||Ahhh Found out! Thanks|||This may help: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1843.aspx

-Jamie
EDIT: Ahh, except that you already worked it out while I was posting this. Never mind :)

How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote:

How to make 2 columns in datagrid with random records?

Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
Well i found one way that was 2 tables the code ended like this
SqlConnection1.Open()
Dim sqlcon As NewSqlCommand("Select Firstname,spillernavn From rndnames CROSS JOINspillere ORDER BY NewID()", SqlConnection1)
Dim sqlrd As SqlDataReader
sqlrd = sqlcon.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = sqlrd
DataGrid1.DataBind()
Well Well at least it works;)

How to loop through this sql table and display in ms access listbox

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

Here is an example of the table:
USER ID TYPE PAYMENT
=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00

If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00

How would I do this?

thanksRon (pts4560@.yahoo.com) writes:

Quote:

Originally Posted by

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.
>
So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.
>
>
Here is an example of the table:
USER ID TYPE PAYMENT
>=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00
>
>
If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00
>
How would I do this?

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT TYPE, PAYMENT FROM tbl WHERE USER_ID = ?"
cmd.Parameters.Append CreateParameter("@.userid", adInteger. _
adParamInput, , txtuserid.text)
rs = cmd.Execute

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to lock a table or row in sql?

hi, i have an application that updates some records in sql tables, and i want to do a web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table so i dont have concurrency problems.
tnx in advance.Have you tried google? Search fordatabase concurrency.
It's not a simple answer. There are different types of concerrency and different levels of locks. You need to undestand what is out there. Once you get the difference, you'll probably know what to use and how to do it.
Now go. Google it.|||

ok , i will try doing something like in this article..

http://www.15seconds.com/issue/030604.htm

tnx Alex.

how to list the records of a select

Hi world,
Normally we receive the results of a query in several or thousands of rows.
Select * From Clients
--------
Row1. Client1
Row2. Client2
...
Which is the the way to have everything on the same row separated by commas?
Row1. Client1, Client2...

thx
David

DECLARE @.MyStr varchar(8000)

SELECT @.MyStr = ISNULL(@.MyStr, '') + Column1+ ',' + Column2+ ','
FROM Anal
where UserName is not null
print @.MyStr
Thanks,
Ram

How to link datasets for reporting?

I have 2 datasets for a single report. One dataset(1) returns value(s), the
second dataset(2) has many records for each value(s) of dataset(1). Yes, one
to many. On the report I wish to display a result of dataset(1) with a
wrapping text box with the many values of dataset(2), then pagebreak and page
two will be the next result of dataset(1) with the many records of dataset(2)
in a wrapbox etc... I have a key column in both datasets. I need to first
figure out how to link them and use the data on report as I described. thanks
!!Read up on subreports. That is how to solve this problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"T" <T@.discussions.microsoft.com> wrote in message
news:5722C5FE-4DD1-4FAA-ACF8-86FCA2B15ACA@.microsoft.com...
>I have 2 datasets for a single report. One dataset(1) returns value(s),
>the
> second dataset(2) has many records for each value(s) of dataset(1). Yes,
> one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and
> page
> two will be the next result of dataset(1) with the many records of
> dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to
> first
> figure out how to link them and use the data on report as I described.
> thanks
> !!|||I have come across similar scenarios and tried to solved it with two
datasets, but each time I come back to include alla data in one "flat"
dataset and use gropuing instead.
Ex. If you have one dataset with adresses and one dataset with related phone
numbers. Like:
Adress
1, "MyStreet", "MyCity"
2, "MySecondStreet", "MyCity"
Phone
1, 1, "Phone", "+46-123456"
2, 1, "Fax", "+46-8-232323"
3, 2, "Phone", "+46-654321"
4, 2, "Fax", "+46-8-44444"
Then join them into one dataset instead of two, like:
1, "MyStreet", "MyCity", 1, 1, "Phone", "+46-123456"
1, "MyStreet", "MyCity", 2, 1, "Fax", "+46-8-232323"
2, "MySecondStreet", "MyCity", 3, 2, "Phone", "+46-654321"
2, "MySecondStreet", "MyCity", 4, 2, "Fax", "+46-8-44444"
Create a new Report using this Dataset.
Set property "HideDuplicates = true" on the Adress columns.
Create a group with "AddressId" as group value, if you like to SUM some
values one on "adress-level"
This way you "simulate" related datasets.
Regards Martin Bring
**********************
"T" wrote:
> I have 2 datasets for a single report. One dataset(1) returns value(s), the
> second dataset(2) has many records for each value(s) of dataset(1). Yes, one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and page
> two will be the next result of dataset(1) with the many records of dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to first
> figure out how to link them and use the data on report as I described. thanks
> !!

How to Limit the number of rows returned with MSSQL, like the MySQL Limit clause.

hi

I use to work with mysql but now I need mssql. Can somebody tell my how I canwork around LIMIT to select a number of records so I can display 10 records at a time in stead of all?

ThanksI think there is nothing exactly the same as LIMIT. But you can use TOP to get limited records. e.g. TOP 10 will return 10 records but this can not be used like TOP 20,10.

Moreover, you can loop the results to reach to your required result number and start displaying them. But this is not a good approach

I don't know the current implementation of TOP. I read this somewhere.|||I've edited the title of the thread, and moved it to the Sql Server / MSSQL Forums.

Moderator

How to limit conditionally records exported

I apologize if I'm posting this question in the wrong forum.

I have a query that exports data from an Oracle database to an Excel spreadsheet. The application that executes the query is Computer Associates Eureka Report writer. I do not have direct access to the database or any of its objects. With the data in Excel, I run a VBA macro-driven inventory report. The Excel spreadsheet row limitation is not a problem presently, however, the query exports a lot of extraneous data that I don't need. I actually export about a dozen columns of data however I'm only listing 4 in the example below.

Example:

CaseNum ActionDate ActionType ComplCd
1029901 09/08/2006 F 0
1029901 09/11/2006 C 0
1029901 08/18/2006 C 1
1029901 08/17/2006 F 1
1029901 08/01/2006 F 1

When the ComplCd = 1 I only want the query to export one row of data for each CaseNum. Is
there a way to code my query to accomplish this?

ugabulldog

Depending on the version of Oracle server, you can use one of the queries below:

select CaseNum, ActionDate, ActionType, CompICd

from (

select CaseNum, ActionDate, ActionType, CompICd

, ROW_NUMBER() OVER(PARTITION BY CaseNum, CompICd ORDER BY ActionDate DESC?) as seq

from tbl

) t

where (CompICd = 0)

or (CompICd = 1 and t.seq = 1)

-- or

select CaseNum, ActionDate, ActionType, CompICd

from tbl

where CompICd = 0

union all

select CaseNum, ActionDate, ActionType, CompICd

from tbl t1

where CompICd = 1

/*

Assumes that ActionDate is unique for each CaseNum, CompICd combination.

Else use some other appropriate column(s)

*/

and ActionDate = (

select max(t2.ActionDate)

from tbl as t2

where t2.CaseNum = t1.CaseNum and t2.CompICd = 1

)

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
Thanks a lot!
regards,
florencelee
This is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>
|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
Thanks a lot!
regards,
florenceleeThis is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
--
Thanks a lot!
regards,
florenceleeThis is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>