Solved

weekly sales summary

Posted on 2007-12-01
5
980 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
[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
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

707 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