Simple Case, (i think) - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Case, (i think)

Author

Topic

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-11-30 : 15:23:36

i have two views that im trying to merge toghether as one..both are demand for parts one is a customer order demand view, one is a workorder demand view. the fields are identical in each 3 fields part id, date (date parted as year date ie 332) and qty

i would like to merge these so that if the date fields are the same than i would like to add the qty's toghether, that much ive figured out with

CASE
WHEN CO.DESIREDDAY = WO.DESIREDDAY
THEN (CO.ORDER_QTY + WO.WO_QTY)
ELSE
END AS MERGEDDEMAND,

what im not sure on how to do is to merge the data if the date fields dont equal. i have demand in each and i need to get it down to one view with the part id , qty (could be from either table) and the date (also could be from either table)

any help i could get would be extremely appreciated

thanks in advance

Mike

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-11-30 : 15:27:57

(date parted as year DAY ie 332) and qty

basically i want three fields in this new view, part_id , demand (from either previous view or a combination of the two if the dates are the same) and date (also from either previous view)

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-11-30 : 15:47:34

Here's what i have so far, unfortunatly when the dates dont equal its not returning the value its just coming up Null

CASE
WHEN CO.DESIREDDAY = WO.DESIREDDAY
THEN (CO.ORDER_QTY + WO.WO_QTY)
WHEN CO.DESIREDDAY <> WO.DESIREDDAY AND CO.ORDER_QTY IS NOT NULL
THEN CO.ORDER_QTY
WHEN CO.DESIREDDAY <> WO.DESIREDDAY AND WO.WO_QTY IS NOT NULL
THEN WO.WO_QTY
ELSE NULL
END AS MERGEDDEMAND,

CASE
WHEN CO.DESIREDDAY = WO.DESIREDDAY
THEN CO.DESIREDDAY
WHEN CO.DESIREDDAY <> WO.DESIREDDAY
AND CO.DESIREDDAY IS NOT NULL
THEN CO.DESIREDDAY
WHEN CO.DESIREDDAY <> WO.DESIREDDAY
AND WO.DESIREDDAY IS NOT NULL
THEN WO.DESIREDDAY
ELSE NULL
END AS DEMANDDAY

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 16:37:40

table structure
sample data
wanted output

No, you're never too old to Yak'n'Roll if you're too young to die.

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-12-01 : 09:09:46

sample data Customer Order Table

Part id QTY YearDay

0106500 5 334
01015PB 3 335
03100PB 4 336

sample data Work order Table

Part id QTY YearDay

0106500 3 334
01015PB 4 339
03100PB 5 340

what i would like to see is

Part id QTY YearDay

0106500 8 334
01015PB 3 335
03100PB 4 336
01015PB 4 339
03100PB 5 340

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 09:19:25

Try this

select [Part id], sum(QTY) as QTY, YearDay from
(
Select [Part id], QTY, YearDay from customer_order
union all
Select [Part id], QTY, YearDay from work_order
) as t
group by [Part id], YearDay

Madhivanan

Failing to plan is Planning to fail

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-12-01 : 11:43:41

GETTING A SYNTAX ERROR BY THE WORD FROM, NOT SURE WHAT I DID WRONG, THANKS FOR YOUR HELP I SEE WHERE YOUR GOING WITH THIS

CREATE VIEW DBO.GPSR_PARTDEMANDDAY AS SELECT

[PART_ID],
SUM(QTY) AS QTY,
DESIREDDAY
FROM
(SELECT [PART_ID],
ORDER_QTY AS QTY,
DESIREDDAY
FROM GPSR_CUSTDEMANDDAY

UNION ALL

SELECT
[PART_ID],
WO_QTY AS QTY,
DESIREDDAY
FROM
GPSR_WODEMANDDAY2)

AS T

FROM GPSR_CUSTDEMANDDAY AS CO WITH(NOLOCK)
LEFT OUTER JOIN GPSR_WODEMANDDAY2 AS WO WITH(NOLOCK)
ON CO.PART_ID = WO.PART_ID

GROUP BY PART_ID , DESIREDDAY

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-01 : 12:38:48

After the 'AS T' part it appears you are joining some more tables...if I am reading this correctly you have 2 from statements. The derived table and then another joining CO to WO.

The above suggestion from Madhiv was to create one derived table and then pull from it.

Edit for readability:

Suggested-
SELECT
t.fields
FROM
(
Derived Table
) t
Group by ...

Yours -
SELECT
t.fields
FROM
(
Derived table
) t

FROM <---the mistake
CO.table
WO.Table
JOIN....
WHERE....

KINGOFMPLS
Starting Member

6 Posts

Posted - 2009-12-02 : 10:18:20

thanks so much everyone, that worked perfectly , just what i wanted, much apprecitated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources