Solved

SQL Help - loop through dates

Posted on 2013-06-11
2
254 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
2 Comments
 
LVL 40

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Expando 4 46
Datagridview control last row back color red vb.net 4 37
DataGridView does not show data rows correctly in vb.net 2013 3 30
Help with Syntax 9 26
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now