SUM DATE into months

[Guest]

I have a query that sums qty sold by item and by class within a date range.
I want to create a second query that sums the date by month and year. I've
try formatting the date but no luck. My gaol is to get the data in a format
that I can filter on month and year.

Here is the current SQL query.
SELECT DISTINCTROW dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, Avg(dbo_OELINHST_SQL.unit_cost) AS AvgOfunit_cost,
Sum(dbo_OELINHST_SQL.qty_to_ship) AS SumOfqty_to_ship
FROM dbo_OELINHST_SQL INNER JOIN dbo_ARCUSFIL_SQL ON dbo_OELINHST_SQL.cus_no
= dbo_ARCUSFIL_SQL.cus_no
WHERE (((dbo_OELINHST_SQL.billed_dt) Between [Enter Start Date:] And [Enter
end Date:] Or (dbo_OELINHST_SQL.billed_dt) Is Null))
GROUP BY dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, dbo_OELINHST_SQL.ord_type
HAVING (((dbo_OELINHST_SQL.ord_type)="O"));

 

[Guest]

I used the dbo_OELINHST_SQL.billed_dt as the date field, if it's not the one,
then change it

SELECT DISTINCTROW Year(dbo_OELINHST_SQL.billed_dt) As YearField
,Month(dbo_OELINHST_SQL.billed_dt) As MonthField , dbo_OELINHST_SQL.item_no,
dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, Avg(dbo_OELINHST_SQL.unit_cost) AS AvgOfunit_cost,
Sum(dbo_OELINHST_SQL.qty_to_ship) AS SumOfqty_to_ship
FROM dbo_OELINHST_SQL INNER JOIN dbo_ARCUSFIL_SQL ON dbo_OELINHST_SQL.cus_no
= dbo_ARCUSFIL_SQL.cus_no
WHERE (((dbo_OELINHST_SQL.billed_dt) Between [Enter Start Date:] And [Enter
end Date:] Or (dbo_OELINHST_SQL.billed_dt) Is Null))
GROUP BY dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat,
dbo_OELINHST_SQL.ord_type,Year(dbo_OELINHST_SQL.billed_dt) ,
Month(dbo_OELINHST_SQL.billed_dt)
HAVING (((dbo_OELINHST_SQL.ord_type)="O"))
Order By Year(dbo_OELINHST_SQL.billed_dt) , Month(dbo_OELINHST_SQL.billed_dt)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT Year(O.billed_dt) As BillYear,
Month(O.billed_dte) As BillMonth,

O.item_no, A.cus_type_cd, O.prod_cat, Avg(O.unit_cost) AS
AvgOfunit_cost, Sum(O.qty_to_ship) AS SumOfqty_to_ship

FROM dbo_OELINHST_SQL AS O INNER JOIN
dbo_ARCUSFIL_SQL As A
ON O.cus_no = A.cus_no

WHERE (O.billed_dt Between [Enter Start Date:] And [Enter
end Date:] Or O.billed_dt Is Null)
AND O.ord_type="O"

GROUP BY O.item_no, A.cus_type_cd, O.prod_cat, O.ord_type

Having a NULL billed_dt seems wrong. It will give weird results for the
BillYear & BillMonth (they will both be NULL).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9lRB4echKqOuFEgEQKBdQCg2oRbDvIZKITqpev4F069Sk1UJ8UAn1C+
eopTWynQQCgVtJXqXhV8BUOa
=Do/0
-----END PGP SIGNATURE-----

 

[Guest]

Close but now I'm getting an error "ODBC-data out of range"

I used the dbo_OELINHST_SQL.billed_dt as the date field, if it's not the one,
then change it

SELECT DISTINCTROW Year(dbo_OELINHST_SQL.billed_dt) As YearField
,Month(dbo_OELINHST_SQL.billed_dt) As MonthField , dbo_OELINHST_SQL.item_no,
dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, Avg(dbo_OELINHST_SQL.unit_cost) AS AvgOfunit_cost,
Sum(dbo_OELINHST_SQL.qty_to_ship) AS SumOfqty_to_ship
FROM dbo_OELINHST_SQL INNER JOIN dbo_ARCUSFIL_SQL ON dbo_OELINHST_SQL.cus_no
= dbo_ARCUSFIL_SQL.cus_no
WHERE (((dbo_OELINHST_SQL.billed_dt) Between [Enter Start Date:] And [Enter
end Date:] Or (dbo_OELINHST_SQL.billed_dt) Is Null))
GROUP BY dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat,
dbo_OELINHST_SQL.ord_type,Year(dbo_OELINHST_SQL.billed_dt) ,
Month(dbo_OELINHST_SQL.billed_dt)
HAVING (((dbo_OELINHST_SQL.ord_type)="O"))
Order By Year(dbo_OELINHST_SQL.billed_dt) , Month(dbo_OELINHST_SQL.billed_dt)
--
\\// Live Long and Prosper \\//
BS"D

I have a query that sums qty sold by item and by class within a date range.
I want to create a second query that sums the date by month and year. I've
try formatting the date but no luck. My gaol is to get the data in a format
that I can filter on month and year.

Here is the current SQL query.
SELECT DISTINCTROW dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, Avg(dbo_OELINHST_SQL.unit_cost) AS AvgOfunit_cost,
Sum(dbo_OELINHST_SQL.qty_to_ship) AS SumOfqty_to_ship
FROM dbo_OELINHST_SQL INNER JOIN dbo_ARCUSFIL_SQL ON dbo_OELINHST_SQL.cus_no
= dbo_ARCUSFIL_SQL.cus_no
WHERE (((dbo_OELINHST_SQL.billed_dt) Between [Enter Start Date:] And [Enter
end Date:] Or (dbo_OELINHST_SQL.billed_dt) Is Null))
GROUP BY dbo_OELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbo_OELINHST_SQL.prod_cat, dbo_OELINHST_SQL.ord_type
HAVING (((dbo_OELINHST_SQL.ord_type)="O"));