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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

abhilash SSC Enthusiast Points: 118 More actions · Answer 2

Dear Boss,

Thanks its working. Thanks for the great help