Obtaning results per Quarters

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

Hello,

Glad there someone helping out on a Sunday.

I created a ranking report listing the total items sold per Salesman
throughout the year from the most to the least.



SELECT COUNT(item_ID) AS Total, Salesman_Lastame, Salesman_ID

FROM items INNER JOIN

salesmen ON dbo.items.Salesman_ID =
dbo.salesmen.Salesman_ID AND

dbo.items.Salesman_ID = dbo.salesmen.Salesman_ID

GROUP BY dbo.salesmen.Salesman_Lastname, dbo.salesman.Salesman_ID





Salesman TotalTY 1Q 2Q 3Q 4Q


John Doe 324 ? ? 0 0

Mary Joe 257 ? ? 0 0

....



Besides the total obtained, I'd like to divide the results in quarters and
hopefully, in months.

Can it be done and if so, how?

Thank you in advance for the help.

Mary
 
Are you doing this is in an MDB or an APD? If this is an MDB, you could use
a Crosstab query. Does your query contain more than one year? If it contains
more than one year, do you want to lump similar quarters together?
 
I'm doing it in a SQL2k and the db is more than 1 yr but I'm limiting only
to 2004 (where sale_date >=20040101). I would like to list separately the
items sold per Q by every salesman.
 
I'm still in a confused state. Access databases usually have an MDB, MDE,
ADP, ADE, or DAP extension (there are a couple others). Are you using an
Access database?
 
Mary,

You can use the CASE expression. Something along the lines of:

SELECT
Salesman_ID,
Salesman_Lastame,
COUNT(item_ID) AS Total,
SUM(CASE WHEN <datefield> BETWEEN '01/01/2004' AND '04/30/2004' THEN 1
END) As 1Q,
SUM(CASE WHEN <datefield> BETWEEN '04/01/2004' AND '06/30/2004' THEN 1
END) As 2Q,
SUM(CASE WHEN <datefield> BETWEEN '07/01/2004' AND '09/30/2004' THEN 1
END) As 3Q,
SUM(CASE WHEN <datefield> BETWEEN '10/01/2004' AND '12/31/2004' THEN 1
END) As 4Q
FROM items
INNER JOIN salesmen
ON dbo.items.Salesman_ID = dbo.salesmen.Salesman_ID
AND dbo.items.Salesman_ID = Dbo.salesmen.Salesman_ID
WHERE
<datefield> BETWEEN '01/01/2004' AND '12/31/2004'
GROUP BY
dbo.salesmen.Salesman_Lastname,
dbo.salesman.Salesman_ID

I have hard-coded the dates into this - if you need them as parameters, you
should use a stored procedure with an input for the year and derived the
dates based upon the year. Also - if your datefield contains times, then
the end range for between must be changed to include the time (e.g.
'04/30/2004 23:59:59') or, increase the day by one.

HTH,

Jeff
 
This solution works for SQL Server but would fail in an MDB (JET/SQL).
That's why I thought it might be nice to know if this was Access.
 
Thank you.

Jeff Williams said:
Mary,

You can use the CASE expression. Something along the lines of:

SELECT
Salesman_ID,
Salesman_Lastame,
COUNT(item_ID) AS Total,
SUM(CASE WHEN <datefield> BETWEEN '01/01/2004' AND '04/30/2004' THEN 1
END) As 1Q,
SUM(CASE WHEN <datefield> BETWEEN '04/01/2004' AND '06/30/2004' THEN 1
END) As 2Q,
SUM(CASE WHEN <datefield> BETWEEN '07/01/2004' AND '09/30/2004' THEN 1
END) As 3Q,
SUM(CASE WHEN <datefield> BETWEEN '10/01/2004' AND '12/31/2004' THEN 1
END) As 4Q
FROM items
INNER JOIN salesmen
ON dbo.items.Salesman_ID = dbo.salesmen.Salesman_ID
AND dbo.items.Salesman_ID = Dbo.salesmen.Salesman_ID
WHERE
<datefield> BETWEEN '01/01/2004' AND '12/31/2004'
GROUP BY
dbo.salesmen.Salesman_Lastname,
dbo.salesman.Salesman_ID

I have hard-coded the dates into this - if you need them as parameters, you
should use a stored procedure with an input for the year and derived the
dates based upon the year. Also - if your datefield contains times, then
the end range for between must be changed to include the time (e.g.
'04/30/2004 23:59:59') or, increase the day by one.

HTH,

Jeff
Click to expand...
 
Back
Top