SUM DATE into months

[mccloud]

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 dbELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbELINHST_SQL.prod_cat, Avg(dbELINHST_SQL.unit_cost) AS AvgOfunit_cost,
Sum(dbELINHST_SQL.qty_to_ship) AS SumOfqty_to_ship
FROM dbELINHST_SQL INNER JOIN dbo_ARCUSFIL_SQL ON dbELINHST_SQL.cus_no
= dbo_ARCUSFIL_SQL.cus_no
WHERE (((dbELINHST_SQL.billed_dt) Between [Enter Start Date:] And [Enter
end Date:] Or (dbELINHST_SQL.billed_dt) Is Null))
GROUP BY dbELINHST_SQL.item_no, dbo_ARCUSFIL_SQL.cus_type_cd,
dbELINHST_SQL.prod_cat, dbELINHST_SQL.ord_type
HAVING (((dbELINHST_SQL.ord_type)="O"));

 

[Ofer]

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

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

GROUP BY O.item_no, A.cus_type_cd, O.prod_cat, rd_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-----

 

[mccloud]

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

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

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