SQl Server Backup Takes 2 Hours To Run But 12 Minutes After A Server Reboot(?)
Sep 19, 2007
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
Hi,
we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
NB this intermittent slowdown only occurred after the disk failure.
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
thanks for your help
best regards
kevin
View 11 Replies
ADVERTISEMENT
Sep 4, 2014
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
View 1 Replies
View Related
Apr 21, 2015
My table as data as follow,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
[code].....
View 6 Replies
View Related
Apr 9, 2007
Could some body in microsoft database team explain this behavior? Problem is predominant when cardinality of a column is very high and a where clause is specified on that column. Both use the same index.
select a12.DATE_INVOICE_ID DATE_INVOICE_ID,
a11.CUSTOMER_ID CUSTOMER_ID,
sum(a11.EXTENDED_PRICE) WJXBFS1,
sum(a11.TOTAL_COST) WJXBFS2,
(sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3
from FACT_SALES_LINE a11
join RLTN_V_SL_INVOICE_YTD a12
on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID)
join LKP_V_SL_EXPENSE_CODE a13
on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID)
join LKP_V_SL_LAST_STATUS a14
on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID)
join LKP_V_SL_NEXT_STATUS a15
on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID)
join LKP_V_SL_ORDER_TYPE a16
on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID)
where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012)
and a12.DATE_INVOICE_ID = 106365
and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU')
and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600)
and a13.EXPENSE_CODE_SRCCD <> 'LC'
and a15.NEXT_STATUS_SRCCD = '999'
and a14.LAST_STATUS_SRCCD in ( '620','914') )
group by a12.DATE_INVOICE_ID,
a11.CUSTOMER_ID
OPTION ( FORCE ORDER )
PLAN without force order:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID 1 1 0 NULL NULL 1 NULL 1.138269 NULL NULL NULL 2.716851 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.138269 0 1.138269E-07 66 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END) [Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1023], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.17323 0.01126126 0.0001004628 41 2.71685 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID])) 1 6 5 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID]) NULL 1.17323 0 8.671883E-06 41 2.705488 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 7 6 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 2.074613 0 8.671883E-06 41 2.702026 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 8 7 Nested Loops Inner Join OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 2.074613 0 8.671883E-06 45 2.697204 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | |--Stream Aggregate(GROUP BY[a11].[FSL_SEQ_NO]) DEFINE[a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]))) 1 9 8 Stream Aggregate Aggregate GROUP BY[a11].[FSL_SEQ_NO]) [a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) 2.074613 0 0.1950575 49 2.692634 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED) 1 10 9 Nested Loops Inner Join OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED NULL 390113 0 1.630672 53 2.497577 [a11].[FSL_SEQ_NO], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 10 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 2.074613 0 9.189784E-06 53 0.0406176 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 2.198513 0 0.0001337915 57 0.03713583 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 14 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.00104035 66 0.02786905 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 15 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 0 32.00753
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 16 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003472585 NULL NULL PLAN_ROW 0 2.198513
| | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD) 1 17 10 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 188041.3 0.2616435 0.2070025 11 0.8262868 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 2.074613
| | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 31 8 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_SRCCD] 1 0.003125 0.0001581 14 0.004555401 .[ORDER_TYPE_SRCCD] NULL PLAN_ROW 0 2.074613
| |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 32 7 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.004812614 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 0 2.074613
|--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 33 6 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.003452996 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 2.074613
PLAN WITH FORCE ORDER:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID OPTION ( FORCE ORDER ) 1 1 0 NULL NULL 1 NULL 1.08425 NULL NULL NULL 9.249098 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.08425 0 1.08425E-07 66 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END) [Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1035], [Expr1036], [Expr1037], [Expr1038] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.103783 0.01126126 0.0001002863 41 9.249096 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Parallelism(Gather Streams) 1 6 5 Parallelism Gather Streams NULL NULL 1.103783 0 0.02850539 41 9.237735 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID])) 1 7 6 Merge Join Inner Join MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) NULL 1.103783 0.0004695 0.001520579 41 9.209229 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Sort(ORDER BY[a11].[ORDER_TYPE_ID] ASC)) 1 8 7 Sort Sort ORDER BY[a11].[ORDER_TYPE_ID] ASC) NULL 1.103783 0.002815315 2.507257E-05 45 0.09645625 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Bitmap(HASH[a11].[ORDER_TYPE_ID]), DEFINE[Bitmap1034])) 1 9 8 Bitmap Bitmap Create HASH[a11].[ORDER_TYPE_ID]) [Bitmap1034] 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[a11].[ORDER_TYPE_ID])) 1 10 9 Parallelism Repartition Streams PARTITION COLUMNS[a11].[ORDER_TYPE_ID]) NULL 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 11 10 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 1.103783 0 4.031072E-06 45 0.06510919 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 11 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 3.857486 0 3.344787E-05 49 0.06137029 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 32.00753 0 3.344787E-05 53 0.05315145 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH) 1 14 13 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH NULL 32.00753 0 3.344787E-05 57 0.03554453 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 16 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.0002600876 62 0.02708879 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 17 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.008185391 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 32.00753
| | | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 18 13 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.01755811 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 1 32.00753
| | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 19 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 1 32.00753
| |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 20 11 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003734868 NULL NULL PLAN_ROW 1 3.857486
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE)) 1 21 7 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE) NULL 20.5 0 0.0285224 11 9.11078 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID])) 1 22 21 Merge Join Inner Join MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]) NULL 20.5 0 0.001586888 11 9.082257 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC)) 1 23 22 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC) NULL 328 0 0.03033649 11 0.03529974 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
| |--Clustered Index Scan(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 24 23 Clustered Index Scan Clustered Index Scan OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_ID] 328 0.004606482 0.00013165 18 0.004738132 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 25 22 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 27 0 1.6875E-05 11 9.045368 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC)) 1 26 25 Parallelism Repartition Streams PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC) NULL 108 0 0.0291047 11 9.045351 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 27 26 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 108 0 0.634653 11 9.016247 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Index Scan(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD) 1 28 27 Index Scan Index Scan OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 5077116 6.985347 1.396246 11 8.381594 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
View 4 Replies
View Related
May 13, 2008
I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
View 10 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
View 5 Replies
View Related
Aug 14, 2007
Hi guys -
I have been googling all day and not found a reasonable solution to my problem.
Here is what I am trying to do. The T-SQL code is included at the bottom of this message.
- The user can specify columns that are stored in a CVColumn table (the data for which comes from an external source). For example, we may provide LastName and FirstName columns and the user could add ShoeSize and FavoriteColor.
- When we get the data from the other source, we create it as a dataset in the *code* with each user-defined field as a column of the table and each person as a row in the table. This is used *everywhere* including binding to grids, etc... So the dataset may look like this:
PersonID
LastName
FirstName
ShoeSize
FavoriteColor
- The dba has required that the data be stored in a normalized fashion. Since we don't know what the columns are - the data is stored in the *table* as:
PersonID
ColumnName
ColumnValue
- When the user gets the data from the external source the first time, the code loops through every person row and every column of the row to insert fields into the table using an Insert stored proc. This performs reasonable well.
- When the code retrieves the data from the database, it needs to reformat the data into one row per person, one column per field so that all of the other code can do the binding, etc.
- So I created a temp table with the appropriate rows and columns and then updated that table with the appropriate data. *This* is the stored proc that has been running now for almost an hour.
- There *must* be a way to do this that performs better?
Here is my T-SQL:
Code Snippet
DECLARE @columnNamesWithSizes varchar(8000)
DECLARE @delim varchar(1)
DECLARE @values varchar(8000)
-- Get the set of column names
SET @columnNamesWithSizes='swiCMKey VARCHAR(4092), swiCMContactManager VarChar(100), swiCMContactManagerID int,
swiCMFolder VarChar(4092), swiCMCVFolder VarChar(4092)'
SELECT @columnNamesWithSizes=COALESCE(@columnNamesWithSizes + ',', '') + ColumnName + ' VARCHAR(50)' FROM CVColumn
-- Create the table with the desired set of columns
EXEC ('CREATE TABLE ##tempTable ( ' + @columnNamesWithSizes + ')')
-- Insert the primary key into each row
EXEC ('INSERT INTO ##tempTable (swiCMKey) SELECT DISTINCT ContactID as swiCMKey FROM CVContact')
-- Use a cursor to loop through all of the records
SET @delim=''''
DECLARE @swiCMKey VARCHAR(4096)
DECLARE contactList CURSOR FOR
SELECT swiCMKey FROM ##tempTable
OPEN contactList
FETCH NEXT FROM contactList INTO @swiCMKey
-- Loop until all rows in temp table have been processed.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @values=null
-- Retrieve all of the columns for this contact
SELECT @values = COALESCE(@values + ',', '') + ColumnName + '=' + @delim + COALESCE(Value,'') + @delim
FROM CVContact
WHERE CVContact.ContactID=@swiCMKey
-- Perform the update to the temp table with the column values
EXEC ('UPDATE ##tempTable SET ' + @values + ' WHERE swiCMKey=' + @delim + @swiCMKey + @delim)
-- Get the next one
FETCH NEXT FROM contactList INTO @swiCMKey
END
CLOSE contactList
DEALLOCATE contactList
-- Return the results
SELECT * from ##tempTable
-- Kill the temp table
DROP TABLE ##tempTable
Any ideas or othre suggestions would be much appreciated!
(BTW - up until today the data from the code-based dataset was stored as XML and then read back as XML. However, we found with 80,000+ people that it took 5 minutes to read the XML. But that is faster than this stored proc!<G>)
View 6 Replies
View Related
Apr 18, 2008
I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...
TimeValue({clm_doubt}-{clm_rcvd})
This is not working for me. Can someone give me a suggestion please.
Thanks!
View 2 Replies
View Related
Nov 6, 2006
Hi
Can anyone help me convert a number to give the result in hours and minutes? For example 195 as 3:15 or 210 as 3:30. We are trying to create a report showing hours and minutes worked without having to export to Excel.
I've had a look around the net and this seems to be quite a difficult function in SQL Server.
Any guidance much appreciated.
View 7 Replies
View Related
Jun 19, 2007
Hi guys,
how to get the Hours and Minutes ago?
I have a column name "DateCreated" which is a DateTime type. I need to get the Hours and Minutes ago
Thank you.
View 1 Replies
View Related
Feb 24, 2006
hi,
I want to convert minutes to hours. for example field_minutes=130minutes to 2:10 hours...
select field_minutes from table---> how can I do?
View 3 Replies
View Related
Nov 11, 2014
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
View 2 Replies
View Related
Apr 20, 2006
I have an IS package containing approx. 10 tasks in the control flow - one of these tasks is a rather large data flow containing around 50 transformations plus 3 sources and two destinations. Around 10 of these are script components and another 10 are Union All transformations. The rest are primarily lookups, conditional splits and derived column transformation.
The XML file containing the package is approx. 4.5 MB. As I am developing the package, it is becoming increasingly slow to work with as more transformations are added to the data flow. Now, it takes 8 minutes every time I have to open the package for development (DelayValidation is even set to true) and DTExec (not the debugger) uses the same amount of time before it starts executing the package. It also takes a very long time to edit the data flow, as I typically have to wait 1-2 minutes every time the designer has to "commit" a change.
Does anyone have any idea what can be done to speed up the package - both with regard to development and execution?
View 4 Replies
View Related
Mar 22, 2008
I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
What shell I do?
View 8 Replies
View Related
Mar 22, 2008
I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
What shell I do?
View 4 Replies
View Related
Jan 30, 2006
guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5
View 3 Replies
View Related
Jul 5, 2014
need to calculate the total hours mintues in sql server and asp.net
View 11 Replies
View Related
Feb 3, 2014
I have a series of times in decimal 15 min slots. The data type is float and the field is the followng:
10
10.25
10.5
10.75
11
and so on
I would like to convert that to the hour and 15 minute slot
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
View 4 Replies
View Related
May 12, 2008
We are having problems getting the ADO.NET client to failover in a timely manner in a web application. Here's what happens:
1) Database fails over in 3 to 5 seconds
2) Clients attempt to login to primary and receive SQL server not available errors
3) The clients slowly start to connect and succeed without errorson the secondary.
4) We are still see SQL Server login errors on the primary, even after the failover
5) When we failback to the primary, it all happens within a few seconds (no problems)
If we recycle IIS, this problem goes away
Here's what we have tried
1) Turning off connection pooling (has no effect). We also can see the connections get killed on the primary server
2) Applying this hotfix (http://support.microsoft.com/kb/944099)
And yes, we are running a script to sync up the SIDS so that doesn't appear to be a problem. What seems to be happening is the primary server is getting stuck in the ADO.NET application memory. The problem is we don't know how to fix it.
BTW, our hardware is SQL Server 2005, Windows Server 2003, .NET 2.0 (and .NET 3.5)
Let me know if you need additional information. Thanks in advance
View 2 Replies
View Related
Mar 21, 2007
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY
=======
CREATE VIEW HISTORY
AS
SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION
FROM LOCAUDIT
LASTHISTORY
==========
CREATE VIEW LASTHISTORY
AS
SELECT CODE, Max(UpdateDateTime) AS LastUpdated
FROM HISTORY
GROUP BY CODE
UPDATE RETAIL
SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END)
FROM RETAIL AS t4
LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE)
LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime)
LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
View 4 Replies
View Related
Jan 29, 2007
Recently my system encounter some problem when retrieving certain record from MSSQL.
For an example i have a database which contains 1.5 million of members. so i have a perl scripts that will execute to query based on certain range.
the schedule like below:
1 script - 1-250k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 250k-500k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 500k-750k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 750k-1M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1M-1.25M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1.25M-1.50M (Query finish in 1++ hours)
END
After the 4th query, the query seems to work very slow, and this problem only raise on windows 2003 with mssql 2005, current server that run smoothly is win2k with mssql2000.
anyone have any idea on this problem either cause by operating system and database or related to something else?
View 10 Replies
View Related
Mar 1, 2007
Hello friends
what is the right datatype to store the hours and minutes part in the database?
i found some info which says we have to convert the duration(hrs and min) into minutes and then store
is it the right approach?
Regards
Sara
View 1 Replies
View Related
Feb 21, 2007
Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000
View 5 Replies
View Related
May 13, 2008
Hi Guys,
I have data in minutes which is integer value ex 14454.I wanto convert it to days,hours,minutes & seconds.
Is there any simple way?
Regards.
View 2 Replies
View Related
Dec 8, 2007
Hi All,
I want to get the date Difference with Day,hours,minutes,seconds of the given 2 dates.(Say the difference of
12/6/2007 7:00:00 AM, 12/8/2007 8:00:00 AM as 2 days 1:00:00)
Is there any inbuilt function in SSRS is available to implement this. Or any other way to do this.Please help me with this .Thanks in advance.
With Thanks
M.Mahendra
View 2 Replies
View Related
Jul 10, 2015
Currently my script is using the below mentioned query to find the time difference.
DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))
If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?
View 14 Replies
View Related
May 26, 2008
In BIDS, I created a time dimension using the wizard. Unfortunately, the template used by the wizard does not contain Hours, Minutes and Seconds; so, I added them using the designer. Now, when I try to build, deploy and process I get the error: Unable to find matching TimeAttributeType. Cannot anyone explain why this is happening and what can be done about? Is there a template available somewhere that does create time dimensions with time and not just dates?
View 5 Replies
View Related
Dec 13, 2007
Dear all,
I need to ignore the hours minutes and seconds elements of a datetime field - I've got a way of doing this in my select statement but it seems to be this can't be the most efficient way!
CONVERT(datetime, CONVERT(nchar(10), db.tab.field, 101)) AS date_key
Is there a better way?
View 4 Replies
View Related
Jun 25, 2007
The query below runs in sub second time if I don't call it as a stored procedure. I have looked at the execution plan for both the query and the query as a stored procedure and they are the same.
When I put the query into a stored procedure it takes over 2 minutes to run.
All feedback (even the ugly stuff) is more than welcome. I want to master this issue and forever put it behind me.
This is the sql when I just execute it outright:1 DECLARE
2 @WebUserID nvarchar(20)
3 ,@DocumentTypeID int
4 ,@RouteID nvarchar(10)
5 ,@CustomerID nvarchar(15)
6 ,@DocumentIDPrefix nvarchar(20)
7 ,@StartDate datetime
8 ,@EndDate datetime
9 ,@OversoldOnly bit
10 ,@DexCustomersOnly bit
11 ,@DeviationsOnly bit
12 ,@CashNoPaymentOnly bit
13 ,@SignatureName nvarchar(45)
14 ,@SortExpression varchar(200)
15 ,@StartRowIndex int
16 ,@MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 WITH OrderedDocumentHistory AS
32 (
33 SELECT
34 dh.DocumentHistoryID
35 ,dh.DocumentID
36 ,dh.DocumentTypeID
37 ,dh.DocumentTypeDesc
38 ,dh.RouteID
39 ,dh.RouteDesc
40 ,dh.CustomerID
41 ,dh.CustomerName
42 ,dh.DocDate
43 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
44 ,ddt.Description AS 'SignatureReason'
45 ,a.Amount
46 ,ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC) AS 'RowNumber'
47 FROM
48 DocumentHistory dh
49 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
50 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
51 INNER JOIN
52 (
53 SELECT
54 DocumentHistoryID
55 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
56 FROM
57 DocumentHistoryItems dhia
58 GROUP BY
59 dhia.DocumentHistoryID
60 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
61 LEFT OUTER JOIN
62 (
63 SELECT DISTINCT
64 dca.DocumentID
65 ,1 AS 'HasReceipt'
66 FROM
67 DocumentCollections dca
68 ) AS dc ON dh.DocumentID = dc.DocumentID
69 WHERE
70 dh.DocDate BETWEEN @StartDate AND @EndDate
71 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
72 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
73 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
74 AND dh.DocumentID LIKE @DocumentIDPrefix
75 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
76 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
77 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
78 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
79 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
80 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
81 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
82 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
83 OR @WebUserID IS NULL)
84 )
85
86 SELECT
87 DocumentHistoryID
88 ,DocumentID
89 ,DocumentTypeDesc
90 ,RouteID
91 ,RouteDesc
92 ,CustomerID
93 ,CustomerName
94 ,DocDate
95 ,Amount
96 ,HasReceipt
97 ,SignatureReason
98 FROM
99 OrderedDocumentHistory
100 WHERE
101 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows) Here is the sql for creating the stored procedure. 1 CREATE Procedure w_DocumentHistory_Select
2 (
3 @WebUserID nvarchar(20)
4 ,@DocumentTypeID int
5 ,@RouteID nvarchar(10)
6 ,@CustomerID nvarchar(15)
7 ,@DocumentIDPrefix nvarchar(20)
8 ,@StartDate datetime
9 ,@EndDate datetime
10 ,@OversoldOnly bit
11 ,@DexCustomersOnly bit
12 ,@DeviationsOnly bit
13 ,@CashNoPaymentOnly bit
14 ,@SignatureName nvarchar(45)
15 ,@SortExpression varchar(200)
16 ,@StartRowIndex int
17 ,@MaximumRows int
18 )
19 AS
20 SET NOCOUNT ON
21
22 IF LEN(@SortExpression) = 0 OR @SortExpression IS NULL
23 SET @SortExpression = 'Number DESC'
24
25 IF @StartRowIndex IS NULL
26 SET @StartRowIndex = 0
27
28 IF @MaximumRows IS NULL
29 SELECT
30 @MaximumRows = COUNT(dh.DocumentHistoryID)
31 FROM
32 DocumentHistory dh;
33
34 WITH OrderedDocumentHistory AS
35 (
36 SELECT
37 dh.DocumentHistoryID
38 ,dh.DocumentID
39 ,dh.DocumentTypeID
40 ,dh.DocumentTypeDesc
41 ,dh.RouteID
42 ,dh.RouteDesc
43 ,dh.CustomerID
44 ,dh.CustomerName
45 ,dh.DocDate
46 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
47 ,ddt.Description AS 'SignatureReason'
48 ,a.Amount
49 ,CASE
50 WHEN @SortExpression = 'Number DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID DESC))
51 WHEN @SortExpression = 'Number ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID ASC))
52 WHEN @SortExpression = 'CustomerName DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName DESC))
53 WHEN @SortExpression = 'CustomerName ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName ASC))
54 WHEN @SortExpression = 'CompletedDate DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC))
55 WHEN @SortExpression = 'CompletedDate ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate ASC))
56 WHEN @SortExpression = 'RouteDescription DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc DESC))
57 WHEN @SortExpression = 'RouteDescription ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc ASC))
58 END AS 'RowNumber'
59 FROM
60 DocumentHistory dh
61 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
62 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
63 INNER JOIN
64 (
65 SELECT
66 DocumentHistoryID
67 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
68 FROM
69 DocumentHistoryItems dhia
70 GROUP BY
71 dhia.DocumentHistoryID
72 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
73 LEFT OUTER JOIN
74 (
75 SELECT DISTINCT
76 dca.DocumentID
77 ,1 AS 'HasReceipt'
78 FROM
79 DocumentCollections dca
80 ) AS dc ON dh.DocumentID = dc.DocumentID
81 WHERE
82 dh.DocDate BETWEEN @StartDate AND @EndDate
83 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
84 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
85 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
86 AND dh.DocumentID LIKE @DocumentIDPrefix
87 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
88 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
89 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL((SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
90 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
91 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
92 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
93 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
94 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
95 OR @WebUserID IS NULL)
96 )
97 SELECT
98 DocumentHistoryID
99 ,DocumentID
100 ,DocumentTypeDesc
101 ,RouteID
102 ,RouteDesc
103 ,CustomerID
104 ,CustomerName
105 ,DocDate
106 ,Amount
107 ,HasReceipt
108 ,SignatureReason
109 FROM
110 OrderedDocumentHistory
111 WHERE
112 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows)
Here is the code for calling the stored procedure:1 DECLARE @RC int
2 DECLARE @WebUserID nvarchar(20)
3 DECLARE @DocumentTypeID int
4 DECLARE @RouteID nvarchar(10)
5 DECLARE @CustomerID nvarchar(15)
6 DECLARE @DocumentIDPrefix nvarchar(20)
7 DECLARE @StartDate datetime
8 DECLARE @EndDate datetime
9 DECLARE @OversoldOnly bit
10 DECLARE @DexCustomersOnly bit
11 DECLARE @DeviationsOnly bit
12 DECLARE @CashNoPaymentOnly bit
13 DECLARE @SignatureName nvarchar(45)
14 DECLARE @SortExpression varchar(200)
15 DECLARE @StartRowIndex int
16 DECLARE @MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 EXECUTE @RC = [Odom].[dbo].[w_DocumentHistory_Select]
32 @WebUserID
33 ,@DocumentTypeID
34 ,@RouteID
35 ,@CustomerID
36 ,@DocumentIDPrefix
37 ,@StartDate
38 ,@EndDate
39 ,@OversoldOnly
40 ,@DexCustomersOnly
41 ,@DeviationsOnly
42 ,@CashNoPaymentOnly
43 ,@SignatureName
44 ,@SortExpression
45 ,@StartRowIndex
46 ,@MaximumRows
View 3 Replies
View Related
May 21, 2008
After differential restore I start Remedy service. It starts in few seconds.
After full restore the same service takes 15 minutes to start. Bothe the things are done through SQL service agent. Even manual restaring the service also takes 15 minutes after full restore. WHy is it happening this way?
View 5 Replies
View Related
Jan 26, 2007
I have an SSIS package that when run from Visual Studio takes 1 minute or less to complete. When I schedule this package to run as a SQL Server job it takes 5+ and sometimes hangs complaining about buffers.
The server is a 4 way 3ghz Xeon (dual core) with 8GB ram and this was the only package running.
When I look in the log I see that the package is running and processing data, although very very very very very slowly.
Has anyone else experienced this?
Thanks for your help
Marcus
View 5 Replies
View Related
Sep 12, 2007
hello
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
-- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
@TotalMarket varchar(255),
@datasetname varchar(255),
@VisibleProducts varchar(500),
@DoctorSubTerritory varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal ,
SUM
(
CASE
WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN xpomeasures.rxvalues
ELSE 0
END
) AS totalrxvalues,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc <> 'Non -')
AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS rxrcount,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [high],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Medium')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [medium],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very high],
(SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography
ON xpomeasures.geographyid = xpogeography.geographyid
WHERE (xpomarket.productdesc = @TotalMarket)
AND (xpomeasures.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (xpomarket.datasetname = @DatasetName)
AND (xpogeography.datasetname = @DatasetName)
AND (xpogeography.doctorgroup = a.doctorgroup)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
) AS rxvaluesdoctorgroup
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN (
SELECT xpogeography.doctorgroup,
SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
WHERE xpomeasures.datasetname = @DatasetName
and (xpomarket.datasetname = @DatasetName)
and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
GROUP BY xpogeography.doctorgroup
) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname = @DatasetName)
AND (xpomarketsize.datasetname = @DatasetName)
AND (xpoproductsize.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (a.datasetname = @DatasetName)
AND (XpoMarket.datasetname = @DatasetName)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
AND (a.doctorsubterritory = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
fn_mvparamsorted.parame ,
fn_mvparamsorted.sortid ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal
ORDER BY s.totalmarketrank,
a.doctorgroup ,
fn_mvparamsorted.sortid
END
View 4 Replies
View Related
Sep 16, 2005
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
View 1 Replies
View Related
