CODEWITHSUNDEEP
sql-server-2008t-sql
DaveN59
DaveN59

Reputation: 3718

How can I select DISTINCT records including DATETIME when DATETIME is different?

I need to pull data for a report from a SQL Server 2008 R2 database that lists all products built for a given day. The records can have multiple entries in the same day, but I only want to count the items one time.

Example record:

SerialNumber VARCHAR(20)
OrderNumber VARCHAR(50)
Price DECIMAL(7,2)
TestDateTime DATETIME

My view query might look something like this:

SELECT DISTINCT OrderNumber, SerialNumber, Price, TestDateTime 
FROM TestTable
ORDER BY OrderNumber, SerialNumber

...and then the report query would look something like this:

SELECT OrderNumber, SerialNumber, Price
FROM TestView
WHERE CONVERT(date, getdate()) = CONVERT(date, TestDateTime)

...except then I get a separate (duplicate) record for each datetime. I need the datetime field as I will be selecting records from the view based on date, but I also want an accurate total of the price fields for a given OrderNumber so I only want to see each SerialNumber once.

Any ideas?

Next question - how do I get the total price for all rows with the same OrderNumber? Maybe that should be a separate question...

Upvotes: 1

Views: 957

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52655

Have you considered just casting it to a date

SELECT DISTINCT OrderNumber, SerialNumber, Price, cast(TestDateTime  as date) testDate
FROM TestTable
ORDER BY OrderNumber, SerialNumber

Upvotes: 2

Related Questions