Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check series is valid or not Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 4:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:50 PM
Points: 1,942, Visits: 2,382
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/
Post #1472493
Posted Thursday, July 11, 2013 4:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1472500
Posted Thursday, July 11, 2013 4:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:50 PM
Points: 1,942, Visits: 2,382
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/
Post #1472501
Posted Thursday, July 11, 2013 4:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:50 PM
Points: 1,942, Visits: 2,382
Thanks Hanshi, I also solved in similar manner :)


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1472503
Posted Thursday, July 11, 2013 6:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1472833
Posted Friday, July 12, 2013 1:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,864, Visits: 14,165
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
Post #1472885
Posted Friday, July 12, 2013 3:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1473253
Posted Friday, July 12, 2013 6:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1473271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse