Looping Transact SQL

--SSRS Parameter

Declare @timezoneOffset int, @PfromDate datetime, @PToDate datetime

Set @timezoneOffset=8

Set @PfromDate='2013-09-1 6:00AM'

Set @PToDate='2013-09-2 6:00AM'

 

 

--Temporary Table

IF OBJECT_ID('tempdb.dbo.#PROD') IS NOT NULL 

  DROP TABLE [dbo].[#PROD]
        CREATE TABLE  #PROD
      (
            XWRKCTRID NVARCHAR(20)                       
             ,XCREATEDBY NVARCHAR(10)
            

          ,XASUSTATUS INT
              ,XOPRNUM INT
              ,XCREATEDDATETIME Datetime
              ,XMODEL NVARCHAR(35)
      )

     

--Insert records       

INSERT INTO  #PROD
(XWRKCTRID  ,XCREATEDBY
                  ,XASUSTATUS
                  ,XOPRNUM
                  ,XCREATEDDATETIME
                  ,XMODEL
)

--Main Query
SELECT PR1.XWRKCTRID
                  ,PR1.XCREATEDBY
                  ,PR1.XASUSTATUS
                  ,PR1.XOPRNUM
                  ,PR1.XCREATEDDATETIME
                  ,substring(PT1.XITEMID,11,CHARINDEX('-',PT1.XITEMID,11)-11) as MODEL
FROM DATA1 PR1
---Loop back this date if there's no record found from Query
WHERE DATEADD(HOUR,convert(int,@timezoneOffset), PR1.createddatetime) BETWEEN (@PfromDate) AND  (@PToDate)

 
--process this query if records found from  #PROD
;WITH CTE AS
(
  SELECT * FROM #PROD
)
SELECT
     SUM(CASE WHEN [status]=5 Then 1 Else 0 END) pasqty
FROM  CTE c
Where STATUS=5