Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# weekly sales summary

Posted on 2007-12-01
Medium Priority
995 Views
in ms sql i'm trying this:

set datefirst 1
select top 100 percent datepart(ww,[ENTRYDATE]) as weeknum,
max([ENTRYDATE]) as [Week Commencing],
sum(ISNULL(cast((PRODUCTDOLLARS/100) as decimal(25,2)),0))
, count(ISNULL([FULLORDERNO],0))
where
FULLORDERNO like 'W%' and STATUS <> 'PD' and ENTRYOPER = 'WEBMACS' and
Datepart(ww,[ENTRYDATE]) <> Datepart(ww,getdate())
group by DatePart(yy,[ENTRYDATE]),Datepart(ww,[ENTRYDATE])
order by [Week Commencing]

the code runs but the totals and counts are not correct....  can you see something in there that could account for incorrect totals?
0
Question by:jimwarrenus
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 25

Expert Comment

ID: 20387645
have you checked the @@datefirst variable?
0

LVL 25

Expert Comment

ID: 20387660
apologies, I see that you have set datefirst.  I have a few questions though, why "TOP 100 percent" since it's everything? also, count(ISNULL([FULLORDERNO],0)) is equal to count(*) since COUNT adds 1 for each NON-NULLvalue.  furthermore, it cannot be null, because FULLORDERNO like 'W%'  ensures it.
last question is, shouldn't this be
min([ENTRYDATE]) as [Week Commencing] instead of MAX so that it's the Monday that's showing?
given Set DateFirst 1 = Monday

the query looks right otherwise, so no clue
0

LVL 143

Expert Comment

ID: 20387677
can you explain how you "see" that the counts and totals are not correct?
I have to agree with imitchie that the query looks correct, at least in syntax...
0

Author Comment

ID: 20388957
ok, i've modified the query to set the datefirst to sunday.....
set datefirst 7
select  datepart(ww,[ENTRYDATE]) as weeknum,
min([ENTRYDATE]) as [Week Commencing],
sum(ISNULL(cast((PRODUCTDOLLARS/100) as decimal(25,2)),0))
, count(*)
where
FULLORDERNO like 'W%' and STATUS <> 'PD' and ENTRYOPER = 'WEBMACS' and
Datepart(ww,[ENTRYDATE]) <> Datepart(ww,getdate())
group by DatePart(yy,[ENTRYDATE]),Datepart(ww,[ENTRYDATE])
order by [Week Commencing]

the last couple of lines of results are:
weeknum      Week Commencing      Column 3      Column 4
46      20071111                           132915.9      1188
47      20071118                            91339.6      800

that last line should be covering 11-18 thru 11-24, right?  and is it not building a line for 11-25 thru today's date because the week isnt complete yet?
0

LVL 25

Accepted Solution

imitchie earned 2000 total points
ID: 20390765
Datepart(ww,[ENTRYDATE]) <> Datepart(ww,getdate())
ensures that unless it's the prior week or earlier, it won't be part of the result. so yes, because the week isn't complete yet.
if you do want to include a running total of the current week, try
Datepart(ww,[ENTRYDATE]) <= Datepart(ww,getdate())
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month10 days, 15 hours left to enroll