Need Help on writing this query

Question

Post reply

Need Help on writing this query

Mond

Old Hand

Points: 352
More actions
September 9, 2021 at 11:40 pm
Go to Answer
#3928403

Hello i need to find a package wich doesnt have a tracking number to process them in another store procedure. I am flaging this packages when it has a tracking number will be flag it as 1 and when not flag it as 2
Here is the code. I created 2 temp tables:
declare @PackageTracking table (id int, serialnumber varchar(max),trackingid varchar(max))
insert into @PackageTracking
select p.id,p.serialnumber,pd.Content from package p
join PackageDetail pd on pd.PackageId=p.id and pd.PackageAttributeId=23
where loadplanid=@LoadPlanId
declare @PackageTotal table (id int, serialnumber varchar(max))
insert into @PackageTotal
select p.id,p.serialnumber from package p
where loadplanid=@LoadPlanId
Results for these 2 tables are:
I need to find from table called @PackageTotal which serials are in table @PackageTracking means they already have a tracking number and if that is the case I need to flag them as 1 or 0 if they dont have tracking numbers.
Could you please help to write this query

really appreciate it. Thanks.

Ant-Green

SSC Guru

Points: 113445

September 10, 2021 at 6:53 am

Answer

#3928455

Please take a read through this link, it defines the usual etiquette on how to post code, as many people wont download a file from an unknown user or take the time to type out an image as a data set, it's just to time consuming and a security risk, so it helps us to help you.

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

But I had a spare 5 minutes so I whipped this up

DECLARE @PackageTracking TABLE (id INT, serialnumber varchar(max), trackingid varchar(max))
INSERT INTO @PackageTracking VALUES (1491,'ABCD','VC782XXXX78')

DECLARE @PackageTotal TABLE (id INT, serialnumber varchar(max))
INSERT INTO @PackageTotal VALUES 
(1491,'ABCD'),
(1492,'ABDX'),
(1493,'ABDY'),
(1494,'ABDZ'),
(1495,'ABDR')

SELECT
tot.id,
tot.serialnumber,
tra.trackingid,
case when tra.trackingid is null then 2 else 1 end as flag
FROM
@PackageTotal tot
LEFT OUTER JOIN
@PackageTracking tra
ON
tot.id = tra.id
AND tot.serialnumber = tra.serialnumber

October 4, 2021 at 2:56 pm

This was removed by the editor as SPAM
October 6, 2021 at 6:00 am

This was removed by the editor as SPAM
October 18, 2021 at 7:25 pm

This was removed by the editor as SPAM
October 18, 2021 at 7:29 pm

This was removed by the editor as SPAM
October 19, 2021 at 6:10 pm

This was removed by the editor as SPAM
October 19, 2021 at 6:11 pm

This was removed by the editor as SPAM
October 19, 2021 at 6:11 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63435 More actions · Answer 1

Post the CREATE TABLE scripts and someone can help you. =)

Mond Old Hand Points: 352 More actions · Answer 2

thanks so much for this help. is working now..save me !!!

thanks a lot!