CODEWITHSUNDEEP
sqlsql-serversql-server-2005
nionios
nionios

Reputation: 189

Multiple OR Clauses in sql server

my code is like this: any ideas about avoiding so many or statements? (sql server)

 SELECT ID, COUNT(*) AS UNIQUE_USERS 
FROM 
    (SELECT Address, ID 
        FROM Table
        WHERE 
        (DateSent between '2012-12-21 00:00:00' and '2012-12-21 23:59:59' or
        DateSent between '2012-12-27 00:00:00' and '2012-12-27 23:59:59' or
        DateSent between '2013-01-03 00:00:00' and '2013-01-03 23:59:59' or
            DateSent between '2013-02-27 00:00:00' and '2013-02-27 23:59:59' or
        DateSent between '2013-03-01 00:00:00' and '2013-03-01 23:59:59' or
        DateSent between '2013-03-07 00:00:00' and '2013-03-08 23:59:59' or 
        DateSent between '2013-03-22 00:00:00' and '2013-03-22 23:59:59' 
)AND
        GROUP BY Address, ID
    ) AS USERS
GROUP BY USERS.ID

Thanks in advance

nionios

Upvotes: 2

Views: 8332

Answers (8)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131730

The canonical SQL solution is to use a table containing the dates you want to test against and do an inner join between your table and the table of dates and return only the source rows that have a match in the dates table.

There various ways you can create the dates table:

  • You can create a real table in the database with the values you want. Usefull if you need to check against the same set of dates.
  • You can create a temporary table in your statement and fill it with the values you need. Only practical if you have a few date values to check.
  • You can pass the dates table as a table-valued parameter to your query. Only available in SQL Server 2008 and later. Usefull if the list of dates is generated by a client application

Gordon's solution is better if you have a small number of dates and is also much simpler to code.

EDIT Since the dates come from a text file, you should import the file in a table using either SSIS or the BULK INSERT T-SQL command and join the tables.

BULK INSERT is preferable because you can combine both the BULK INSERT and your query statements in the same batch. BULK INSERT requires that the file is a simple delimited file and the text data can be converted to the target column's type without explicit conversions. BULK INSERT can handle some conversions but it may become too cumbersome for simple jobs.

SSIS allows great flexibility while importing data but it's YetAnotherExternalTool to use.

Upvotes: 0

Max
Max

Reputation: 7100

If you have large table you could create an indexed view with ID and Date.

CREATE VIEW VIEW_TABLE WITH SCHEMABINDING
AS

SELECT ID, CONVERT(DATE, DATESENT) DateSent
FROM TABLE

GO

CREATE NONCLUSTERED INDEX ixDateView
ON [VIEW_Table] (DateSent)

After

 SELECT ID, COUNT(distinct address)
  FROM View_Table WITH (NOEXPAND)
  WHERE DateSent in (
     '2012-12-21', 
     '2012-12-27', 
     '2013-01-03',
     ...)
  GROUP BY ID

Upvotes: 0

i-one
i-one

Reputation: 5120

create table #dates (DateFrom datetime, DateTo datetime)
insert into #dates (DateFrom, DateTo) values
    ('2012-12-21 00:00:00', '2012-12-21 23:59:59')
    , ('2012-12-27 00:00:00', '2012-12-27 23:59:59')
    etc...


SELECT ID, COUNT(*) AS UNIQUE_USERS 
FROM 
    (SELECT DISTINCT Address, ID
        FROM [Table]
            join #dates on [Table].DateSent between #dates.DateFrom and #dates.DateTo
    ) AS USERS
GROUP BY USERS.ID

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

If you are running SQL Server 2008 or newer where datatype DATE exists, you can change to:

WHERE CAST(DateSent AS DATE) IN ('2012-12-21','2012-12-27','2013-01-03','2013-02-27','2013-03-07','2013-03-22)

If on SQL Server 2005 (as it turns out), you can achieve the same with:

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, DateSent)) IN 
     ('2012-12-21','2012-12-27','2013-01-03','2013-02-27','2013-03-07','2013-03-22)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425378

If you cast the timestamp to date, and since your ranges are full days, logically you can code it as a single IN (...):

...
SELECT Address, ID 
    FROM Table
    WHERE CAST (DateSent AS DATE) IN ('2012-12-21', '2012-12-27', '2013-01-03', '2013-02-27', '2013-03-01', '2013-03-07', '2013-03-22') 
...

Upvotes: 0

Luis LL
Luis LL

Reputation: 2993

SQL2005 or after

WITH Dates AS 
(
    SELECT '2012-12-21' AS DateToCheck
    UNION SELECT '2012-12-27'
    UNION SELECT '2013-01-03'
    UNION SELECT '2013-02-27'
    UNION SELECT '2013-03-01'
    UNION SELECT '2013-03-07'
    UNION SELECT '2013-03-22'

)
SELECT ID, COUNT(*) AS UNIQUE_USERS
FROM dbo.Table
INNER JOIN  Dates ON CONVERT(Date, DateSent) = DateToCheck
GROUP BY ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You could do it as by casting to a date and using in:

  SELECT ID, COUNT(distinct address)
  FROM Table
  WHERE cast(datesent as date) in ('2012-12-21', '2012-12-27', '2013-01-03',
                                   '2013-02-27', '2013-03-01', '2013-03-07',
                                   '2013-03-22'
                                  ) and . . .
  GROUP BY ID

I also modified the query to use count(distinct) instead of a subquery. The results should be the same, assuming that address is never NULL. If it can be NULL and you want to count that, then you can do:

  SELECT ID, COUNT(distinct address) +
             max(case when address is null then 1 else 0 end)

Upvotes: 2

catfood
catfood

Reputation: 4341

The DATEPART function gives you an integer that represents the date part of your column without the time-of-day part. Maybe you could compare on that.

http://msdn.microsoft.com/en-us/library/ms186724.aspx#GetDateandTimeParts

Upvotes: 0

Related Questions