January 15, 2014 at 10:15 am
help i have created following three table
CREATE TABLE [dbo].[TBL_Item](
[ItemID] [varchar](50) NOT NULL,
[ItemName] [varchar](200) NOT NULL,
[ItemDescription] [varchar](max) NULL,
[Qty] [numeric](18, 2) NULL,
[Unit] [bigint] NULL,
[GroupID] [int] NOT NULL,
[UserID] [nchar](10) NULL,
[CreateDate] [date] NULL,
[GoodType] [char](1) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[TBL_Inward](
[InwardID] [int] IDENTITY(1,1) NOT NULL,
[InwardDate] [date] NOT NULL,
[InwardItemID] [varchar](50) NOT NULL,
[SchemeID] [int] NULL,
[Qty] [numeric](18, 2) NOT NULL,
[Unit] [bigint] NOT NULL,
[PartyID] [varchar](50) NOT NULL,
[BillNo] [varchar](50) NULL,
[BillDate] [date] NULL,
[PageNo] [varchar](50) NULL,
[InwardDesc] [varchar](max) NULL,
CONSTRAINT [PK_Inward] PRIMARY KEY CLUSTERED
(
[InwardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[TBL_Outward](
[OutwardID] [int] IDENTITY(1,1) NOT NULL,
[OutwardDate] [date] NOT NULL,
[OutwardItemID] [varchar](50) NOT NULL,
[SchemeID] [int] NOT NULL,
[Qty] [numeric](18, 3) NOT NULL,
[Unit] [bigint] NOT NULL,
[PartyID] [varchar](50) NOT NULL,
[IndentNo] [varchar](50) NULL,
[IndentDate] [date] NULL,
[PageNo] [varchar](50) NULL,
[OutwardDesc] [varchar](max) NULL,
CONSTRAINT [PK_Outward] PRIMARY KEY CLUSTERED
(
[OutwardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
i Want create ledger
ItemID | Date | Item Name | Inward | Outward | Balance|
-----------------------------------------------------------------
1 | 02/01/2014 | Page A4 | 5 | | 5 |
1 | 02/01/2014 | Page A4 | | 2 | 3 |
1 | 03/01/2014 | Page A4 | | 1 | 2 |
1 | 02/01/2014 | Page A4 | 1 | | 3 |
-----------------------------------------------------------------
Item detail save in Item Detail in TBL_Item
Item inward Detail Save in TBL_Inward
Item outward Detail Save in TBL_Outward
I want Date of Inward and Outward tables shoen in one column and create another new column Balce where shown balance
Kindly help us
January 15, 2014 at 12:14 pm
Awesome job posting clean ddl. There are a few questions here. What is the balance? I assume it is some sort of calculation between inward and outward? Also, you said you want the date from inward and outward in a single column. This is impossible and doesn't make sense if you think about it. You have to pick one of the two values for a given column.
Can you post a few of data for each of those three tables and then put together what the desired output is based on that data? This should be a pretty simple query once we know the details of what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2014 at 12:39 pm
I guess this could help you get started and then follow a running total option such as the Quirky Update[/url].
SELECT ItemID, [InwardDate] AS [Date], [ItemName], [Qty] AS Inward, 0 ASOutward
FROM dbo.TBL_Inward inw
JOIN dbo.TBL_Item i ON inw.InwardItemID = i.ItemID
UNION ALL
SELECT ItemID, [OutwardDate] AS [Date], [ItemName], 0 AS Inward, [Qty] AS Outward
FROM dbo.[TBL_Outward] out
JOIN dbo.TBL_Item i ON out.OutwardItemID = i.ItemID
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply