Friday, July 5, 2013

[SQL Server 2008 issues] Find out Free Virtual Memory

[SQL Server 2008 issues] Find out Free Virtual Memory

Find out Free Virtual Memory

Posted: 04 Jul 2013 03:49 PM PDT

HiHow to find out free virtual memory size by sql query

Autonumber field sometimes skips a 1000

Posted: 04 Jul 2013 01:06 AM PDT

I have an order table with an ID field as its primary key. This is a straightforward indentity field which increment with each new record, well that's how its supposed to work anyway.Every now and then it skips a 1000 numbers for no apparent reason, see example below:108373108369107360107357And:107336107335106340106338These are actual committed orders, so its normal that it sometime skips a couple of numbers. But a thousand is a bit strange. HHas anybody every encountered this issue and what can I do about it?

I want deadlock notification using mail address and record stored in table.

Posted: 04 Jul 2013 05:18 PM PDT

I want deadlock notification using mail address and record stored in table for mssql

how do i select previous date using ssis expression?

Posted: 04 Jul 2013 04:32 PM PDT

hi i have ssis package and following expression which gives me todays date and time for file name@[User::FilePath]+ "Bloomberg_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"\\public\\Bloomberg_Upload\\Bloomberg_2013-07-05 005738.xlsI need to get one date previous like following only for weekdays:\\public\\Bloomberg_Upload\\Bloomberg_2013-07-04 005738.xlsHow can I do this ?For Monday -If I execute my package on Monday date should be of Friday.please guide me

tempdb file delete

Posted: 04 Jul 2013 05:31 PM PDT

ALTER DATABASE tempdbMODIFY FILE (name=tempdev,size=512MB);GOALTER DATABASE tempdbADD FILE (name=tempdev2,size=512MB,filename='N:\Tempdb\tempdev2.ndf');GOALTER DATABASE tempdbADD FILE (name=tempdev3,size=512MB,filename='N:\Tempdb\tempdev3.ndf');GOALTER DATABASE tempdbADD FILE (name=tempdev4,size=512MB,filename='N:\Tempdb\tempdev4.ndf'); I want delete file tempdb4 then how to do it ?

I want create baseline for sql server

Posted: 04 Jul 2013 05:32 PM PDT

hi,Tell different tricks and fusible ways ?

Viewing Management Data Warehouse Reports from a web browser

Posted: 04 Jul 2013 05:32 PM PDT

Hi all!I am constructing a monitoring solution for our enterprise which comprises of Email notification of an error or warning and a link contained in the Email to a report that describes the problem in more detail. In addition to this, I would like certain users to be able to view the Management Data Warehouse (MDW) reports through their Web Browser. Although (once the MDW has been setup and data collection started) these reports are easy enough to view in the SSMS, I would prefer (as would my developers and users!) that an HTTP(S) link could be used to take them straight to where they need to go.Therefore, my question is this: Does anyone know how to use hyperlinks to access the MDW reports from a local web browser?Many thanks in advance!Regards,Kev

sqlquery

Posted: 04 Jul 2013 05:16 PM PDT

which is the programmer develope highest number of package.i have atablre which contain pname,title,developin,scost,dcost,sold

SSRS report Page break should fixed.

Posted: 04 Jul 2013 05:16 PM PDT

hi,I want ssrs report should print tow section in page and divided by page break that page should fixed..regards,DBA.

Stored Procedure is not responding, i am executing but does nothign and donot execute

Posted: 04 Jul 2013 10:20 AM PDT

I am getting error message when i run SP at the bottom[b]Error Msg:Msg 2714, Level 16, State 3, Procedure NIC_OA_GetPhysicianXRayReportsOut, Line 119There is already an object named 'NIC_OA_GetPhysicianXRayReportsOut' in the database[/b]/****** Object: StoredProcedure [dbo].[NIC_OA_GetPatientXRayReportsOut] Script Date: 03/27/2012 18:18:31 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NIC_OA_GetPatientXRayReportsOut]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[NIC_OA_GetPatientXRayReportsOut]GO/****** Object: StoredProcedure [dbo].[NIC_OA_GetPatientXRayReportsOut] Script Date: 03/27/2012 18:18:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO' ' ' Execution Samples: NIC_OA_GetPhysicianXRayReportsOut @lPhysician ' NIC_OA_GetPhysicianXRayReportsOut 70 ' ' ' REVISION HISTORY ' ' Date Developer Comments ' _________ __________________ _____________________________________________ ' '******************************************************************************/ create PROCEDURE [dbo].[NIC_OA_GetPhysicianXRayReportsOut] @lPhysician INT, @Page INT=1, @RecsPerPage INT=50 AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON CREATE TABLE #TempItems ( ID INT IDENTITY, lID INT NOT NULL ) INSERT INTO #TempItems (lID) --SELECT lID FROM tblItem SELECT a.lid FROM [PhysicianXRayRequisitions] a INNER JOIN [Map_XRayRequisitionToProgressNote] b ON a.lID = b.lXRayRequisition INNER JOIN [ProgressNote] c ON c.lID = b.lProgressNote INNER JOIN patient d ON c.lPatient = d.lID WHERE a.lPhysician = @lPhysician AND a.nRecordStatus = 1 AND a.bReportReceived = 0 AND a.bReportRemoved = 0 AND a.szLastPrintedBy IS NOT NULL ORDER BY a.dDateOrdered ASC -- Find out the first and last record we want DECLARE @FirstRec INT, @LastRec INT SELECT @FirstRec = ( @Page - 1 ) * @RecsPerPage SELECT @LastRec = ( @Page * @RecsPerPage + 1 ) SELECT Isnull(b.szLaboratory, 'non selected') AS szLaboratory, a.lID, a.bDiagnosticMammogram, a.bScreeningMammogram, a.dDateOrdered, a.dCheckForReport, p.szLast, p.szFirst, a.lPhysician, p.szFirst AS szPhysicianFirst, p.szLast AS szPhysicianLast, szLastPrintedBy, pat.szFirst AS szPatFirst, pat.szLast AS szPatLast, pat.lid AS lPatient, Rtrim(Isnull(DIform.szFileName, '')) AS szDIFormFileName, a.lDIform_data, DIform_data.lDIform, a.szListRequisition_FreeForm, Isnull(f.FormLiteFormID, 0) AS FormLiteFormID, f.FormLiteSnapshotID, TotalRecords = (SELECT Count(*) FROM #TempItems TI) FROM #TempItems Inner join [PhysicianXrayRequisitions] a ON a.lid = #TempItems.lID LEFT JOIN [Laboratory] b ON b.lID = a.lLaboratory LEFT JOIN FormLiteSnapshotMap f ON a.lid = f.lPhysicianXRayRequisitions INNER JOIN [Map_XRayRequisitionToProgressNote] c ON c.lXRayRequisition = a.lID INNER JOIN [ProgressNote] d ON c.lProgressNote = d.lID INNER JOIN [Patient] pat ON d.lPatient = pat.lID INNER JOIN [Physician] p ON p.lid = a.lPhysician LEFT JOIN DIform_data ON DIform_data.lid = a.lDIform_data LEFT JOIN DIform ON DIform.lid = DIform_data.lDIform WHERE #TempItems.ID > @FirstRec AND #TempItems.ID < @LastRec ORDER BY #TempItems.ID SET NOCOUNT OFF

Replication failing with Merge process could not enumerate

Posted: 05 Aug 2012 08:01 PM PDT

Hi All,Out of the blue my SQL server started going slow sporadically and every so often throws up some errors.SQL Sever : 2008Windows Server : 2008Merge replication with 1 subscriberThe error that is occurring...Error messages:The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)Get help: http://help/MSSQL_REPL-2147200999TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)Get help: http://help/10054Communication link failure (Source: MSSQLServer, Error number: 10054)Get help: http://help/10054Protocol error in TDS stream (Source: MSSQLServer, Error number: 0)Get help: http://help/0Has anyone come across this before??How does one switch on verbose logging for a merge replication?ThanksReggie

Return Empty is a specific record is encontered.

Posted: 04 Jul 2013 07:18 AM PDT

Hi all,This seems to be very easy (and probably is!!) but I'm just stuck here.I have a table that contains an ID, a date and a Status.I want to return the top row (most recent datetime) if any of the status for the same Id is not C (as in Canceled).Here's an example:create table #Temp1(RecID int, DateEntered Datetime, Status Varchar(1))INSERT INTO #Temp1 VALUES (1,'01-01-2013 10:20:10', 'N')INSERT INTO #Temp1 VALUES (1,'01-02-2013 08:14:00', 'R')INSERT INTO #Temp1 VALUES (1,'01-03-2013 03:30:00', 'E')INSERT INTO #Temp1 VALUES (2,'01-01-2013 14:58:00', 'N')INSERT INTO #Temp1 VALUES (2,'01-02-2013 08:23:00', 'R')INSERT INTO #Temp1 VALUES (2,'01-04-2013 22:14:00', 'C')select * from #Temp1drop table #temp1In this examples my result set should be:1 ; 01-03-2013 03:30:00 ; ENo record should be returned from RecId 2 because the last line is a 'C'.The 'C' status will always be in the last row.Thank you all for your help!!

Can I change a column from smallint to float?

Posted: 04 Jul 2013 09:53 AM PDT

I've got a column, in 1 table, that's a smallint. Then I've got another table with the same column name (it's meant to be the same thing) but it is a float. I'd really like to change the data type in the first table to a float, too, but am concerned that I might loose data. May I just change the data type from smallint to float, and will SQL Server 2008 R2 keep my data?

Retrieve the TOP 10 Elapsed time (query)

Posted: 03 Jul 2013 10:26 PM PDT

Hi ,Someone can give me a script that return the top 10 sql queries based on elapsed time for the past 7 days.Please ?

Impact of DBCC UPDATEUSAGE (0)

Posted: 03 Jul 2013 08:17 PM PDT

We got the advice to run DBCC UPDATEUSAGE, on one of our databases.What impact can be expected from this command?[b]Can this be done online?[/b]Does this impact the servers performance?This is for a 2005 system, databasesize is 40 Gb.On a 2008 system a comparable database of 10 Gb was done in 10 secs.On a 2008 R2 system (small) a 200 Gb database was done in just under 6 minutes.Test for a 2005 system on the backup of the database is in preparation.thanks for your time and attention,Ben Brugman

Getting error while creating database

Posted: 03 Jul 2013 11:27 PM PDT

Getting error while creating database."SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)"Thanks,

sql 2000 to sql 2008

Posted: 04 Jul 2013 03:15 AM PDT

can someone tell why this works in sql2000, but not sql2008?DECLARE @rows varchar(5), @cnt int, @pak decimal, @results varchar(1000)SELECT @cnt = isnull(max(seq),0) from @patemp <<<[i] this temp table is empty so @cnt becomes zero[/i]IF @cnt>0 BEGIN UPDATE unanet..sequence_number SET @pak=last_number=last_number+@cnt WHERE table_name='project_assignment' [b] UPDATE @patemp SET pa_key=@pak-@cnt+seq[/b] << this worked in sql2000 but gives error in sql2008:Data type decimal of receiving variable is not equal to the data type decimal of column 'last_number'. [SQLSTATE 42000] (Error 425). The step failed.sorry, here table:CREATE TABLE [dbo].[sequence_number]( [table_name] [varchar](50) NOT NULL, [last_number] [decimal](15, 0) NOT NULL,

configure location of analysis services database and deployment

Posted: 03 Jul 2013 07:57 PM PDT

HiI am creating a new analysis services project and would like the analysis services database to be on a public server (not on my local pc).How do i configure that?or can the cube be developed locallally and deployed to a public server.Thanks in advance

Worker Thread

Posted: 03 Jul 2013 11:19 PM PDT

hi,fires an alarm upon detecting that the percentage of Worker Threads used.Please give query to find out Worker Threads

SQL I/O Errors

Posted: 03 Jul 2013 11:15 PM PDT

Hi,How can i get SQL I/O Errors by sql query

Reporting Services DRP Strategy

Posted: 04 Jul 2013 01:04 AM PDT

Hey Everyone,I'm looking for some insight on Reporting Services 2008. I currently am backing up the ReportServer Database and loading it weekly to our DRP site (we don't make many changes). While I fully understand that I could mirror this database, I have now decide to have this DRP Server to run off of the DRP data 'Sources'. Is there a way that when changes are made to reports that I can have them copied to the DRP site without having to manually upload the RDL files every time a change is made? If not then this just doesn't make any sense to me.Thanks in advance for your expertise!Steve

Find All Compressed tables in database - script

Posted: 12 Apr 2010 07:09 AM PDT

So here is what I have so far, seems to work but I swear i've seen a more simplistic way to do this. What I want to do is display all tables in a database that are using some sort of compression (regardless if it's PAGE or ROW). Below is what I already have, but if anyone knows of anything better I would be greatful. --script to identify compressed tablesSELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression, sp.data_compression_desc FROM sys.partitions SPINNER JOIN sys.tables ST ONst.object_id = sp.object_idWHERE data_compression <> 0;-)

Table Locks

Posted: 03 Jul 2013 11:18 PM PDT

Hi, fires an alarm, when the number of times page locks escalated to table locks per second e

Insert with ' Symbol

Posted: 03 Jul 2013 08:31 PM PDT

Hi Team.Insert into table_Name values (1,'Text') -- Done.Insert into table_Name values (2,'Text's') - -- Unclosed quotation mark after the character string ')How to insert a value with ' Symbol.Please help..

memory

Posted: 03 Jul 2013 09:46 PM PDT

how to restrict memory at query level and also at server level?

Reg Query Count

Posted: 03 Jul 2013 09:41 PM PDT

Hi all, From the trace its been found that some sp are running 15k times . Is there any way we can reduce the count they are running ...

Transposing two rows of data to one row

Posted: 03 Jul 2013 09:17 PM PDT

I have a large table of 2m records and I need to combine every two Document No_ to one row and placing the other fields on one row against it as below:Document No_ Dimension Code Dimension Value Code------------------------------------------------------------------------------------------SHP99994 DEPARTMENT MHTSHP99994 TRADETYPE MI would like the code change the above display to the following:Document No_ Dimension Value Code_1 Dimension Value Code_2-----------------------------------------------------------------------------------------------SHP99994 MHT MCan you help please?Thank you in advance

SSIS - Oracle Source character set changed from non unicode to Unicode - Do I need to change the code?

Posted: 10 Sep 2012 06:06 AM PDT

Hi,Our source database Oracle is changed from non Unicode to Unicode. The mappings were done with source DT_STR for source and target external columns. Do I need to change the code to DT_WSTR for source and Target external columns with unicode conversion transformation? Or changing the Target SQL Server database character set will resolve the issue?Thank You.

How to find who changed the database to Single_user mode.

Posted: 03 Jul 2013 08:55 PM PDT

Hi All,One of our databases changed to single_user mode. But we could not find the exact user, hostname etc. Is there is any way to find these details?I had gone through the SQL Server Error logs and Windows event error logs. But could find the exact user who changed the database to single_user mode? Do database automatically changed to single user mode? Regards,Varun

Database Restoring

Posted: 03 Jul 2013 08:09 PM PDT

Hi All,How to set Database restoring mode to normal mode?Thanks,RR

Calculation For Time Attendance System

Posted: 03 Jul 2013 07:28 PM PDT

i had plot data from Entry Pass Scan in / out system into Database and write calculation in SQL script to generate Report in Aspx web page. i had different type of shift pattern following by[img]http://img841.imageshack.us/img841/7942/cj81.jpg[/img]Currently i had facing a problem which is a)Total actual working minute(as Work MIn) is 720min per day[12 hours per day] (if more than 720 minutes working hours is consider under category Extra OT hour). b) Cut off the scan in and out time from 0700 ~ 1900 (DAY) and 1900 ~ 0700(NIGHT) calculation. However, if the person late in or early out the calculation of the time is base from the last and the first scan. Anyone can help me or amend the calculation in SQL Script ? For the Work Hour calculation, i have a idea which is catch the (last scan time - EP_SHIFT_TIMEFROM) for every shift type Kindly advise , thank youSELECT CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) AS SCANDATE ,FIRSTSCAN.EP_EMP_COMPANY ,FIRSTSCAN.EP_EMP_DEPT ,FIRSTSCAN.EP_EMP_ID ,FIRSTSCAN.EP_EMP_NAME ,FIRSTSCAN.EP_EMP_SECTION ,FIRSTSCAN.EP_EMP_SHIFT ,FIRSTSCAN.EP_SHIFT ,right(FIRSTSCAN.EP_SCAN_DATE,6) AS FIRSTSCAN ,right(LASTSCAN.EP_SCAN_DATE,6) AS LASTSCAN ,SCANTIMECAL.INFAB AS INFAB_MIN ,SCANTIMECAL.OUTFAB AS OUTFAB_MIN ,DATEDIFF(MI,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_MIN ,DATEDIFF(HOUR,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_HOUR ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) ELSE ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) END AS OTWORK_HOUR ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN 3 - 0.25 END OTHOUR_FIX ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME) ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME), CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT) - 3,2) ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) ,Cast(STUFF(Stuff(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2)END END OTHOUR_EXTRA ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN FIRSTSCAN.LATEIN END LATEIN ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN SCANTIMECAL.OUTFAB >= SHIFTDESC.EP_SHIFT_OGRACE THEN 1 END END AS BREAK_ABNORMAL ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8), CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108),108),':','') THEN 1 END END AS EARLYOUT_NORMAL ,(CASE WHEN FIRSTSCAN.EP_SHIFT<> 'NS' AND FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN (CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+3,CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108)),108),':','') THEN 1 END) END)AS EARLYOUT_SHIFT FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY SCANHIST.EP_EMP_ID,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112) ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) END ORDER BY SCANHIST.EP_EMP_ID) AS RowNum ,SCANHIST.EP_SCAN_DATE ,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112) ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) END AS EMP_WORKDATE ,EMPINFO.EP_EMP_COMPANY ,SCANHIST.EP_EMP_ID ,SCANHIST.EP_EMP_NAME ,SCANHIST.EP_EMP_DEPT ,SCANHIST.EP_EMP_SECTION ,SCANHIST.EP_EMP_SHIFT ,SHIFTCAL.EP_SHIFT ,SCANHIST.EP_SCAN_ID ,SCANHIST.EP_TRANS_LOC ,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) > REPLACE(SHIFTDESC.EP_SHIFT_TIMEFR,':','') THEN 1 END AS LATEIN FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(SCANHIST.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND SCANHIST.EP_SCAN_DATE >= '20130524' + ' ' + CASE WHEN (SHIFTCAL.EP_SHIFT <> 'R1' AND SHIFTCAL.EP_SHIFT <> 'R2') THEN REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-4,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') ELSE REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-0,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') END AND SCANHIST.EP_SCAN_DATE < CASE WHEN (SHIFTCAL.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,'20130526'),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (SHIFTCAL.EP_SHIFT = 'R1' OR SHIFTCAL.EP_SHIFT = 'R2') THEN '20130526' + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE '20130526' + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END AND SCANHIST.EP_TRANS_LOC = 'IN' AND EMPINFO.EP_EMP_LEVEL > '10' --AND EMPINFO.EP_EMP_LEVEL <> '' --AND SCANHIST.EP_EMP_DEPT = '' --AND SCANHIST.EP_EMP_SECTION = '' --AND SCANHIST.EP_EMP_SHIFT = '' --AND SCANHIST.EP_EMP_ID = '' AND SCANHIST.EP_EMP_SHIFT ='A' )FIRSTSCAN OUTER APPLY ( SELECT TOP 1 SCANHIST.EP_SCAN_DATE ,EMPINFO.EP_EMP_COMPANY ,SCANHIST.EP_EMP_ID ,SCANHIST.EP_EMP_NAME ,SCANHIST.EP_EMP_DEPT ,SCANHIST.EP_EMP_SECTION ,SCANHIST.EP_EMP_SHIFT ,SHIFTCAL.EP_SHIFT ,SCANHIST.EP_SCAN_ID ,SCANHIST.EP_TRANS_LOC FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND SCANHIST.EP_SCAN_DATE > FIRSTSCAN.EP_SCAN_DATE AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') else CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN left(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE left(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END AND SCANHIST.EP_TRANS_LOC = 'OUT' AND EMPINFO.EP_EMP_LEVEL > '10' AND EMPINFO.EP_EMP_LEVEL <> '' AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID ORDER BY SCANHIST.EP_SCAN_DATE )LASTSCAN OUTER APPLY ( SELECT GROUP_SCANTIMECAL.EP_EMP_ID ,SUM(CAST(GROUP_SCANTIMECAL.INFAB_MIN AS FLOAT)) AS INFAB ,SUM(CAST(GROUP_SCANTIMECAL.OUTFAB_MIN AS FLOAT)) AS OUTFAB FROM ( SELECT SCANHIST.EP_SCAN_DATE ,SCANHIST.EP_EMP_ID ,SCANHIST.EP_EMP_NAME ,SCANHIST.EP_EMP_DEPT ,SCANHIST.EP_EMP_SECTION ,SCANHIST.EP_EMP_SHIFT ,SCANHIST.EP_TRANS_LOC ,DATEDIFF(MI,CAST(STUFF(STUFF(SCANHIST.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME), CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS INFAB_MIN ,DATEDIFF(MI,CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME), CAST(STUFF(STUFF(PREVSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS OUTFAB_MIN FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST OUTER APPLY ( SELECT TOP 1 NEXTSCAN.EP_SCAN_DATE ,NEXTSCAN.EP_EMP_ID ,NEXTSCAN.EP_EMP_NAME ,NEXTSCAN.EP_EMP_DEPT ,NEXTSCAN.EP_EMP_SECTION ,NEXTSCAN.EP_EMP_SHIFT ,NEXTSCAN.EP_SCAN_ID ,NEXTSCAN.EP_TRANS_DESC ,NEXTSCAN.EP_TRANS_LOC FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] NEXTSCAN JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = NEXTSCAN.EP_EMP_ID JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = NEXTSCAN.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND SCANHIST.EP_SCAN_ID = NEXTSCAN.EP_SCAN_ID AND NEXTSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE AND NEXTSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(NEXTSCAN.EP_SCAN_DATE,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END AND NEXTSCAN.EP_TRANS_LOC = 'OUT' ORDER BY NEXTSCAN.EP_SCAN_DATE )NEXTSCAN OUTER APPLY ( SELECT TOP 1 PREVSCAN.EP_SCAN_DATE ,PREVSCAN.EP_EMP_ID ,PREVSCAN.EP_EMP_NAME ,PREVSCAN.EP_EMP_DEPT ,PREVSCAN.EP_EMP_SECTION ,PREVSCAN.EP_EMP_SHIFT ,PREVSCAN.EP_SCAN_ID ,PREVSCAN.EP_TRANS_DESC ,PREVSCAN.EP_TRANS_LOC FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] PREVSCAN JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = PREVSCAN.EP_EMP_ID JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = PREVSCAN.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND SCANHIST.EP_SCAN_ID = PREVSCAN.EP_SCAN_ID AND PREVSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE AND PREVSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(SCANHIST.EP_SCAN_DATE,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END AND PREVSCAN.EP_TRANS_LOC = 'IN' ORDER BY PREVSCAN.EP_SCAN_DATE )PREVSCAN JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND SCANHIST.EP_SCAN_DATE >= FIRSTSCAN.EP_SCAN_DATE AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID AND SCANHIST.EP_TRANS_LOC = 'IN' )GROUP_SCANTIMECAL GROUP BY GROUP_SCANTIMECAL.EP_EMP_ID )SCANTIMECAL JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL ON SHIFTCAL.EP_SHIFT_NAME = FIRSTSCAN.EP_EMP_SHIFT AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8) JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT WHERE 1=1 AND FIRSTSCAN.RowNum = 1 AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130524' AND '20130526' ORDER BY FIRSTSCAN.EP_EMP_ID
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog