how to display daily sales between the date? [urgent]

campbells

I have create a SQL which calculate the daily sale but it will only show 1 day when i run it select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL from (select (select sum(totalsales+returnamt)as TotalSoft from tot_item where bizdate='2006-10-1'and (deptcode= 11 or deptcode=12 or deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17 ))as softline, (select sum (totalsales+returnamt)as TotalHard from tot_item where bizdate='2006-10-1'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode= 24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline, (select sum(totalsales+returnamt)as TotalSupermarket from tot_item where bizdate='2006-10-1' and (deptcode= 30 or deptcode=40 or deptcode=50 or deptcode= 60 or deptcode= 70 or deptcode=80 or deptcode= 90)) as supermarket )as t1 but i want to show like the daily result between day 1- day 15 so i create this SQL but the result not wat i expected, the RESULT should the TOTAL sum of this 15 days.... i want to show 1 by 1 for the daily sale... how? select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL from (select (select sum(totalsales+returnamt)as sototal from tot_item where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 11 or deptcode=12 or deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17 ))as softline, (select sum(totalsales+returnamt)as htotal from tot_item where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode= 24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline, (select sum(totalsales+returnamt)as sutotal from tot_item where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 30 or deptcode=40 or deptcode=50 or deptcode=60 or deptcode= 70 or deptcode=80 or deptcode= 90 )) as supermarket )as t1

Edbert P

Hmm...it's a bit hard to understand your query as it's too complicated. You might want to simplify it a bit. First, use IN instead of deptcode = 14 or deptcode = 15 and so on. So let's format it first into:

SELECT t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline) AS TOTAL
FROM
(SELECT (SELECT SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE bizdate='2006-10-1' AND deptcode IN (11, 12, 13, 14, 15, 16, 17)) AS softline,

    (SELECT SUM(totalsales+returnamt) AS TotalHard 
    FROM tot\_item
    WHEREbizdate='2006-10-1' AND deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)) AS hardline,

    (SELECT sum(totalsales+returnamt) AS TotalSupermarket 
    FROM tot\_item
    WHERE bizdate='2006-10-1' AND deptcode IN (30, 40, 50, 60, 70, 80, 90)) AS supermarket

) AS t1

Now that it's more simplified, I found that you can actually separate softline, hardline, and supermarket into 3 separate queries to save the confusion, and we can use GROUP BY to show the sum for each bizdate. Query1:

    SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft 
    FROM tot\_item
    WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
    GROUP BY bizdate

Query2:

    SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard 
    FROM tot\_item
    WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline
    GROUP BY bizdate

Query3:

    SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket 
    FROM tot\_item
    WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) AS supermarket
    GROUP BY bizdate

Now in your final query you can link all the total using the following:

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM Query1
LEFT JOIN Query2 ON Query1.bizdate = Query2.bizdate
LEFT JOIN Query3 ON Query1.bizdate = Query3.bizdate

This will return your daily result of the sale. From there you can specify the 15 days total sum. If you want to put them all as one big query, here's the query:

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline

campbells

i got this error: Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'AS'. actaully where do i put the date that i want them to show on?? like where bizdate<='2006-10-15' and bizdate>='2006-10-01which it will show between '2006-10-01' and'2006-10-15'

Edbert P

Which query did you run? Does each query run? You can put the WHERE condition at the end of the query, before the GROUP BY if there is any.

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin Edbert Sydney, Australia

Edbert P

Oh. Scratch that. I forgot to delete the AS hardline and AS supermarket. The code below should (hopefully) work :laugh: (Please delete the AS hardline and AS supermarket in Query2 and Query3 respectively)

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)
GROUP BY bizdate) AS Query2
ON Query1.bizdate = Query2.bizdate
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90)
GROUP BY bizdate) AS Query3
ON Query1.bizdate = Query3.bizdate

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin Edbert Sydney, Australia

campbells

yes is working!! Thank you so much ^.^v