Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Help - loop through dates

Posted on 2013-06-11
2
Medium Priority
?
261 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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