June 17, 2009 at 2:24 pm
Hello friends,
Im very new in this and tried a lot to make it by myself bud it looks that i can use some help.
I have this tables:
CREATE TABLE [dbo].[receipt](
[oreceiptnr] [int] NOT NULL,
[oleveranciernr] [int] NULL,
[odate] [datetime] NULL,
CONSTRAINT [PK_receipt] PRIMARY KEY CLUSTERED
(
[oreceiptnr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------
CREATE TABLE [dbo].[spend](
[ospendnr] [int] NOT NULL,
[oklantnr] [int] NOT NULL,
[odate] [datetime] NULL,
CONSTRAINT [PK_spend] PRIMARY KEY CLUSTERED
(
[ospendnr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------
CREATE TABLE [dbo].[receiptregel](
[oreceiptnr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [nchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
------------------------------------
CREATE TABLE [dbo].[spendregel](
[ospendnr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [int] NULL
) ON [PRIMARY]
--------------------------------------
Now i have to make a view to show all mutations per article.
The result most show:
date, type, amount of receipt, amount of spend (of a specific article).
The end result most be like this:
date--------type--------amount of receipt-------amount of spend
1-1-2009 receipt 10
2-1-2009 spend 6
3-1-2009 spend 3
4-2-2009 receipt 12
I hope it's clear and you can help me with this issue.
Thanks.
June 17, 2009 at 2:37 pm
Please provide sample data as described in the link in my signature. The table defs are a first step, but the table should have some data, too.
Also, it would be great if you could show us what you've tried so far and what you're struggling with...
June 24, 2009 at 4:28 am
Hi Imu92,
These are the data:
INSERT INTO receipt VALUES(1,1,1-1-2009)
INSERT INTO receipt VALUES(2,1,4-2-2009)
INSERT INTO spend VALUES(1,1,2-1-2009)
INSERT INTO spend VALUES(2,1,3-1-2009)
INSERT INTO receiptregel VALUES(1,1,10)
INSERT INTO receiptregel VALUES(2,1,10)
INSERT INTO spendregel VALUES(1,1,6)
INSERT INTO spendregel VALUES(2,1,3)
I learnd how to make a view bud in this case im stuck in the select tile. Hope you can help me with this issue.
June 24, 2009 at 6:04 am
try this one...
select odate ,recpt.oreceiptnr,oamount from receipt recpt
inner join receiptregel recptrgl on recpt.oreceiptnr=recptrgl.oreceiptnr
union
select odate ,spd.ospendnr,oamount from spend spd
inner join spendregel spdl on spdl.ospendnr=spd.ospendnr
June 24, 2009 at 11:15 am
Hi V.Vivek,
Thanks for response.
However im little bit confused. You said :
select odate ,recpt.oreceiptnr,oamount from receipt recpt
What do you mean by 'from receipt recpt' ? and 'receipt' table does'nt have a oamount column. How can that?
June 24, 2009 at 12:04 pm
SELECT CONVERT(VARCHAR(10),odate,105) AS date,'receipt' AS type,CAST(oamount AS VARCHAR(30)) AS [amount of receipt],'' AS [amount of spend]
FROM receipt INNER JOIN receiptregel ON receipt.oreceiptnr=receiptregel.[oreceiptnr]
UNION ALL
SELECT CONVERT(VARCHAR(10),odate,105) AS date,'spend' AS type,'' AS [amount of receipt],CAST(oamount AS VARCHAR(30)) AS [amount of spend]
FROM spend INNER JOIN spendregel ON spend.[ospendnr]=spendregel.[ospendnr]
ORDER BY date
/* result
datetypeamount of receiptamount of spend
01-01-2009receipt10
01-02-2009spend6
01-03-2009spend3
02-04-2009receipt10
*/
Note: Normally, the conversion of empty integer columns into blanks would be done at the frontend side (same goes for date format). It just adds overhead to the server...
June 24, 2009 at 12:06 pm
What do you mean by 'from receipt recpt' ? and 'receipt' table does'nt have a oamount column. How can that?
recpt is an alias name for the table receipt. Aliases are used when we join two or more tables to get data from them.
I think the results are correct unless you want the Type columns in terms of receipt or spend?
:w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply