Solved

SQL Help - loop through dates

Posted on 2013-06-11
2
260 Views
Last Modified: 2013-06-17
Trying to create a query which will retrieve information and store in a temp table so I can report on the values.  The report needs to show the information by each Friday.  I am creating a temp table which adds each Friday by date

Like this.

reportdate                      source      item_no      quantity      trx_dt      promise_dt
2013-06-14 00:00:00.000      NULL      NULL      NULL      NULL      NULL
2013-06-21 00:00:00.000      NULL      NULL      NULL      NULL      NULL
2013-06-28 00:00:00.000      NULL      NULL      NULL      NULL      NULL

However, my other data doesn't fall into each Friday order.

source      item_no                                        quantity      doc_type      trx_dt
P      ITK2236                             60.0000      O      2013-06-14 00:00:00.000
O      ITK2236                             14.0000      A      2013-06-14 00:00:00.000
P      ITK2236                             60.0000      O      2013-06-17 00:00:00.000
P      ITK2236                             60.0000      O      2013-06-19 00:00:00.000
O      ITK2236                             15.0000      A      2013-06-21 00:00:00.000
O      ITK2236                             27.0000      A      2013-07-26 00:00:00.000


How can I loop, cursor, whatever through these records and sum the values of the quantity by the trx_dt by the appropriate Friday.  This will only run by 1 item_no at a time, but there could be muliple records per trx_dt.

Any help would be appreciated?
0
Comment
Question by:red_75116
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39239758
try this. add relevant columns wherever you want.
select DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,trx_dt), 0)) reportdate,
       NULL source,
	   item_no,
	   sum(quantity) quantity
  from your_table
 group by DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,trx_dt), 0)),item_no

Open in new window

0
 

Author Closing Comment

by:red_75116
ID: 39253154
Thanks!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question