Trying to run TSQL queries with regular query in same SQMS querywindow

Question

Trying to run TSQL queries with regular query in same SQMS querywindow

Zososql

Ten Centuries

Points: 1218
More actions
January 4, 2017 at 5:16 pm

#325033

I'm using SQL 2012 inside SQL Studio Manager, i have a date calculating query that i want to run to capture the number of weekdays without weekends before i run a select statement that will use the results of the date parse, but I get no rows returned in the query
declare @startdate date,
@enddate date, @a integer
set @startdate=’2016-04-01'
set @enddate=’2016-04-30'
set @a=0
while @startdate<=@enddate
Begin
if Datename(dw,@startdate)in('Monday','Tuesday','Wednesday','Thursday','Friday')
begin
set @a=@a+1
end
set @startdate=DATEADD(d,1,@startdate)
end;
select @a
select
a.productno,
a.productdescript,
a.orderdate,
b.logno,
b.logtype
from myproducts a, mylogs b
join myothertable c
on
a.productno = c.productno
where @startdate > a.orderdate and @enddate < a.orderdate
order by productno
...if i do not use the While block in this, I do get the rows returned . I am trying to run this in studio management query window without building a stored procedure. Is there something with the tsql While clause i am missing?
thanks
Zo

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

This is an awful way to do this. Loop structures are incredibly slow in SQL.

Also, I would expect your dataset be different with the while block, you're changing your start date with each loop. If you checked your variables you would have noticed that you're running a query that asks for items between the dates 01 May 2016 and 30 Apr 2016. That will never bring back anything as your start day is after your end date.

Have you tried using a calendar table instead? Have a read of Bones of SQL - The Calendar Table[/url]. You can then do a simple SELECT and COUNT from there.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 2

A better solution for you:

DECLARE @StartDate DATE, @EndDate DATE;

SET @StartDate = '01-Apr-2016';

SET @EndDate = '30-Apr-2016';

DECLARE @Weekdays INT;

WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L4),

DateRange AS (SELECT DATEADD(DAY, T.n, 0) AS DayDate

FROM TALLY T

WHERE DATEADD(DAY, T.n, 0) BETWEEN @StartDate AND @EndDate)

SELECT @Weekdays = COUNT(*)

FROM DateRange DR

WHERE DATENAME(WEEKDAY, DR.DayDate) NOT IN ('Sunday', 'Saturday');

SELECT @Weekdays;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DesNorton SSC-Insane Points: 24386 More actions · Answer 3

You could also use a virtual tally table

declare @startdate DATE = '2016-04-01'

, @enddate DATE = '2016-04-30'

, @a INTEGER = 0;

WITH lv0(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)

,(0),(0),(0),(0),(0),(0),(0),(0)

,(0),(0),(0),(0),(0),(0),(0),(0) ) l(N) ) -- 64 rows

, lv1(n) AS (SELECT 0 FROM lv0 a CROSS JOIN lv0 b) -- 4,096 rows

, DateList (dt) AS (SELECT TOP (DATEDIFF(dd, @startdate, @enddate) +1)

dt = DATEADD(dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1, @startdate)

FROM lv1

)

SELECT @a = COUNT(*)

FROM DateList

WHERE DATENAME(DW, dt) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')

SELECT @a;

How to post data/code on a forum to get the best help.