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.
Author |
Topic |
Junior Sqler
Starting Member
18 Posts |
Posted - 2013-03-07 : 07:48:18
|
Hello !I am trying to create a sampling procedure for invoice checking for a specific period of time.Data: 1.group of different suppliers 2.Invoices for each of above suppliers Target:For each supplier check every 2 invoices.If find an invoice with discrepancy i check every invoice after it till I find 2 invoices in row ok.Then restart sampling.This invoice ok? Previous Invoice ok? statusno NULL Initialno no SkipYes no samplingno Yes 1st Checkno no 2nd Checkno no skipno no samplingHow could I adjust my query if i want to sample every n invoices instead of every 2? (the rest will remain the same)I understand that a counter for 'skip' status is needed but i'm not sure how i could do that.My query (it works) is the following:dROP TABLE #TEMPCREATE TABLE #TEMP( [ranking] int, newrowNumb int, [STATUS] VARCHAR(20) )declare @SuppliersCount as intset @SuppliersCount = (select COUNT(distinct [bUSINESSPARTNERCODE]) from #q2)declare @countsupplier intSET @countsupplier=1 while @countsupplier<=@SuppliersCountBEGIN declare @RowsCount INT set @RowsCount = (select MAX(newrownumb) from #q2 where BusinessPartnerCode = (select distinct businesspartnercode from #q2 where ranking = @countsupplier)) declare @countrow INT SET @countrow=1 WHILE @countrow <= @RowsCount BEGIN insert into #temp(ranking, newrownumb, [status]) select A.ranking,A.newrowNumb, case when a.newrowNumb = 1 THEN 'Sampling' when a.newrowNumb=2 and a.PreviousDiscrepancies='yes' THEN '1st Check' when a.newrowNumb=2 and a.PreviousDiscrepancies='no' THEN 'Skip' when b.[status]= '1st Check' THEN '2nd Check' when b.[status]='2nd Check' and a.PreviousDiscrepancies='yes' THEN '1st Check' when b.[status]='2nd Check' and a.PreviousDiscrepancies='no' THEN 'skip' when b.[status]='skip' THEN 'sampling' when b.[status]='sampling' and a.PreviousDiscrepancies='no' THEN 'skip' when b.[status]='sampling' and a.PreviousDiscrepancies='yes ' THEN '1st Check' else 'unknown' end as status from #q2 aLEFT join #temp b on b.newrowNumb = a.newrowNumb - 1 and a.ranking=b.ranking where a.ranking = @countsupplier and a.newrowNumb = @countrow set @countrow=@countrow +1 ENDSET @countsupplier= @countsupplier +1endDrop table #FinalMethod2 select q.*,t.[status] into #FinalMethod2 from #q2 q left join #temp t on t.ranking = q.ranking and t.newrowNumb = q.newrowNumb Thank you in advance for your help!! |
|
Junior Sqler
Starting Member
18 Posts |
Posted - 2013-03-08 : 03:48:54
|
I guess that my question was complicated/not clear... :(That is why I will be JUNIOR Sqler forever... |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
|
|