July 11, 2013 at 4:12 am
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/
July 11, 2013 at 4:32 am
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
July 11, 2013 at 4:36 am
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/
July 11, 2013 at 4:42 am
Thanks Hanshi, I also solved in similar manner 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2013 at 6:43 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 12, 2013 at 1:35 am
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')
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
July 12, 2013 at 3:49 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 12, 2013 at 6:24 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply