Need to Display date wise sales summary.

Question

Need to Display date wise sales summary.

abhilash

SSC Enthusiast

Points: 118
More actions
April 29, 2020 at 4:10 pm

#3747372

Hi,
I have created a stored procedure like follows
select issuedate,
SUM((CASE WHEN ISNUMERIC(netpayable)=1
THEN CONVERT(MONEY,netpayable)
ELSE 0
END)
)
AS TotAmt
from tbktym_TicketPrimeDetails
where
LEFT(CONVERT(DATETIME,issuedate,103),12)>=LEFT(CONVERT(DATETIME,'01-02-2020',103),12) and
LEFT(CONVERT(DATETIME,issuedate,103),12)<=LEFT(CONVERT(DATETIME,'03-02-2020',103),12)
and cstatus='billed' and branchid=1
group by issuedate
The result is showing like
2020-02-03 00:00:00.000 252397.86
2020-02-02 00:00:00.000 500.00
2020-02-01 00:00:00.000 401757.00
2019-02-03 00:00:00.000 119547.00
2019-02-02 00:00:00.000 395913.00
In the query result 2020-02-03 & 2020-02-02 data viewing multiple time with different values. How can we solve the issue.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246692 More actions · Answer 1

This might help:

SELECT calc.dt
      ,TotAmt = SUM(   (CASE
                            WHEN ISNUMERIC(netpayable) = 1 THEN
                                CONVERT(MONEY, netpayable)
                            ELSE
                                0
                        END
                       )
                   )
FROM tbktym_TicketPrimeDetails
    CROSS APPLY
(SELECT dt = CAST(IssueDate AS DATE)) calc
WHERE calc.dt
      BETWEEN '20200102' AND '20200302'
      AND cstatus = 'billed'
      AND branchid = 1
GROUP BY calc.dt;

I'd also recommend not using the MONEY datatype, as you will find that it generates rounding errors. DECIMAL works better.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

abhilash SSC Enthusiast Points: 118 More actions · Answer 2

Dear Boss,

Thanks its working. Thanks for the great help