Curtis Long
asked on
SQL 2008 R2 to Excel
I have a table in SQl 2008R2 like the following:
USE [IEOPC]
GO
/****** Object: Table [dbo].[INSPECTION_v2] Script Date: 01/28/2013 07:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[INSPECTION_v2](
[DATE] [date] NULL,
[APPLICANT] [varchar](max) NULL,
[LOCATION] [varchar](max) NULL,
[REMARKS] [varchar](max) NULL,
[QUANTITY] [numeric](18, 0) NULL,
[COUNT] [numeric](18, 0) NULL,
[CONTAINER] [varchar](max) NULL,
[GRADE] [varchar](50) NULL,
[CERTIFICATE NUMBER] [numeric](18, 0) NULL,
[CAR NUMBER] [numeric](18, 0) NULL,
[QUANTITY THOUSAND WEIGHT] AS ([QUANTITY]*[COUNT]),
[TIME INSPECTION BEGUN] [time](0) NULL,
[TIME INSPECTION COMPLETED] [time](0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And data like the attached spread sheet.
The tab called database is the data that populates the table mentioned above.
I need to be able to input a date range in the tab called Input and then have the tab called LIGP and EOPC add the certificates associated with their company names together for each day in the date range and add them to the correct columns in the tab.
For example:
You will see that LIGP has 12 line items for 10-1-2012 from certificate 73496 totaling 744,372.
I would need all the certificates for 10-1-2012 added together and put the date, Certificate and weight in the appropriate columns. Also the weight column needs to be divided by 100 as the weight column is hundred weight.
This would need to be done for each certificate and each applicant in the provided date range.
Where would I start with this??
Thanks!!
USE [IEOPC]
GO
/****** Object: Table [dbo].[INSPECTION_v2] Script Date: 01/28/2013 07:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[INSPECTION_v2](
[DATE] [date] NULL,
[APPLICANT] [varchar](max) NULL,
[LOCATION] [varchar](max) NULL,
[REMARKS] [varchar](max) NULL,
[QUANTITY] [numeric](18, 0) NULL,
[COUNT] [numeric](18, 0) NULL,
[CONTAINER] [varchar](max) NULL,
[GRADE] [varchar](50) NULL,
[CERTIFICATE NUMBER] [numeric](18, 0) NULL,
[CAR NUMBER] [numeric](18, 0) NULL,
[QUANTITY THOUSAND WEIGHT] AS ([QUANTITY]*[COUNT]),
[TIME INSPECTION BEGUN] [time](0) NULL,
[TIME INSPECTION COMPLETED] [time](0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And data like the attached spread sheet.
The tab called database is the data that populates the table mentioned above.
I need to be able to input a date range in the tab called Input and then have the tab called LIGP and EOPC add the certificates associated with their company names together for each day in the date range and add them to the correct columns in the tab.
For example:
You will see that LIGP has 12 line items for 10-1-2012 from certificate 73496 totaling 744,372.
I would need all the certificates for 10-1-2012 added together and put the date, Certificate and weight in the appropriate columns. Also the weight column needs to be divided by 100 as the weight column is hundred weight.
This would need to be done for each certificate and each applicant in the provided date range.
Where would I start with this??
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What I dont know is how to set the "Date Range tab" so the query will reflect the date I type in the space there.