Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: SQL Query times out

    Code:
    SELECT     TOP (10) CONVERT(VARCHAR(12), order_date, 103) AS Order_Date, COUNT(*) AS Orders,
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_cps
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_call
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_call_isdn
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_orderdetails_broadband
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_nongeo
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_private
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_other
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_hardware
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) +
                              (SELECT     COUNT(*) AS Expr1
                                FROM          tblFixedLine_featurelines
                                WHERE      (CONVERT(VARCHAR(12), created_date, 103) = CONVERT(VARCHAR(12), tblFixedLine_order_summary.order_date, 103))) AS Lines
    FROM         tblFixedLine_order_summary
    GROUP BY CONVERT(VARCHAR(12), order_date, 103), DATEPART(yyyy, order_date), DATEPART(mm, order_date), DATEPART(dd, order_date)
    ORDER BY DATEPART(yyyy, order_date), DATEPART(mm, order_date), DATEPART(dd, order_date)
    Any ideas how i can get this to run on my server?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't convert dates to strings for starters.

    All these different fixed line tables - do they have lots of similar columns?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, remove ALL the DATEPART stuff.

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    How can i remove the time element from a datetime field without converting to a string? If you could show me thatm i would be extremely greatful.

    The Fixedline table are similar but not identical, whats your thinking?

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    I added the datepart stuff so that i could do ORDER BY the date (probably becuase i converted to strings but it was ordering by day without)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by stephena View Post
    I added the datepart stuff so that i could do ORDER BY the date (probably becuase i converted to strings but it was ordering by day without)
    Exactly - leave dates as dates and it will order fine.

    perhaps this:
    Code:
    SELECT  TOP (10) 
            DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.created_date), '19000101')
          , Orders          =  COUNT(*) 
          , Lines           =  (SELECT  COUNT(*)
                                FROM    
                                        (
                                            SELECT   created_date
                                            FROM    tblFixedLine_cps
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_call
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_call_isdn
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_orderdetails_broadband
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_nongeo
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_private
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_other
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_hardware
                                            UNION ALL
                                            SELECT   created_date
                                            FROM    tblFixedLine_featurelines
                                        ) AS lines
                                WHERE   lines.created_date >= DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.created_date), '19000101')
                                    AND lines.created_date <  DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.created_date) + 1, '19000101'))
    FROM    tblFixedLine_order_summary
    GROUP BY 
            DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.created_date), '19000101')
    ORDER BY 
            DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.created_date), '19000101')

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    Thanks Pootle Flump,

    I have refined to:
    Code:
    SELECT     TOP (10) DATEADD(d, DATEDIFF(d, '19000101', order_date), '19000101') AS Expr1, COUNT(*) AS Orders,
                              (SELECT     COUNT(*) AS Expr1
                                FROM          (SELECT     created_date
                                                        FROM          tblFixedLine_cps
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_call
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_call_isdn
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_orderdetails_broadband
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_nongeo
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_private
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_other
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_hardware
                                                        UNION ALL
                                                        SELECT     created_date
                                                        FROM         tblFixedLine_featurelines) AS lines
                                WHERE      (created_date >= DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.order_date), '19000101')) AND 
                                                       (created_date < DATEADD(d, DATEDIFF(d, '19000101', tblFixedLine_order_summary.order_date) + 1, '19000101'))) AS Lines
    FROM         tblFixedLine_order_summary
    GROUP BY DATEADD(d, DATEDIFF(d, '19000101', order_date), '19000101')
    ORDER BY Expr1
    however i get the error 'Column dbo.order_summary.order_date is invalid in the select list becuase it is not contained in theeither the aggreage function or the Group BY cluase'.

    I have added order_date without the Dateadd/Datediff and then there are multiple entries for 17/01/2010 00:00:00 for example.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you supply DDL?

  9. #9
    Join Date
    Jul 2009
    Posts
    32
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_featurelines](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[fllog_ref] [int] NULL,
    	[order_summary] [nchar](30) NULL,
    	[created_by] [nchar](20) NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL,
    	[new_transfer_move] [nchar](50) NULL,
    	[est_monthly_call_spend] [nchar](10) NULL,
    	[cli] [nchar](15) NULL,
    	[cost_centre] [nchar](20) NULL,
    	[current_provider] [nchar](50) NULL,
    	[bt_account_no] [nchar](20) NULL,
    	[address_ref] [nchar](10) NULL,
    	[line_description] [nchar](50) NULL,
    	[rental_charge] [decimal](18, 2) NULL,
    	[connection_charge] [decimal](18, 2) NULL,
     CONSTRAINT [PK_tblFixedLine_featurelines] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_cps]    Script Date: 06/08/2010 15:15:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_cps](
    	[order_summary] [nchar](18) NULL,
    	[address_ref] [nchar](10) NULL,
    	[cli] [nchar](30) NULL,
    	[product] [nchar](3) NULL,
    	[line_description] [nchar](50) NULL,
    	[cost_centre] [nchar](35) NULL,
    	[provider] [nchar](30) NULL,
    	[postcode] [nchar](10) NULL,
    	[monthly_spend] [decimal](18, 2) NULL,
    	[fllog_ref] [int] NOT NULL,
    	[createdby] [nchar](30) NULL,
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](2500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_other]    Script Date: 06/08/2010 15:16:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_other](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[created_by] [nchar](30) NULL,
    	[order_summary] [nchar](20) NULL,
    	[product_name] [nchar](20) NULL,
    	[programming_req] [nchar](10) NULL,
    	[bandwidth] [nchar](10) NULL,
    	[contract_length] [nchar](10) NULL,
    	[connection_charge] [decimal](18, 2) NULL,
    	[monthly_rental_charges] [decimal](18, 2) NULL,
    	[btglobix_quote_reference] [nchar](10) NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](2500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_private]    Script Date: 06/08/2010 15:16:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_private](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[created_by] [nchar](30) NULL,
    	[order_summary] [nchar](30) NULL,
    	[product] [nchar](30) NULL,
    	[billing_cli] [nchar](30) NULL,
    	[total_connection_charges] [decimal](18, 2) NULL,
    	[total_monthly_charges] [decimal](18, 2) NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](2500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_order_summary]    Script Date: 06/08/2010 15:16:31 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_order_summary](
    	[fllog_ref] [int] IDENTITY(1,1) NOT NULL,
    	[order_date] [datetime] NULL,
    	[new_existing_customer] [nchar](18) NULL,
    	[customer_name] [nchar](60) NULL,
    	[contact_name] [nchar](40) NULL,
    	[email_address] [nchar](50) NULL,
    	[telephone_number] [nchar](15) NULL,
    	[prospect_number] [nchar](10) NULL,
    	[vbs_account_number] [nchar](20) NULL,
    	[sales_person] [nchar](50) NULL,
    	[sales_region] [nchar](50) NULL,
    	[sales_dise_code] [nchar](10) NULL,
    	[sales_channel] [nchar](50) NULL,
    	[referrer_name] [nchar](30) NULL,
    	[order_completed_by] [nchar](40) NULL,
    	[cps] [nchar](5) NULL,
    	[ida] [nchar](5) NULL,
    	[calls_and_access_lines] [nchar](5) NULL,
    	[broadband] [nchar](5) NULL,
    	[non_geographic_numbers] [nchar](5) NULL,
    	[private_circuits] [nchar](5) NULL,
    	[other_data_products] [nchar](5) NULL,
    	[total_monthly_rental_charges] [decimal](18, 2) NULL,
    	[total_monthly_call_charges] [decimal](18, 2) NULL,
    	[total_monthly_revenue] [decimal](18, 2) NULL,
    	[connection_charge] [decimal](18, 2) NULL,
    	[notes] [nchar](1000) NULL,
    	[fl_advisor_pulled] [nchar](21) NULL,
    	[pulled_date] [datetime] NULL,
    	[fl_advisor_status] [nchar](10) NULL,
    	[current_fl_advisor] [nchar](21) NULL,
    	[status] [nchar](30) NULL,
    	[bid_model] [nchar](80) NULL,
    	[usage_tariff] [nchar](50) NULL,
    	[pricing] [nchar](50) NULL,
    	[bp_z_code] [nchar](20) NULL,
    	[credit_check_auth_code] [nchar](20) NULL,
    	[bp_dealer_manager] [nchar](35) NULL,
    	[reject_reason] [nchar](250) NULL,
    	[reject_username] [nchar](30) NULL,
    	[reject_date] [datetime] NULL,
    	[cco_applied] [nchar](20) NULL,
    	[blr_applied] [nchar](20) NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_call]    Script Date: 06/08/2010 15:15:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_call](
    	[item_no] [nchar](10) NULL,
    	[order_summary] [nchar](30) NULL,
    	[address_ref] [nchar](10) NULL,
    	[move_to_address] [nchar](10) NULL,
    	[new_transfer_move] [nchar](25) NULL,
    	[cli] [nchar](30) NULL,
    	[cps_live] [nchar](20) NULL,
    	[product] [nchar](35) NULL,
    	[no_of_aux_lines] [nchar](10) NULL,
    	[isdn30_channels] [nchar](10) NULL,
    	[isdn30_length] [nchar](10) NULL,
    	[line_description] [nchar](70) NULL,
    	[account_cost_centre] [nchar](35) NULL,
    	[current_provider] [nchar](15) NULL,
    	[installation_postcode] [nchar](15) NULL,
    	[bt_account_no] [nchar](30) NULL,
    	[directory_enq] [nchar](10) NULL,
    	[company_name] [nchar](60) NULL,
    	[service_maintanence] [nchar](50) NULL,
    	[ss1_monthly] [nchar](10) NULL,
    	[service_maintanence2] [nchar](50) NULL,
    	[ss2_monthly] [nchar](10) NULL,
    	[service_maintanence3] [nchar](50) NULL,
    	[ss3_monthly] [nchar](10) NULL,
    	[ss_one_off] [nchar](10) NULL,
    	[est_monthly_call_spend] [decimal](18, 2) NULL,
    	[total_conn_charge] [decimal](18, 2) NULL,
    	[total_rental_charge] [decimal](18, 2) NULL,
    	[redcare] [nchar](10) NULL,
    	[totalcare] [nchar](10) NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[created_by] [nchar](30) NULL,
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](3000) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_hardware]    Script Date: 06/08/2010 15:16:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_hardware](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[hardware_model] [nchar](60) NULL,
    	[hardware_price] [decimal](18, 2) NULL,
    	[additional_microfilters] [nchar](3) NULL,
    	[microfilter_cost] [decimal](18, 2) NULL,
    	[address_ref] [nchar](10) NULL,
    	[cli] [nchar](15) NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](500) NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[created_date] [datetime] NULL,
    	[created_by] [nchar](40) NULL,
    	[completed_date] [datetime] NULL,
     CONSTRAINT [PK_tblFixedLine_eof_hardware] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_call_isdn]    Script Date: 06/08/2010 15:15:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_call_isdn](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[item_no] [nchar](10) NULL,
    	[new_transfer] [nchar](20) NULL,
    	[no_of_ddis] [nchar](10) NULL,
    	[no_of_msn] [nchar](10) NULL,
    	[range_start] [nchar](15) NULL,
    	[range_finish] [nchar](15) NULL,
    	[range_check] [nchar](10) NULL,
    	[no_of_snddis] [nchar](10) NULL,
    	[range_setup] [nchar](10) NULL,
    	[ddi_setup] [nchar](10) NULL,
    	[ddi_rental] [nchar](10) NULL,
    	[created_by] [nchar](30) NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](2500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[tblFixedLine_orderdetails_broadband]    Script Date: 06/08/2010 15:16:51 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblFixedLine_orderdetails_broadband](
    	[order_summary] [nchar](18) NULL,
    	[order_type] [nchar](18) NULL,
    	[package] [nchar](27) NULL,
    	[telephone_number] [nchar](15) NULL,
    	[new_line_installation] [nchar](3) NULL,
    	[installation_address] [nchar](5) NULL,
    	[connection_charge] [decimal](18, 2) NULL,
    	[monthly_rental_charge] [decimal](18, 2) NULL,
    	[mac_number] [nchar](20) NULL,
    	[current_provider] [nchar](10) NULL,
    	[additional_ip] [nchar](10) NULL,
    	[hardware_model] [nchar](60) NULL,
    	[hardware_price] [nchar](10) NULL,
    	[additional_microfilters] [nchar](3) NULL,
    	[microfilter_cost] [nchar](10) NULL,
    	[part_of_vpn] [nchar](3) NULL,
    	[billing_address] [nchar](10) NULL,
    	[hardware_delivery_address] [nchar](10) NULL,
    	[account_level_cost_centre] [nchar](35) NULL,
    	[line_description] [nchar](50) NULL,
    	[vodabb_emailaddress] [nchar](30) NULL,
    	[created_by] [nchar](21) NULL,
    	[fllog_ref] [nchar](10) NULL,
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[status] [nchar](30) NULL,
    	[notes] [nchar](2500) NULL,
    	[created_date] [datetime] NULL,
    	[completed_date] [datetime] NULL
    ) ON [PRIMARY]
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •