?
Solved

SQL Server For Loop?

Posted on 2011-04-20
4
Medium Priority
?
499 Views
Last Modified: 2012-05-11
I need to select records from a database in SQL Server.  I need the value of two fiels (BegDate and EndDate)  

Lets say the table has the following fields
pk id  fk id   begdate  enddate
   1       3      041711    043011
   2       5      051511    053111

I need to get the BEGDATE and ENDDATE from the table and insert it inot a TempTable

DECLARE @CountTable TABLE
(
    Total            INT
  , ID            UNIQUEIDENTIFIER            
  , OrderNo            NVARCHAR(50)                  
  , BegDate            SMALLDATETIME                  
  , EndDate            SMALLDATETIME                  
)

Any ideas?
0
Comment
Question by:CipherIS
  • 2
4 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35435964
This is relatively easy, but what goes in the other columns in your temp table?  Is the temp table already populated?
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 35435965
You haven't specified enough sample data to determine what you want to do; there is no obvious correlation between pk_id or fk_id columns in the source and the id or orderno in the target.

From what you have posted, I can't see why a simple INSERT/SELECT statement couldn't do what you are after, though.  You should avoid loops like the plague in SQL, if you can:

INSERT INTO @CountTable (begdate, enddate)
SELECT begdate, enddate
FROM source_table

J.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 35436130
ok - i need to try to clarify a little better

Below is a sampel code for my TempTable.

If I have another table lets say named tblDates and it has the following data
pk id  fk id   begdate  enddate
   1       3      041711    043011
   2       5      051511    053111

I need the dates in tblDate above to be utilized as parameters below.

Make sense?  
INSERT INTO @CountTable
SELECT COALESCE(SUM(Quantity * -1),0) AS Total ,
       ID ,
       NULL ,    -- OrderNo
       BegDate , -- BeginDate
       EndDate   -- EndDate
FROM TableA TA 
      INNER JOIN TableB TB ON TB.ID = TA.ID 
WHERE TA.Type = 'Ordered' 
AND BegDate BETWEEN @BegDate AND @EndDate
GROUP BY TA.Type

Open in new window

0
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 2000 total points
ID: 35436917
I haven't tried your code, I don't think it would run since you are doing a GROUP BY but haven't included ID, BegDate or EndDate in a GROUP BY or aggregate expression.

That said, maybe the following might get you a step closer:
INSERT INTO @CountTable
SELECT COALESCE(SUM(Quantity * -1),0) AS Total ,
       ID ,
       NULL ,    -- OrderNo
       BegDate , -- BeginDate
       EndDate   -- EndDate
FROM tblDates dt
      INNER JOIN TableA TA ON TA.BegDate BETWEEN dt.begdate AND dt.enddate
      INNER JOIN TableB TB ON TB.ID = TA.ID 
WHERE TA.Type = 'Ordered' 
GROUP BY dt.pk_id, TA.Type

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 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