about a WHERE

Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.If you want daily totals per product for the given data, try:
select
convert (datetime, convert (char (8), SaleDate, 112), 112) as SaleDate
, Product
, sum (Price) as Total
from
Sales
group by
convert (datetime, convert (char (8), SaleDate, 112), 112)
, Product
order by
SaleDate
, Product
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.|||This should answer your question..
given that the SaleDate is a datetime (you have seconds in your second
snippet)
DECLARE @.Date datetime
SET @.Date = '20050218' -- no seconds in here
... WHERE SaleDate>=@.Date AND SaleDate<@.Date+1
-- note the >= on LHS and the < on RHS to prevent overlaps
If possible, dont cast the column in your table to compare it as that
precludes the optimiser from using an index s (I hate it when the
optimiser does implicit casts on the column rather than the variable).
Mr Tea
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
> Hi, I think this is an easy stuff but not for me.. I have an application
> running on a production server.
> In my DB I have a Sales Table like this
> SaleDate Product Price$
> 2005-02-18 00:00:00.000 1 500
> 2005-02-18 00:00:00.000 1 100
> 2005-02-18 00:00:00.000 3 200
> Using SP I'm getting a total by Date by product (e.g For SaleDate =
> 2005-02-18 Product 1 = 600, Product 3 = 200)
> Now also I need to Store the times like this
> SaleDate Product Price$
> 2005-02-18 09:37:39.000 1 500
> 2005-02-18 09:30:09.000 1 100
> 2005-02-18 14:20:10.000 3 200
> How should I modify my SP to get the same..I tried this:
> Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
> CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
> but it does nothing, the SP return a total for each row
> thks.
>
>