Day Of Week 3

[monkeybarfan]

I have a Crystal Report that counts up all weekend calls, by month. I have created a Crystal formula to perform this - I am trying to recreate this in a stored procedure to use elsewhere, but the results are quite different.

SELECT
Datepart(yy,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) as 'Year',
Datepart(mm,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) as 'Month',
COUNT(CLID) as 'CLID'

FROM STCLLREC

WHERE Datename(dw,STARTDATE)='Sunday'
OR Datename(dw,STARTDATE)='Saturday'

This is not working. I have also tried using Datepart(dw, STARTDATE)=1 and it makes no difference. I am sure the Crystal Report is right, because I have drilled down into the actual details.

Can anyone spot anything? The data all comes off one table, so there are no joins or anything.

 

[gmmastros]

Convert(Convert(ahhh)) ???

This is not necessary.

Try this query. If it returns the correct data and you want me to explain it, I will.

SELECT Year([STARTDATE]) as [Year],
       Month([STARTDATE]) as [Month],
       COUNT(CLID) as [CLID]
FROM   STCLLREC
Group By Year(StartDate), Month(StartDate)
WHERE (Datename(dw,STARTDATE)='Sunday'
      OR Datename(dw,STARTDATE)='Saturday')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

you've forgotten the GROUP BY clause

SELECT CONVERT(char(7),STARTDATE,120) as 'YYYY-MM'
     , COUNT(CLID) as 'CLID'
  FROM STCLLREC
 WHERE DATEPART(dw,STARTDATE) IN (1,7)
GROUP
    BY CONVERT(char(7),STARTDATE,120)

r937.com | rudy.ca

 

[monkeybarfan]

Sorry, they didn't fix it but I was being a bit too vague. I also didn't put my full query in here, this is it... (I want to count bank holidays as weekend days, hence the long array of dates)

SELECT
Datepart(yy,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) as 'Year',
Datepart(mm,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) as 'Month',
Datepart(dd,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) as 'Day',
COUNT(CLID) as 'CLID'

FROM STCLLREC

WHERE FROMGROUP = 10 AND DNIS in ('1720', '1734', '1735', '1739')
AND STARTDATE in ('01/01/2004','09/04/2004','12/04/2004','03/05/2004','05/31/2004',
'08/30/2004','12/25/2004','12/26/2004','12/27/2004','12/28/2004','01/03/2005',
'03/25/2005','03/28/2005','05/02/2005','05/30/2005','08/29/2005','12/25/2005',
'12/26/2005','12/27/2005','01/02/2006','04/14/2006','04/17/2006','05/01/2006',
'05/29/2006','08/28/2006','12/25/2006','12/26/2006','01/01/2007','04/06/2007',
'04/09/2007','05/07/2007','05/28/2007','08/27/2007','12/25/2007','12/26/2007',
'01/01/2008','03/21/2008','03/24/2008','05/05/2008','05/26/2008','08/25/2008',
'12/25/2008','12/12/2008','01/01/2009','04/10/2009','04/13/2009','05/04/2009',
'05/25/2009','08/31/2009','12/25/2009','12/26/2009','12/28/2009','01/01/2010',
'04/02/2010','04/05/2010','05/03/2010','05/31/2010','08/30/2010','12/25/2010',
'12/26/2010','12/27/2010','12/28/2010','01/03/2011','04/22/2011','04/25/2011',
'05/02/2011','05/30/2011','08/29/2011','12/25/2011','12/26/2011','12/27/2011',
'01/02/2012','04/06/2012','04/09/2012','05/07/2012','05/28/2012','08/27/2012',
'12/25/2012','12/26/2012')
OR Datename(weekday,STARTDATE)='Sunday'
OR Datename(weekday,STARTDATE)='Saturday'

GROUP BY Datepart(yy,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)),
Datepart(mm,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)),
Datepart(dd,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103))
ORDER BY Datepart(yy,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)) ASC,
Datepart(mm,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103)),
Datepart(dd,CONVERT(DateTime, CONVERT(varchar(10), [STARTDATE], 103), 103))

(I have also put daily groupings in there just to pinpoint the error - these will be removed)

This is generating about 3 times as many calls as the Crystal report (count of CLID)

If I remove the section
OR Datename(weekday,STARTDATE)='Sunday' OR Datename(weekday,STARTDATE)='Saturday'
in both the SQL Server query and the Crystal Reports formula then the returned datasets match exactly, it is as soon as I add the two OR criterion that the data differs.

 

[SQLDenis]

Are you for real??? Ever heard of a Calendar table for things like that?


create the calendar, join on that, done

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

easy to maintain, you create it once and no code changes needed period.


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[monkeybarfan]

Yes I am aware of using tables, but that is not the issue here, that can come later (when time permits) and it is not the real issue here as it is not affecting the results.

I constructed this query by copying a more complex Crystal formula and adapting it slightly.

 

[SQLDenis]

the problem with your query is that you need an additional set of parentheses right now



your calendar date would have a IsWeekend column and a IsHoliday column

join on that where one of those is true and you are done



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[r937]

you've almost cracked it on your own (when you said it works okay without the ORs)

the problem is how you combine your conditions

you have this --

WHERE a AND b AND c OR d OR e

which is evaluated like this --

WHERE ( a AND b AND c ) OR ( d ) OR ( e )

now, relate that back to your WHERE clause and you will see why it's wrong

suggestion: code the parentheses to ensure your conditions are combined the way you want

i think you want this --

WHERE a AND b AND ( c OR d OR e )





r937.com | rudy.ca

 

[gmmastros]

It's not set in stone, but.... I have a rule.

When you use OR in a where clause, you should use parenthesis.

WHERE FROMGROUP = 10 AND DNIS in ('1720', '1734', '1735', '1739')
    AND STARTDATE in ('01/01/2004','09/04/2004','12/04/2004','03/05/2004','05/31/2004',
    '08/30/2004','12/25/2004','12/26/2004','12/27/2004','12/28/2004','01/03/2005',
    '03/25/2005','03/28/2005','05/02/2005','05/30/2005','08/29/2005','12/25/2005',
    '12/26/2005','12/27/2005','01/02/2006','04/14/2006','04/17/2006','05/01/2006',
    '05/29/2006','08/28/2006','12/25/2006','12/26/2006','01/01/2007','04/06/2007',
    '04/09/2007','05/07/2007','05/28/2007','08/27/2007','12/25/2007','12/26/2007',
    '01/01/2008','03/21/2008','03/24/2008','05/05/2008','05/26/2008','08/25/2008',
    '12/25/2008','12/12/2008','01/01/2009','04/10/2009','04/13/2009','05/04/2009',
    '05/25/2009','08/31/2009','12/25/2009','12/26/2009','12/28/2009','01/01/2010',
    '04/02/2010','04/05/2010','05/03/2010','05/31/2010','08/30/2010','12/25/2010',
    '12/26/2010','12/27/2010','12/28/2010','01/03/2011','04/22/2011','04/25/2011',
    '05/02/2011','05/30/2011','08/29/2011','12/25/2011','12/26/2011','12/27/2011',
    '01/02/2012','04/06/2012','04/09/2012','05/07/2012','05/28/2012','08/27/2012',
    '12/25/2012','12/26/2012')
    [!]And ([/!]Datename(weekday,STARTDATE)='Sunday'
    OR Datename(weekday,STARTDATE)='Saturday'[!])[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[monkeybarfan]

Thanks lads

Got it working now, it was all about the brackets!

Been working with Crystal formulas for too long, need to brush up on my T-SQL syntax!!

 

[Ruenells]

I am having a simular issue, what I am trying to recreate is a report that runs from Tuesday to Thursday and Tuesday to Tuesday. It was working in Crystal but I am unable to recreate it in Ms Access.

I do not want to have to enter the dates at all I want to be able to run the report and have it pull the correct information always Tuesday to Thursday and Tuesday to Tuesday.

Any help would be great!!

 

[jbenson001]

... but I am unable to recreate it in Ms Access.

Try the Access forums.

 

[Ruenells]

I'm sorry I am new to this forum. Did i miss something here?

 

[jbenson001]

This forum is for Microsoft SQL Server, not Access. You'd be best off asking there.

 

[SQLBill]

Ruenells,

Microsoft SQL Server (this forum) and Access use two different versions of SQL. That's why there is a forum for MS Access and why JBenson001 suggested you try there.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875