SQL Server For Loop?

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?
LVL 2
CipherISAsked:
Who is Participating?
 
jimbobmcgeeCommented:
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
 
knightEknightCommented:
This is relatively easy, but what goes in the other columns in your temp table?  Is the temp table already populated?
0
 
jimbobmcgeeCommented:
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
 
CipherISAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.