Querying by date 1

[belle9]

I'm trying to get a list of orders placed between a specific time period, and I want one result for each date. I am currently getting one result for each time on each date...How can I query the date field to ignore the time part?

 

[Lothario]

Show us your query and some sample data.

-L

 

[belle9]

Here's my query:

SELECT h_orders.wsid, COUNT(*) AS Expr1, sum(h_orders.subtotal),convert(char(11),h_orders.date_Created)
FROM h_orders INNER JOIN
crm_Frontline.dbo.Action_Info ON h_orders.OrdersID = crm_Frontline.dbo.Action_InfrderID INNER JOIN
h_sessions ON crm_Frontline.dbo.Action_Info.ctID = h_sessions.ctID
WHERE (h_orders.date_created BETWEEN CONVERT(varchar(11), '2004-09-15 00:00:00', 20)
AND CONVERT(varchar(11), '2004-10-27 00:00:00', 20))
AND (h_orders.wsid = 3)
AND h_orders.OrderStatus = 1
AND (h_orders.afid = 13)

GROUP BY h_orders.date_Created,h_orders.wsid


and here's the data I'm getting:

wsid Expr1
----------- ----------- --------------------- -----------
3 1 36.9800 Oct 17 2004
3 3 88.7100 Oct 18 2004
3 1 9.2400 Oct 18 2004
3 1 27.5700 Oct 19 2004
3 1 108.1900 Oct 19 2004
3 1 4.4700 Oct 19 2004

 

[Lothario]

Does this work??

SELECT h_orders.wsid, COUNT(*) AS Expr1, sum(h_orders.subtotal),convert(char(11),h_orders.date_Created)
FROM h_orders INNER JOIN
crm_Frontline.dbo.Action_Info ON h_orders.OrdersID = crm_Frontline.dbo.Action_InfrderID INNER JOIN
h_sessions ON crm_Frontline.dbo.Action_Info.ctID = h_sessions.ctID
WHERE (h_orders.date_created BETWEEN CONVERT(varchar(11), '2004-09-15 00:00:00',101 )
AND CONVERT(varchar(11), '2004-10-27 00:00:00', 101))
AND (h_orders.wsid = 3)
AND h_orders.OrderStatus = 1
AND (h_orders.afid = 13)

GROUP BY h_orders.date_Created,h_orders.wsid


-L

 

[belle9]

Nope, I still get:

wsid Expr1
----------- ----------- --------------------- -----------
3 1 52.8600 Oct 17 2004
3 1 71.6400 Oct 17 2004
3 1 10.7800 Oct 18 2004
3 1 15.4900 Oct 19 2004
3 3 68.7600 Oct 19 2004
3 2 195.7200 Oct 21 2004
3 1 20.4600 Oct 21 2004

 

[dbomrrsm]

Group by h_orders.date_Created alone.

[blue]DBomrrsm[/blue]

 

[belle9]

Can't do that though,because I need the wsid in my query.

 

[Lothario]

Try this:

WHERE (h_orders.date_created BETWEEN CAST('2004-09-15' AS datetime ) AND CAST('2004-10-27' AS dateime))

-L

 

[belle9]

same thing

 

[dbomrrsm]

just a thought but try this:

SELECT       h_orders.wsid, COUNT(*)  AS Expr1, sum(h_orders.subtotal),convert(char(11),h_orders.date_Created) AS DC
FROM         h_orders INNER JOIN
                      crm_Frontline.dbo.Action_Info ON h_orders.OrdersID = crm_Frontline.dbo.Action_Info.OrderID INNER JOIN
                      h_sessions       ON crm_Frontline.dbo.Action_Info.ctID = h_sessions.ctID
WHERE                (h_orders.date_created BETWEEN CONVERT(varchar(11), '2004-09-15 00:00:00',101 ) 
                    AND CONVERT(varchar(11), '2004-10-27 00:00:00', 101)) 
                     AND (h_orders.wsid = 3) 
                     AND h_orders.OrderStatus = 1
                     AND (h_orders.afid = 13)

GROUP BY DC,h_orders.wsid

Not sure this will work but wont harm to try !!!


[blue]DBomrrsm[/blue]

 

[SQLBill]

How about:

GROUP BY GROUP BY CONVERT(CHAR(11),h_orders.date_Created), h_orders.wsid

Remember you are trying to GROUP BY:
mm dd yyyy
NOT by
mm dd yyyy hh:mm:ss

-SQLBill

 

[belle9]

I had tried that too, but it wont let me group by DC

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DC'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DC'.

 

[belle9]

SQLBill, thanks! I think it works well now.

 

[dbomrrsm]

Well done Bill - now help me with my instal problem !!

[blue]DBomrrsm[/blue]

 

[belle9]

What if I want the date to display numerically, so that I can sort by it?

 

[SQLBill]

How do you mean numerically?

(For DBomrrsm - I tried, but I'm glad you finally found a solution).

-SQLBill

 

[belle9]

I guess I mean I'd have wanted the dates returned something like this: 09-01-2004...that way the results are sortable. WIth results like Sept, Oct, it sorts them alphabetically, which isn't the correct order.

Thanks...

 

[SQLBill]

Try CONVERT(CHAR(11),h_orders.date_Created, 110)

The 110 is the style option. Refer to the BOL for more information, look up CONVERT.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[belle9]

Thanks much!