帮助查询所需的每周自动报告.查询包括！ [英] Help Query for Weekly automated reports wanted. query included !!

问题描述

在下面，我提供了一个查询，该查询运行报告以生成按月显示的输出.

在此报告中，我们提供了一系列日期"以获取作为月份名称的输出.

我的要求是产生一个每周自动报告，该报告从星期六至星期四获取数据并返回星期几.在第几周中

现在由于星期六是我的一周的开始日期，因此在以下所有情况下都将保持这种状态；例如周六是12月29日. 2009年，那是一年的最后一周.并且在2010年的第一个星期六将是第一个星期.


请帮我把这个1弄清楚...

选择"LXE"作为站点，选择日期名称(月，wh1.dropid.adddate)作为月份名称，wh1.codelkup.[描述]作为ShippedType，将wh1.orders.storerkey，sum(wh1.orderdetail.shippedqty)作为ShippedEaches，sum (wh1.orderdetail.shippedqty/wh1.pack.casecnt)作为ShippedCases，sum(wh1.orderdetail.shippedqty/wh1.pack.pallet)作为ShippedPallets，运输"为Type
来自wh1.orders内部联接wh1.orders.orderkey上的wh1.orderdetail = wh1.orderdetail.orderkey
在wh1.orderdetail.packkey上的内部连接wh1.pack = wh1.pack.packkey
内部连接wh1.dropid.dropid上的wh1.dropid = wh1.orders.dropid
内部连接wh1.codelkup.code = wh1.orders.type
上的wh1.codelkup 其中(wh1.pack.casecnt<> 0)和(wh1.pack.pallet<> 0)和(wh1.dropid.adddate在'09/01/2010 00:00:00'AND'之间'09/30/2010 23:59:59'')和(wh1.codelkup.listname =''ORDERTYPE'')
按wh1.orders.storerkey，datename(month，wh1.dropid.adddate)，wh1.codelkup分组.[描述]
通过wh1.orders.storerkey排序，日期名(月，wh1.dropid.adddate)，wh1.codelkup.[说明]

Hi,

Below i have provided with a query, that runs a report to produce output that displays according to months.

In this report we provide a range of Date to gain the output as month names.

My requirement is to produce a weekly automated report, that picks up data from Saturday -Thursday and returns the week no. in the column week no.

now since saturday is my starting day of the week, it will stay that way in all cases below; such as if saturday is on 29th dec. 2009, then that is the last week of the year. and the first saturday in 2010, will be the first week.


PLEASE PLEASE PLEASE, i need help to figure this 1 out...

select ''LXE'' as Site, datename(month,wh1.dropid.adddate) as MonthName,wh1.codelkup.[description] as ShippedType,wh1.orders.storerkey,sum(wh1.orderdetail.shippedqty) as ShippedEaches,sum(wh1.orderdetail.shippedqty / wh1.pack.casecnt) as ShippedCases,sum(wh1.orderdetail.shippedqty / wh1.pack.pallet) as ShippedPallets, ''Shipping'' as Type
from wh1.orders inner join wh1.orderdetail on wh1.orders.orderkey = wh1.orderdetail.orderkey
inner join wh1.pack on wh1.orderdetail.packkey = wh1.pack.packkey
inner join wh1.dropid on wh1.dropid.dropid = wh1.orders.dropid
inner join wh1.codelkup on wh1.codelkup.code = wh1.orders.type
Where (wh1.pack.casecnt <> 0) and (wh1.pack.pallet <> 0) and (wh1.dropid.adddate between ''09/01/2010 00:00:00'' AND ''09/30/2010 23:59:59'')and (wh1.codelkup.listname=''ORDERTYPE'')
group by wh1.orders.storerkey,datename(month,wh1.dropid.adddate),wh1.codelkup.[description]
order by wh1.orders.storerkey,datename(month,wh1.dropid.adddate) ,wh1.codelkup.[description]

推荐答案

我不完全了解您的意思确切地想要...但是我认为的是，以下链接将为您提供帮助.它提供了一个存储过程，可为您提供特定工作日的日期.

http://www.mssqltips.com/tip.asp?tip=1769 [ ^ ]

对于周数，您可以在这里查看:
http://msdn.microsoft.com/en-us/library/aa258265% 28SQL.80％29.aspx [ ^ ]

祝你好运！

I do not completely understand what you want exactly... but what I do think is that the following link will help you out. It provides a stored procedure that gives you the date for a specific weekday.

http://www.mssqltips.com/tip.asp?tip=1769[^]

For weeknumbers you could have a look here:
http://msdn.microsoft.com/en-us/library/aa258265%28SQL.80%29.aspx[^]

Good luck!

这篇关于帮助查询所需的每周自动报告.查询包括！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！