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 2008 Forums
 Transact-SQL (2008)
 Evaluate or Count if new model was added

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-13 : 02:46:23
Hi,

I have a requirements to evaluate or check if new model was added to an existing model per RequiredPart. My sample DDL was the result of my query created getting the model and requiredpart. My main query getting the requiredpart and finding or obtaining the model used in the requiredpart.

Hoping my requirements is clear to understand. thank you in advance.

Below sample data and DDL

Create table #Sample
(Model nvarchar(35), RequiredPart nvarchar(35))
Insert into #Sample(Model,RequiredPart )
values ('2XRED','TTC1045'),('2XBLU','TTC1045'),
('2XBLK','TTC1045'),('2XRED','TTC1045'),
('8XBLU','BTC1045'),('8XRED','BTC1045'),
('8XRED','COR30044118XRED'),('2XRED','TTC1046'),('2XBLU','TTC1046'),
('8XRED','TTC1050'),('8XBLU','TTC1050')

Select
t.RequiredPart,
Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart for xml path ('')), 1 , 1 ,'') as Model
From #Sample t
Group by t.REQUIREDPART
Order by t.REQUIREDPART


Desired Result: need to add column Count

RequiredPart-----Model-------------COUNT
BTC1045----------8XBLU,8XRED--------2
COR30044118XRED--8XRED--------------1
TTC1045----------2XBLK,2XBLU,2XRED--3
TTC1046----------2XBLU,2XRED--------2
TTC1050----------8XBLU,8XRED--------2

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-13 : 03:11:12
[code]
Select
t.RequiredPart,
Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart for xml path ('')), 1 , 1 ,'') as Model
,Count(DISTINCT Model) AS noCount
From #Sample t
Group by t.REQUIREDPART
Order by t.REQUIREDPART
[/code]


[code]
RequiredPart Model noCount
BTC1045 8XBLU,8XRED 2
COR30044118XRED 8XRED 1
TTC1045 2XBLK,2XBLU,2XRED 3
TTC1046 2XBLU,2XRED 2
TTC1050 8XBLU,8XRED 2
[/code]


sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-15 : 21:23:29
Thank you very much Stepson.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 00:48:40
You're welcome


sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-16 : 01:12:51
I forgot to ask this question. Its possible or what should i do to track if new model was added in the list.
let say when i run this query today there's a new model added under requiredpart "BTC1045", the model name is 8XGRN. With a lot of records how could i say or find if has a new model. Thanks.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 01:59:47
Base only on existent fields is little hard.

If you run this query once a day , you can have a job that save the result into a Result_Archive table (truncate/delete every day before repopulate it) and then compare with the actual result.

It is possible to add some new column ? like when this part was added datetime or an ID ?



SELECT TODAY.Model, RA.Model
FROM #Result_Archive AS RA
FULL JOIN
(
Select
t.RequiredPart,
Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart for xml path ('')), 1 , 1 ,'') as Model
,Count(DISTINCT Model) AS noCount
From #Sample t
Group by t.REQUIREDPART
) AS TODAY
ON RA.RequiredPart = TODAY.RequiredPart
WHERE RA.noCount <> TODAY.noCount




sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 02:12:22
an edit to this:

WHERE RA.noCount <> TODAY.noCount OR RA.noCount IS NULL



sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-27 : 04:01:05
Do i need to create a temporary table to save the result of the report called #Result_Archive.
then every time i run this codes after getting the result i need to delete or removed the existing records.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-02 : 00:44:22
#Result_Archive could be a normal table. The idea is that you have something(table) were you store yesterday result to compare with today result.
And yes, that "working" table will hold only yesterday result


sabinWeb MCP
Go to Top of Page
   

- Advertisement -