Project Detail

Description

Query Designer is needed for joined tables in MS SQL database. This project requires the knowledge of Structured Query Language and classic ASP.

If any similar projects done, please send example.

PREVIOUS PROJECT REFERENCES ARE VERY IMPORTANT, IF YOU CAN NOT PROVIDE GOOD PREVIOUS PROJECT REFERENCES (RELATED PROJECTS TO THE SUBJECT), PLEASE DO NOT BOTHER SUBMITTING BID.

1) User should be able to select output columns (SELECT mapping)
2) User should be able to specify column conditions (WHERE clause mapping)
3) User should be able to join tables (with INNER JOIN, sample tables are listed in this document. Join columns would be specified before, not by the user)
4) User should be able to use group by, maximum, minimum, average functions
5) User should be able to save a query he/she built using this query editor for later use
6) User should be able to exclude another query results ( ... NOT IN (saved_query) )
7) User should be able to export the query results to a TAB delimited text file
8) Admin should be able to choose which tables are selectable by users (somewhere within the code, there is no need to setup a seperate admin screen).

For example; somehwere in the code, or in a configuration file I specified that "new_tbl_customers", "new_tbl_order_master", and "new_tbl_order_detail" are the 3 tables that will be used by the query designer.
Then; users will go to query designer and see there are upto 3 tables selectable.


Example 1:
----------
SELECT TABLES: [X]Customers []Orders []Order Detail (User selects the first table. Colums from that table appears below)

SELECT OUTPUT COLUMNS: [X]First Name []Last Name [X]Email []Company []Address []City []State ........ (User selects first name and email)

FILTERS: [First Name ] [Like ] [John%] [REMOVE] (First two are combo box, user selects a column name and a filter, then enters the condition in the text box, then clicks ADD)
[select a column] [filters] [ ] [ADD] (user should be able to add as many filters as they want, or REMOVE the entered filter)



Save query as: [_____________] [SAVE] (User should be able to enter a name such as, "All the Johns" and save this for later use)

Run the Query [TO SCREEN] [TO FILE] (One button outputs to screen, other one outputs to a file)


So, once this form is submitted, the ASP code would generate a query something like this:
SELECT customer_bill_first, customer_bill_email from new_tbl_customers WHERE customer_bill_first LIKE 'John%'




Example 2:
----------
SELECT TABLES: [X]Customers [X]Orders []Order Detail (user selects the first one, colums from that table appears below)

SELECT OUTPUT COLUMNS: [X]First Name []Last Name [X]Email []Company .... []Order# [X]Order Date (User selects first name, email, and the order date)

GROUP BY: [Group]First Name [skip]Last Name [Group]Email [skip]Company .... [skip]Order# [Maximum]Order Date (user groups by name, email; selects the latest order date using Maximum)

FILTERS: [Order Date ] [ > ] [1/1/2008] [REMOVE] (First two are combo box, user selects a column name and a filter, then enters the condition in the text box, then clicks ADD)
[select a column] [filters] [ ] [ADD] (user should be able to add as many filters as they want, or REMOVE the entered filter)


EXCLUDE: [All The Johns] (user wants to exclude all the Johns, so he selects a previously saved query from the combo box to exclude)


Save query as: [_____________] [SAVE] (User should be able to enter a name such as, "Customer Name and Email" and save this for later use)

Run the Query [TO SCREEN] [TO FILE] (One button outputs to screen, other one outputs to a file)










--------------------------------------------------------
TABLES USED IN THIS EXAMPLE
-- PLEASE NOTE --

If you can satisfy the Requirement #8 above,
you do not have to stick with these tables.
These are just examples
--------------------------------------------------------


/*First table is customer table which contains basic customer information such as name, address, and email.*/


/****** Object: Table [dbo].[new_tbl_customers] Script Date: 12/16/2008 11:22:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[new_tbl_customers](
[customer_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_first] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_last] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_mi] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_bill_company] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_bill_email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_bill_street] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_city] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_state_id] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_zip] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_bill_country_id] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[customer_bill_phone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_bill_fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_subscribed] [bit] NOT NULL,
[customer_ref] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_http_ref] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_from] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_store_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[no_email] [bit] NULL CONSTRAINT [DF_new_tbl_customers_no_email] DEFAULT (0),
[bad_email] [bit] NULL,
[remind_every] [tinyint] NULL,
[added_on] [datetime] NULL CONSTRAINT [DF_new_tbl_customers_added_on] DEFAULT (getdate()),
[rep_1_id] [tinyint] NULL,
[rep_2_id] [tinyint] NULL,
[customer_notes] [varchar](2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_printer_model] [int] NULL,
[customer_terms] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_credit] [money] NULL,
[customer_ns_id] [int] NULL,
[customer_level] [tinyint] NULL,
[customer_last_updated_on] [datetime] NULL,
[customer_last_updated_by] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_status] [tinyint] NULL CONSTRAINT [DF_new_tbl_customers_customer_status] DEFAULT (2),
[tax_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duns_no] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[org_type] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[req_credit_limit] [money] NULL,
[ap_first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ap_last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ap_email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[customer_dropships] [bit] NULL,
CONSTRAINT [PK_new_tbl_customers] PRIMARY KEY CLUSTERED
(
[customer_id] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



/* Second table is Orders Table, and it can be joined with the first table using customer_id column*/

/****** Object: Table [dbo].[new_tbl_order_master] Script Date: 12/16/2008 11:23:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[new_tbl_order_master](
[order_id] [int] NOT NULL,
[customer_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[order_date] [datetime] NULL,
[order_subtotal] [money] NULL,
[order_tax] [money] NULL,
[order_shipping] [money] NULL,
[order_coupon] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_discount] [money] NULL,
[order_grandtotal] [money] NULL,
[order_ref] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_http_ref] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_avsdata] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_pnref] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_shipping_info_id] [int] NOT NULL,
[order_shipment_date] [smalldatetime] NULL,
[order_shipment_status_id] [tinyint] NULL CONSTRAINT [DF_new_tbl_order_master_order_shipment_status_id] DEFAULT (0),
[order_shipping_method_id] [tinyint] NULL,
[order_tracking_number] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_store_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_note] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_comment] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_invoice] [bit] NULL,
[is_changed] [bit] NULL,
[is_dropship] [bit] NULL,
[order_shipment_date_2] [smalldatetime] NULL,
[order_shipping_method_id_2] [tinyint] NULL,
[order_tracking_number_2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_shipment_date_3] [smalldatetime] NULL,
[order_shipping_method_id_3] [tinyint] NULL,
[order_tracking_number_3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_ref_amount] [smallmoney] NULL CONSTRAINT [DF_new_tbl_order_master_order_ref_amount] DEFAULT (0.00),
[order_ref_check] [smallint] NULL,
[order_eta] [smalldatetime] NULL,
[auth_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[search_function] [smallint] NULL,
[ajax_fob] [smallint] NULL,
[ajax_mof] [smallint] NULL,
[ajax_oob] [smallint] NULL,
[ajax_mob] [smallint] NULL,
[ref_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ref_4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_new_tbl_order_master] PRIMARY KEY CLUSTERED
(
[order_id] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF




/* Third table is Orders Details table, and it can be joined with the second table using order_id column*/


GO
/****** Object: Table [dbo].[new_tbl_order_detail] Script Date: 12/16/2008 11:24:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[new_tbl_order_detail](
[order_id] [int] NOT NULL,
[product_sku] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[order_qty] [tinyint] NULL,
[backorder_qty] [tinyint] NULL CONSTRAINT [DF_new_tbl_order_detail_backorder_qty] DEFAULT (0),
[product_price] [smallmoney] NOT NULL CONSTRAINT [DF_new_tbl_order_detail_product_price] DEFAULT (0),
[hold_qty] [tinyint] NULL,
CONSTRAINT [PK_new_tbl_order_detail] PRIMARY KEY CLUSTERED
(
[order_id] ASC,
[product_sku] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF