SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check series is valid or not


Check series is valid or not

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5114 Visits: 2767
Hi, I have a scenario in which I have to check that whether I am correct no of series or not...

Create table tbl
(
ID int identity,
number varchar(10),
numstatus varchar(10)
)

INSERT INTO tbl
Values
('V001','Active'),
('V002','Active'),
('V003','Active'),
('V004','InActive'),
('V005','Active')



I tried in this manner....

Declare @firstno varchar(10) = 'V0001',
@scndno varchar(10) = 'V0005'

Create table #voucherno
(
voucherno varchar(10)
)
INSERT INTO #voucherno
SELECT VoucherNo from tbl WHERE number between @firstno and @scndno

SELECT gv.number
FROM tbl As gv
INNER JOIN #voucherno ro ON ro.VoucherNo = gv.number
WHERE gv.numstatus = 'Active'



AS values that i passed in parameters @frstno and @scndno is not in series as v004 is inactive so I should get message invalid series....
My query gives me output as V001,v002,V003,v005. I tried with IF EXISTS but didnt get desired output

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8206 Visits: 3718
What about a simple CASE statement, will dat do the trick?
Declare @firstno varchar(10) = 'V001',
@scndno varchar(10) = 'V005'
select count(*) from #tbl WHERE number between @firstno and @scndno
select count(*) from #tbl WHERE number between @firstno and @scndno and numstatus = 'Active'

select
case when
(select count(*) from #tbl WHERE number between @firstno and @scndno)
=
(select count(*) from #tbl WHERE number between @firstno and @scndno and numstatus = 'Active')
then 'Active serie'
else 'One or more values are not Active'
end as result



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5114 Visits: 2767
I tried and got the solution in the below manner but I am looking if there is any other approach for this:


Declare @count int , @count1 int
SELECT @count = (SELECT count(gv.number) frst
FROM tbl As gv
INNER JOIN #voucherno ro ON ro.VoucherNo = gv.number

WHERE gvs.numStatus = 'Active at HO'

)

SELECT @count1 = (select COUNT(v.voucherno) scnd from #voucherno v)

IF @count = @count1

print 'correct'
ELSE
Print 'Invalid series'



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5114 Visits: 2767
Thanks Hanshi, I also solved in similar manner Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
dwain.c
dwain.c
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16721 Visits: 6431
How about like this?


Create table #tbl
(
ID int identity,
number varchar(10),
numstatus varchar(10)
);

INSERT INTO #tbl
Values
('V001','Active'),
('V002','Active'),
('V003','Active'),
('V004','InActive'),
('V005','Active'),
('V006','Active'),
('V007','Active'),
('V008','Active'),
('V009','Active'),
('V010','Active');

WITH SeriesRuns AS (
SELECT Start='V001', [End]='V005' UNION ALL SELECT 'V006','V010')
SELECT Start, [End], IDStart=MIN(ID), IDEnd=MAX(ID), [Status]=MAX(numstatus)
FROM SeriesRuns
CROSS APPLY (
SELECT ID, number, numstatus
FROM #tbl
WHERE number BETWEEN Start AND [End]) a
GROUP BY Start, [End]

GO
DROP TABLE #tbl





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39118 Visits: 19990
DROP table tbl
CREATE TABLE tbl
(ID int identity, Voucherno varchar(10), numstatus varchar(10)
)

INSERT INTO tbl VALUES
('V0001','Active'),
('V0002','Active'),
('V0003','Active'),
('V0004','InActive'),
('V0005','Active')

DECLARE @firstno varchar(10) = 'V0001',
@scndno varchar(10) = 'V0005'

SELECT Voucherno
FROM tbl
WHERE Voucherno BETWEEN @firstno AND @scndno
AND NOT EXISTS (
SELECT 1
FROM tbl
WHERE Voucherno BETWEEN @firstno AND @scndno
AND numstatus = 'InActive')




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18528 Visits: 7397


SELECT CASE WHEN Total_Rows = Active_Rows THEN 'Valid' ELSE 'Invalid' END AS Status
FROM (
SELECT
SUM(1) AS Total_Rows,
SUM(CASE WHEN number = 'Active' THEN 1 ELSE 0 END) AS Active_Rows
FROM tbl
WHERE
number BETWEEN @firstno AND @scndno
) AS derived




SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Steven Willis
Steven Willis
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 1721
Here's another approach:

Sample data:



IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
DROP TABLE #tbl

CREATE TABLE #tbl
(
ID INT IDENTITY
,numval VARCHAR(10)
,numstatus VARCHAR(10)
)

INSERT INTO #tbl
VALUES
('V001','Active'),
('V002','xActive'),
('V003','Active'),
('V004','Active'),
('V005','xActive')

--INSERT INTO #tbl
--VALUES
-- ('V001','Active'),
-- ('V002','Active'),
-- ('V003','Active'),
-- ('V004','Active'),
-- ('V005','Active')




Validate series status:



SELECT
nrows AS TotalRows,
maxRows AS MatchedRows,
(CASE
WHEN maxRows <> nrows THEN 'Invalid series'
ELSE 'Valid Series'
END)
AS SeriesStatus
FROM
(
SELECT TOP(1)
MAX(r.rowNum) OVER (PARTITION BY r.numval) AS maxRows
,@@RowCount AS nrows
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY numstatus ORDER BY numval) AS rowNum
,@@RowCount AS nrows
,numval
FROM
#tbl
) r
ORDER BY
r.rowNum DESC
) r1




 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search