Solved

weekly sales summary

Posted on 2007-12-01
5
947 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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now