I need some help to update my current SQL script to count with specific date for a year. From my testing, i have no luck so far to get the correct value in the result page.
Here is the details on the requirement:
1 Jul - 15 Jul 2023
16 Jul - 15 Aug 2023
16 Aug - 15 Sep 2023
16 Sep - 15 Oct 2023
16 Oct- 15 Nov 2023
16 Nov - 15 Dec 2023
16 Dec - 15 Jan 2024
Here is my current output:
Here is my expected output:
Below is my current script:
SELECT YEAR(last_modified_date) as TokenYear,
MONTH(last_modified_date) as TokenMonth,
count (*) AS TotalToken into #tmptoken
FROM CC_CARD
where YEAR(last_modified_date) < 2024
and merchant_id = 'user1'
and status="single"
GROUP BY YEAR(last_modified_date), MONTH(last_modified_date)
ORDER BY YEAR(last_modified_date), MONTH(last_modified_date)
select *, sum(Totaltoken) over (order by tokenyear, tokenmonth) as Running_Total
from #tmptoken
Please help on my issue here.
Expected output ?
Sample data and expected results will help us help you here.
@AmiraBedhiafi My expected output? I cant get the calculation right with the script i shared earlier. My current script count data for each month, now the requirement need me to count the data starting date from 16 to 15 for each month. Is this answering your question? Thanks.
don’t add the output as a comment, add it to the question