SQL comparison between 3 columns

Question

I have 3 columns in my database. (1) Buy/Sell (2) ID (3) Date and time. For example:

buySel      ID     Date
  1         234    12/12/2014
  1         234    12/12/2014
  2         234    12/12/2014

In buySell the number (1) is represented as buy and (2) is sell. Within the same day if the ID e.g. '234' is bought and sold this should return a error message.

This is what I have done in C#

string connectionString= "connection string goes here";
        string Query = "SELECT COUNT(*) AS sum from databaseTable  WHERE created_time >= DATEADD(hour, 9, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))";
         ........
        SqlDataReader data;
        try
        {

            con.Open();
            myReader = cmdg.ExecuteReader();
            while (data.Read())
            {

                if (myReader[0].ToString() != "0")
                {


                    MessageBox.Show("Error " + myReader[0].ToString());
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }

I managed to compare it with today's date however how will I compare it to the buySell column and the ID column?

score 2 · Answer 1 · answered Apr 24 '14 at 20:50

2

I'm not sure exactly what you want to return. The following will identify all the errors in your data, based on having a buy and sell in the same day:

select id, date
from databaseTable t
group by id, date
having sum(case when buysel = 1 then 1 else 0 end) > 0 and
       sum(case when buysel = 2 then 1 else 0 end) > 0;

answered Apr 24 '14 at 20:50

Gordon Linoff

1,242,037
58
646
786

The date you are using is this comparing against todays date? and you have `from databaseTable t` what does the 't' represent? Because when I execute this query i recieve no records... – user3517479 Apr 24 '14 at 20:58
1

This is not comparing against a specific date. It groups by date to look for your error condition on any date. 't' is a table alias which is unneeded in this query but pretty common and useful in a query as in my answer. – Karl Kieninger Apr 24 '14 at 21:08
But I still get no results when I place it in SQL server 2012 – user3517479 Apr 24 '14 at 21:11
1

@user3517479 . . . I note things that your *question* uses a column called `date`, but your code uses a column called `created_time`. If your so-called `date` field has a time component, then you would be unlikely to get duplicates. You need to extract the date from it, typically using `cast(date as date)`. – Gordon Linoff Apr 24 '14 at 21:16
@GordonLinoff I caught that too. Look at the SQL sample: `DATEADD(hour, 9, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))` which even tries to change a `DATE` to a `DATETIME` so that you can add 9 hours then compare it back to `DATE` values. It would help to know the precision of the date values stored in this table and its test data. – Richard Pascual Apr 24 '14 at 21:20

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

I'll like @GordonLinoff's answer, but haven't compared it performance wise to what you would get from a using EXISTS with correlated subqueries.

create table databaseTable (buySel TINYINT, ID INT, [Date] DATE)

insert into databaseTable values 
(1,234,'12/12/2014'),
(1,234,'12/12/2014'),
(2,234,'12/12/2014')

select id
      ,[Date]
  from databaseTable a
 where exists(select 1 from databaseTable b where b.id=a.id 
                                              and b.[Date] = a.[Date]
                                              and buysel = 1)
   and exists(select 1 from databaseTable b where b.id=a.id 
                                              and b.[Date] = a.[Date]
                                              and buysel = 2)
 group by id
         ,[Date]

In this query the group by serves only as a more efficient DISTINCT.

EDIT:

Since the above statement has been questioned I figure I should examine it more closely. There a lot of discussion here and on the web at large. I think the sum of the guidance would be that GROUP BY is often more efficient then DISTINCT, but not always and DISTINCT is more intuitive a syntax.

Huge performance difference when using group by vs distinct

When the performance of Distinct and Group By are different?

http://msmvps.com/blogs/robfarley/archive/2007/03/24/group-by-v-distinct-group-by-wins.aspx

edited May 23 '17 at 12:28

Community

1
1

answered Apr 24 '14 at 21:00

Karl Kieninger

8,841
2
33
49

@GordonLinoff the query is not returning anything – user3517479 Apr 24 '14 at 21:07
Both of these return the same thing when I run them against the sample data. Familiar with sqlfiddle.com? – Karl Kieninger Apr 24 '14 at 21:09
I have not idea what that means. But http://sqlfiddle.com/#!6/284e1/1 shows both the queries against your sample data. – Karl Kieninger Apr 24 '14 at 21:12
@KarlKieninger . . . `group by` and `distinct` generally have the same performance profile. Some dumb databases might be smarter about index usage for `distinct`, but SQL Server is pretty smart, so I suspect both `group by` and `select distinct` would generate the same execution plan. – Gordon Linoff Apr 24 '14 at 21:12
@GordonLinoff I've on pretty good authority that GROUP BY out performs DISTINCT, so is preferred when returning 1 or few columns. I'll see if I can dig up find the reference since in this case it'll be trivial. – Karl Kieninger Apr 24 '14 at 21:15
@KarlKieninger . . . You might be interested in this discussion (http://stackoverflow.com/questions/10650719/when-the-performance-of-distinct-and-group-by-are-different) and this article (http://sqlmag.com/database-performance-tuning/distinct-vs-group). Be careful, because there are dumb databases out there. – Gordon Linoff Apr 24 '14 at 21:26

SQL comparison between 3 columns

2 Answers2