problem with showing the results into 1 row

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

SQL Server 2005 Forums

Transact-SQL (2005)

problem with showing the results into 1 row

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2010-05-17 : 00:17:34

hello again guys,

i am trying to build a code with the following columns from only 1 table.

the table looks like this : (im gona show only the information needed)

tran.type | date | code | description | quantity | price | order | invoice | supplier

00 | 2010-04-01 | MFR0004 | BLACK 560 | 100 | 25 | 0000001 | 112233 | 1stSUPPLIER |
03 | 2010-04-02 | MFR0004 | BLACK 560 | 0 | 123 | 0000001 | 44556 | 2ndSUPPLIER |
03 | 2010-04-03 | MFR0004 | BLACK 560 | 0 | 77 | 0000001 | 554269 | 3rdSUPPLIER |
00 | 2010-04-10 | MFR0006 | BLACK 454RF | 25 | 10 | 0000002 | 54671 | 1stSUPPLIER |
03 | 2010-04-11 | MFR0006 | BLACK 454RF | 0 | 150 | 0000002 | 654871 | 4thSUPPLIER |
00 | 2010-04-15 | MFRT004 | BLUE 534 | 15 | 8 | 0000003 | 65421 | 5thSUPPLIER |

the table goes on with more records. what i would like to get is the following:

2010-04-01 | MFR0004 | BLACK 560 | 100 | 2500 | 200 | 0000001 | 112233 | 1stSUPPLIER
2010-04-10 | MFR0006 | BLACK 454RF | 25 | 250 | 150 | 0000002 | 54671 | 1stSUPPLIER

in other words : case when tran.type is 00 put some information, and some calculus and when tran.type is 03 put more information and sum the price, all this information on one row (this is what beats me) , while they have the same order and product. the table is big , but im spliting it in months , with parameters based on date column.

thanks for everyone who will give me an idea.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:17:20

[code]
SELECT t.date,t.code,t.description,t.quantity,t.quantity * t.price as 1stsuppprice,t1.TotalPrice AS othersuppprice,
t.order,t.invoice,t.supplier
FROM YourTable t
INNER JOIN (SELECT code , description,MIN(CASE WHEN tran.type='00' THEN date ELSE NULL END) AS MinDate,
SUM(CASE WHEN tran.type='03' THEN price ELSE NULL END) AS TotalPrice
FROM YourTable
GROUP BY code ,description
)t1
ON t1.code = t.code
AND t1.description = t.description
AND t1.MinDate = t.date
[/code]

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-18 : 05:43:21

quote:

Originally posted by visakh16


SELECT t.date,t.code,t.description,t.quantity,t.quantity * t.price as 1stsuppprice,t1.TotalPrice AS othersuppprice,
t.order,t.invoice,t.supplier 
FROM YourTable t
INNER JOIN (SELECT  code , description,MIN(CASE WHEN tran.type='00' THEN date ELSE NULL END) AS MinDate,
            SUM(CASE WHEN tran.type='03' THEN price  ELSE NULL END) AS TotalPrice
            FROM YourTable
            GROUP BY  code ,description
           )t1
ON t1.code = t.code 
AND t1.description = t.description
AND t1.MinDate = t.date

The red part should be:
[1stsuppprice]

______________________

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 14:08:11

[code]
SELECT t.date,t.code,t.description,t.quantity,t.quantity * t.price as [1stsuppprice],t1.TotalPrice AS othersuppprice,
t.[order],t.invoice,t.supplier
FROM YourTable t
INNER JOIN (SELECT code , description,MIN(CASE WHEN type='00' THEN [date] ELSE NULL END) AS MinDate,
SUM(CASE WHEN type='03' THEN price ELSE NULL END) AS TotalPrice
FROM YourTable
GROUP BY code ,description
)t1
ON t1.code = t.code
AND t1.description = t.description
AND t1.MinDate = t.[date]
[/code]

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

tempus
Starting Member

47 Posts

Posted - 2010-06-01 : 02:32:42

sorry for replying so late.

it seems that something is not working right. i tried to aply the code bellow to my database but i must be doing something wrong.

first, the description is from another table, my fault . the join criteria for the decription table and the table that i written earlier is the column [code] (where anothertable.code = writtenearliertable.code)

thanks in advance. (i really tried to do it myself but then the script is a bit complicated for me, and i cant seem to find where to fit it)

Best regards.

PS: i also created the following table for test:

00 | 2010-04-01 | MFR0004 | BLACK 560 | 100 | 25 | 0000001 | 112233 | 1stSUPPLIER |
03 | 2010-04-02 | MFR0004 | BLACK 560 | 0 | 123 | 0000001 | 44556 | 2ndSUPPLIER |
03 | 2010-04-03 | MFR0004 | BLACK 560 | 0 | 77 | 0000001 | 554269 | 3rdSUPPLIER |
00 | 2010-04-10 | MFR0006 | BLACK 454RF | 25 | 10 | 0000002 | 54671 | 1stSUPPLIER |
03 | 2010-04-11 | MFR0006 | BLACK 454RF | 0 | 150 | 0000002 | 654871 | 4thSUPPLIER |
00 | 2010-04-02 | MFRT009 | BLUE 09 | 60 | 7 | 0000004 | 65421 | 5thSUPPLIER |
00 | 2010-04-07 | MFRT008 | BLUE 08 | 50 | 3 | 0000005 | 65469 | 9thSUPPLIER |
00 | 2010-04-04 | MFRT007 | BLUE 07 | 80 | 5 | 0000006 | 65564 | 7thSUPPLIER |
00 | 2010-04-07 | MFRT0015| BLUE 015 | 150 | 1 | 0000007 | 65999 | 6thSUPPLIER |
03 | 2010-04-01 | MFRT0015| BLUE 015 | 0 | 100 | 0000007 | xx13 | 12thSUPPLIER |

it has a few more records than the other one.

the result after running the script:

2010-04-01 00:00:00.000 MFR0004 100.00 2500.000000 200.0000 1 112233 1stSUPPLIER
2010-04-10 00:00:00.000 MFR0006 25.00 250.000000 150.0000 2 54671 1stSUPPLIER
2010-04-07 00:00:00.000 MFRT0015 150.00 150.000000 100.0000 7 65999 6thSUPPLIER
2010-04-04 00:00:00.000 MFRT007 80.00 400.000000 NULL 6 65564 7thSUPPLIER
2010-04-07 00:00:00.000 MFRT008 50.00 150.000000 NULL 5 65469 9thSUPPLIER
2010-04-02 00:00:00.000 MFRT009 60.00 420.000000 NULL 4 65421 5thSUPPLIER

the results are great. (only need to solve that field from another table.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources