What is the sql script to have dates sum as quarterly? 2

[drussum]

If I have a date that is stored in short date format, 02/10/02 for a field called [Plan Del Date] and I have another field that is called [Back Order]. How do I get a return that has quarterly summaries along the top, like Qtr1, Qtr2, Qtr3, Qtr4, and then below would show the number of items on back order.

I tried to use crosstab and it does not let me run a qry on only one item taken from a drop down list on another form.

Does anyone know what the sql way is to do this??

 

[tlbroadbent]

You can use the Datepart function to determine the quarter.

datepart("q",MyDAte)

Example: Extract quarterly order count by customer in year 2001.

SELECT
Year([OrdDate]) AS [Year],
OrdersTbl.CustID,
Sum(IIf(DatePart("q",[OrdDate])=1,1,0)) AS Qtr1,
Sum(IIf(DatePart("q",[OrdDate])=2,1,0)) AS Qtr2,
Sum(IIf(DatePart("q",[OrdDate])=3,1,0)) AS Qtr3,
Sum(IIf(DatePart("q",[OrdDate])=4,1,0)) AS Qtr4
FROM OrdersTbl
WHERE Year([OrdDate])=2001
GROUP BY
Year([OrdDate]),
OrdersTbl.CustID;
Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[drussum]

where do I type all this in the design view? or do I need to write a new qry maybe and just paste it into the sql view with my field names?? Thank you for your time and help.

 

[tlbroadbent]

It is probably easiest to copy and paste into SQL view.

You can put each item in the select list as a different column or field in the designer grid. Make the query a totals query. Setup the Group and Summ properly.
[tt]
Field: Year: Year(OrdDate) CustID QTR1: IIf(DatePart("q",[OrdDate])=1,1,0) ... etc.
Table: OrdersTbl OrdersTbl
Total: Group By Group By Sum
.
.
Criteria: 2001[/tt] Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[drussum]

Thanks for your help. I tried what was above and am not comming up with the correct count. Below is what I tried: [back Order] is the field that I am trying to sum qtrly. and [Plan del Date] is the field the dates is based on.
Thanks for your patience.



qtr1: IIf(DatePart("q",[Back Order])=1,1,0)

 

[tlbroadbent]

If [Back order] is the field you want to sum, you cannot use it in a date function. You need to use the date - such as [Date Ordered] in the datapart function. Substitute the coorect name in the function.

You should use the [Back Order] as the True result of the IIF function and zero as the False result.

qtr1: IIf(DatePart("q",[Date Ordered])=1,[Back Order],0) Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[drussum]

Thank you very, very much. That worked great. You really saved me allot of time and headache. I will be able to use your solution many times again in the future. Thanks again for your generosity.

 

[drussum]

what is the select statement to specify which field and the specific value in that field?


for example: I have a field called [CostCenter]and it has either one of two values it is always either a "1" or a "2" never both and never empty. I am trying to use the union qry above however i do not want all the records only some.

 

[tlbroadbent]

I'm unsure what you mean. I don't know which UNION query you refer to. If you refer to the summation query, all you need to do is add the criteria to the WHERE clause.

WHERE Year([OrdDate])=2001
AND CostCenter="1"

Let me know if you have something different in mind.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[drussum]

the union qry I have is returning both "1" and "2" records in the [cost center] field. what I need is the "1"s in one field and the "2"s in another field. I am sorry if this is not a clear explanation. I will try again if this doesnt work. Thanks again for your reply.

 

[drussum]

it starts with an oracle table that stores accounting information with [cost center] [Year] and then the months [January] [feb] [Mar] and so on.

The cost center is either a "1" or a "2".
so the current qry shows like this:

all the records sorted by "1" and then the months accorss and then all the records sorted by "2" followed by all the months. I am trying to get all the records to show the "1" in one column and the "2" in another.

 

[drussum]

?

 

[drussum]

tlbroadbent I sent you an email with a better explanation and a sample qry that I have been using.