Solved

weekly sales summary

Posted on 2007-12-01
5
964 Views
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))
from ORDERHEADER
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
Comment
Question by:jimwarrenus
  • 3
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20387645
have you checked the @@datefirst variable?
0
 
LVL 25

Expert Comment

by:imitchie
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

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...
0
 

Author Comment

by:jimwarrenus
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(*)
from ORDERHEADER
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

by:
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())
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

821 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