weekly sales summary

Posted on 2007-12-01
Last Modified: 2012-05-05
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))
     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?
Question by:jimwarrenus
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
  • Learn & ask questions
  • 3
LVL 25

Expert Comment

ID: 20387645
have you checked the @@datefirst variable?
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
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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...

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(*)
     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?  
LVL 25

Accepted Solution

imitchie earned 500 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())

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question