Need assistance with SQL Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Need assistance with SQL Query

Author

Topic

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-12 : 13:12:20

I have this query that I will create into a stored procedure after this query is complete.

I have commented a line of code within this query. This line of code will insert the data that the query creates (Pretty much a grouped query of @Data). I need to take this query and JOIN the table that I need to INSERT the data into. I want the JOIN to eliminate the possibility of displaying duplicates.

How should this query look?

Thanks for your help!!


Declare @Data 
Table   (RowId Integer Identity(1,1),
        tRSSQL_TRANS DateTime, 
        bEND_OF_REEL bit,
        nLINE Integer
        )

Insert Into @Data(tRSSQL_TRANS, bEND_OF_REEL, nLINE)
Select tRSSQL_TRANS, bEND_OF_REEL, nLINE 
From   vM_FR_STOPS_Grouping
Order By nLine, tRSSQL_TRANS

--Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        Select B.tRSSQL_TRANS As StartGroup, 
               B.nLine
        From   @Data A 
               Inner Join @Data B 
                 On  A.RowId = B.RowId -1 
                 And A.nLine = B.nLine
        Where  A.bEND_OF_REEL = 1
               And B.bEND_OF_REEL = 0
        ) A
        Inner Join @Data D 
          On A.nLine = D.nLine
          And A.StartGroup < D.tRSSQL_TRANS
          And D.bEND_OF_REEL = 1
Group By A.StartGroup, A.nLine

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-12 : 14:49:54

Is there something wrong with this topic. Is that why no one will respond?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-12 : 15:34:13

quote:
Originally posted by chippyles

Is there something wrong with this topic. Is that why no one will respond?

I don't understand what yout want. Could you provide a data example of your problem?

Tara Kizer
aka tduggan

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-12 : 15:56:04

I figured there was something wrong here.

I have a table that collects data every minute from PLC's. The code I have takes the data and groups them up so I can get a StartGroup Time and an EndGroup Time for each reel of steel we produce.


nLine	StartGroup	EndGroup
1	6/12/2006 3:02	6/12/2006 4:38
1	6/12/2006 8:40	6/12/2006 10:15
1	6/12/2006 4:40	6/12/2006 8:38
2	6/12/2006 5:15	6/12/2006 9:16
6	6/12/2006 3:06	6/12/2006 5:12
6	6/12/2006 5:20	6/12/2006 10:02

The code takes data and places it in a temp table called @Data.

I have an INSERT command that will take the @data and put it in its permanent home called tM_FR_WIP_UnitID_FastStops.


--Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)

I will remove the comment (--) once I know the code works correctly.

What I need is to take what the code produces in @Data and compare it to the table tM_FR_WIP_UnitID_FastStops and make sure there are no duplicates.

*****

A real simple JOIN on two tables to verify that I am only selecting new records from the second table is this...


SELECT     dbo.Table2.col1, dbo.Table2.col2
FROM         dbo.Table1 RIGHT OUTER JOIN
                      dbo.Table2 ON dbo.Table1.col1 = dbo.Table2.col1
WHERE     (dbo.Table1.col1 IS NULL)

That is what I am tryiong to accomplish using my original code.

Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-12 : 15:59:46

So @Data has the rows to be added, but you only want to add non-duplicates? If so, then what signifies a duplicate?

Tara Kizer
aka tduggan

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-13 : 07:07:55

A duplicate is signified when the below section of the code does match the line or ENDGROUP of tM_FR_WIP_UnitID_FastStops. Hence the JOIN of the example code of Table 1 and Table 2. If you did a RIGHT OUTER JOIN Table 2, the only way to make sure it is new data is to check to see if a field in Table 1 is NULL.


Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        Select B.tRSSQL_TRANS As StartGroup, 
               B.nLine
        From   @Data A 
               Inner Join @Data B 
                 On  A.RowId = B.RowId -1 
                 And A.nLine = B.nLine
        Where  A.bEND_OF_REEL = 1
               And B.bEND_OF_REEL = 0
        ) A
        Inner Join @Data D 
          On A.nLine = D.nLine
          And A.StartGroup < D.tRSSQL_TRANS
          And D.bEND_OF_REEL = 1
Group By A.StartGroup, A.nLine

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-13 : 09:12:26

can anyone be of assistance?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources