July 13, 2011 at 2:56 am
Hi Guys,
I have the following requirement:
My main table is called File. This is the structure:
CREATE TABLE [dbo].[File](
[FileID] [bigint] IDENTITY(1,1) NOT NULL,
[VesselID] [bigint] NULL,
[VoyageNo] [varchar](255) NULL,
[OpenedDate] [datetime] NULL,
[FileStatusID] [int] NULL CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
then I have a Timesheets table. Each file can have one or many timesheets
CREATE TABLE [dbo].[TimeSheet](
[TimeSheetID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[UserID] [uniqueidentifier] NULL,
[EffDate] [datetime] NULL,
[Units] [decimal](18, 0) NULL,
[RateTypeID] [bigint] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_TimeSheet] PRIMARY KEY CLUSTERED
(
[TimeSheetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Now I need to create another table with a single field. It will contain data like Office Fee, Bank Charges, Entertainment. Basically different types of costings.
Now I need to create a view or stored proc. not sure which would be best. I need to list each of the costings from the costings table I described above as well as the costs from the TimeSheets table. So my end result would be:
(these are from costings table)
Office Fee R1000
Bank Charges R800
Entertainment R1000
(these must pull from TimeSheet table)
Telephone R500
Fax R300
On my front end I will have a grid with all those costings, and then a column for the user to capture the values. Please give me so pointers on the best way of achieving this task
July 13, 2011 at 4:15 am
Can u Plz tell me some more details...
There is 2 two tables u listed there
1) File --- menas main table
2)time Sheet --
ur mentioned one more table Costing table :-
can u plz give me the Costing table structure
July 13, 2011 at 4:20 am
hi, this is the costings table. I just created it now:-P
CREATE TABLE [dbo].[Costings](
[CostingID] [bigint] IDENTITY(1,1) NOT NULL,
[Description] [varchar](150) NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_Costings] PRIMARY KEY CLUSTERED
(
[CostingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
July 13, 2011 at 4:32 am
The above two tables are having the relation with the File and wt abt the costing table
and give me more explanation abt the Problem then i can help u in this regard...
July 13, 2011 at 4:48 am
Hi again,
the costings table isn't relalted to file. I will probably need an intercepting table to save a costingid with the fileid. So that tables sole purpose would be to hold costings for a file. It would have ID, FileID and CostingID, Value fields.
The problem is, I need to create a view or sp that can display each costing field(probably need a cross join to the file table), then get the corresponding values for the costing from the intercepting table, as well as get the relevant costs from TimeSheet. Sorry I know my explanation is bad but I can't think of a better way of putting this.
July 13, 2011 at 5:01 am
Not sure what are you looking for and for what purpose. can you elaborate more with Sample data and proper structure?
Abhijit - http://abhijitmore.wordpress.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply