￥￥￥￥￥￥￥￥￥￥看看？发过没有人回答

[xxsteven]

我有一个表,里面有几个字段
1.产品名称2.型号规格3.数量4.出入库情况5.日期6.金额7.仓库类别.
我现在要求查一个结存数量和结存金额.现在有一个:
SELECT 产品名称,型号规格,仓库类别,日期,SUM(数量*CHARINDEX('入库', 出入库情况)) AS 入库数量,
SUM(数量*CHARINDEX('出库', 出入库情况)) AS 出库数量,
SUM(数量*CHARINDEX('入库', 出入库情况)) - SUM(数量*CHARINDEX('出库', 出入库情况)) AS 数量结存,
SUM(金额*CHARINDEX('入库', 出入库情况)) AS 入库金额,
SUM(金额*CHARINDEX('出库', 出入库情况)) AS 出库金额,
SUM(金额*CHARINDEX('入库', 出入库情况)) - SUM(金额*CHARINDEX('出库', 出入库情况)) AS 金额结存
FROM 产品
WHERE 日期 between '1999-01-01'and '2002-12-31'
GROUP BY 产品名称,型号规格,仓库类别,日期

以上的这个sql不包括下面的要求
要求如果日期按年就要显示每一个月的结存和总的结存,按月就要显示每天的结存和总的结存.
现在我不知道这样的一个表可不可以实现上面的要求?如果可以请大家谈谈,如果不行请大家说出原因,一样加分..

[xxsteven]

师傅，朋友快来看看

[王集鹄]

SELECT 产品名称,型号规格,仓库类别,DATEPART(month, 日期) AS 月份,SUM(数量*CHARINDEX('入库', 出入库情况)) AS 入库数量,
SUM(数量*CHARINDEX('出库', 出入库情况)) AS 出库数量,
SUM(数量*CHARINDEX('入库', 出入库情况)) - SUM(数量*CHARINDEX('出库', 出入库情况)) AS 数量结存,
SUM(金额*CHARINDEX('入库', 出入库情况)) AS 入库金额,
SUM(金额*CHARINDEX('出库', 出入库情况)) AS 出库金额,
SUM(金额*CHARINDEX('入库', 出入库情况)) - SUM(金额*CHARINDEX('出库', 出入库情况)) AS 金额结存
FROM 产品
WHERE 日期 between '1999-01-01'and '2002-12-31'
GROUP BY 产品名称,型号规格,仓库类别,DATEPART(month, 日期)

[王集鹄]

Syntax

DATEPART(datepart, date)

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft?SQL Server?

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 慗an 1, xxxx? = 1, where xxxx is any year.
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because
smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values that are less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values that are greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For information about specifying time values, see Time Formats. For information about specifying dates, see datetime and smalldatetime.

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date'

GO



Here is the result set:

Current Date
---------------------------
Feb 18 1998 11:46PM


SELECT DATEPART(month, GETDATE()) AS 'Month Number'

GO



Here is the result set:

Month Number
------------
2


This example assumes the date May 29.

SELECT DATEPART(month, GETDATE())

GO



Here is the result set:

-----------

5



(1 row(s) affected)



In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)



Here is the result set:

----- ------ ------

1 1 1900



See Also

CAST and CONVERT Date and Time Functions
Data Types

[xxsteven]

师傅谢谢你又出手了，但是学生这里有个问题想不明白，
你想：要是是盘点的话，因该有一个时间段的问题呀，也就是说我要查3月的那么因该加前2个月的呀，我想那个表是不是有问题

[王集鹄]

你要做一个结存
不过要考虑的东西太多
只有你仔细分析了
我没有这么多精力

[xxsteven]

谢谢

[xxsteven]

不过，我想请你看看，那个表有问提吗？