report of inventory issue.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

report of inventory issue..

Author

Topic

choong1990
Starting Member

1 Post

Posted - 2012-01-14 : 22:05:05

[code]+------------+ +-----------------+
| record | | record_items |
+------------+ +-----------------+
|RecordID(PK)|>--->| RecordID (FK) |
|PatientID | | ItemID |
|Precription | | Amount |
|VisitDate | +-----------------+
+------------+
[/code]
this 2 is the table i created, my problem is there is many items to store in a "ReportID",so is that all store in a row or not?
[code]
---------------------------------------------
|ReportID | ItemID | Amount |
----------|---------|----------|-------------
| 1 | 1 | 10 |
| 1 | 2 | 50 |
| 1 | 5 | 100 |
-------------------------------------[/code]
or the table look like this? each item store in a row,but still same id?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 02:13:20

what you've shown is correct. it will be stored one item per row and ReportID will repeat. Thats not an issue as you can display them in seperate columns in a single row if you want by doing cross tabing
ie something like


SELECT RecordID,
MAX(CASE WHEN ItemID = 1 THEN Amount END) AS Amount1,
MAX(CASE WHEN ItemID = 2 THEN Amount END) AS Amount2,
MAX(CASE WHEN ItemID = 5 THEN Amount END) AS Amount5
FROM record_items
GROUP BY RecordID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources