none
Between two dates comparison last three months sales RRS feed

  • Dotaz

  • Hi Everyone,

    Please find the query which i'm currently using to get date .. we have compare last 3months given dates

    Declare @fromdate int 
    declare @today int 
    set @fromdate=20190801
    set @today=20190815
    
    SELECT 
    A.TERMINALID ,
    replace(replace(A.CompanyName, char(13), ''), char(10), '')TERMINALNAME,C.TRAVELAGENTID STOCKISTID,
    replace(replace(C.COMPANYNAME, char(13), ''), char(10), '')STOCKISTNAME,SALESPERSONNAME,A.CITY,
    E.STATENAME,F.REGIONNAME
    ,SUM(CASE WHEN ISNULL(ICASHDMRAMOUNT,0)<>0 THEN ICASHDMRAMOUNT ELSE 0 END) ICASHDMRAMOUNT
    FROM GM_TERMINAL A
    JOIN TRA_PRODUCT_NETSALES  B ON A.TERMINALID=B.TERMINALID
    JOIN GM_TRAVELAGENT C ON A.TRAVELAGENTID=C.TRAVELAGENTID
    JOIN GM_SALESPERSON D ON A.SALESPERSONID=D.SALESPERSONID
    JOIN GM_STATE E ON A.STATEID=E.STATEID
    JOIN GM_REGION F ON E.REGIONID=F.REGIONID
    WHERE C.PROVIDERID=3 AND DATEKEY between @fromdate and @today
    
    AND (ISNULL(ICASHDMRAMOUNT,0)<>0 OR ISNULL(MTAMOUNT,0)<>0 OR ISNULL(ICASHDMRAMOUNT,0)<>0 OR ISNULL(INDUSDMRAMOUNT,0)<>0
    OR ISNULL(RBLDMRAMOUNT,0)<>0 OR ISNULL(AEPSAMOUNT,0)<>0 OR ISNULL(YBL_AEPSAMOUNT,0)<>0) 
    GROUP BY A.TERMINALID ,A.COMPANYNAME,C.TRAVELAGENTID,C.COMPANYNAME,SALESPERSONNAME,A.CITY,E.STATENAME,F.REGIONNAME
    

    Actually out put i need to show 

    TERMINALID CITY STATENAME REGIONNAME 1 to 15 icc_aug 1 to 15 icc_sep 1 to icc_oct
    INAMD03000012J SURAT Gujarat WEST 41207.18 0 0
    INAMD030000236 Bharuch Gujarat WEST 0 0 13344
    INAMD03000100A Ahmedabad Gujarat WEST 0 0 0
    INAMD030001024 Ahmedabad Gujarat WEST 103252.35 0 0
    INAMD03000102Z Ahmedabad Gujarat WEST 114505.44 0 233444
    INAMD03900030G SURAT Gujarat WEST 0 75424.8068 0
    INAMD039000312 SURAT Gujarat WEST 1469796.79 0 0


    baskarlakshmi


    • Upravený baskaLakshmi úterý 22. října 2019 4:26 query parameter correction
    pondělí 21. října 2019 7:51

Všechny reakce

  • Hi ,

    Thank you for your posting,

     

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    Also , I am not sure what is your requirement. Could you explain more clearly?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 22. října 2019 7:40
  • Hi

    Avatar of Rachel_Wang

    Rachel_Wang

    Please find the code

    Create table Net_sales (terminalid varchar(14),Region varchar(20),City varchar(50),ICC_TRNS_COUNT int,ICC_AMount money,Trns_Date datetime)
    
    --SELECT * FROM Net_sales
    
    insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,5000,'08/01/2019')
    insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,4000,'08/02/2019')
    insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,200,'08/04/2019')
    
    
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'08/01/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,40700,'08/02/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,2200,'08/04/2019')
    
    
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'09/01/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,47000,'09/02/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,2200,'09/10/2019')
    
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'10/01/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,70000,'10/02/2019')
    insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,3200,'10/10/2019')
    
    
    -------------------
    DECLARE @FROMDATE DATETIME
    DECLARE @TODAY DATETIME 
    SET @FROMDATE='08/01/2019'
    SET @TODAY='10/10/2019'
    
    SELECT 
    TERMINALID ,
    CITY,
    REGION,
    MONTH(TRNS_DATE),
    SUM(ICC_AMOUNT) ICASHDMRAMOUNT
    FROM NET_SALES WHERE CONVERT(VARCHAR,TRNS_DATE,101) BETWEEN CONVERT(VARCHAR,@FROMDATE,101) AND CONVERT(VARCHAR,@TODAY,101)
    GROUP BY TERMINALID ,
    CITY,
    REGION,
    MONTH(TRNS_DATE)
    ORDER  BY TERMINALID

    CURRENT RESULT
    TERMINALID CITY REGION ICASHDMRAMOUNT
    INAMD030001024 Gujarat WEST 94200
    INAMD03900030G Gujarat WEST 94900
    INMAA031000000 CHENNAI SOUTH 5000

    Expected result
    TERMINALID CITY REGION ICASHDMRAMOUNT_AUG ICASHDMRAMOUNT_SEP ICASHDMRAMOUNT_OCT
    INAMD030001024 Gujarat WEST 94200 94200 94200
    INAMD03900030G Gujarat WEST 94900 101200 125200
    INMAA031000000 CHENNAI SOUTH 5000 0 0


    baskarlakshmi

    úterý 22. října 2019 8:19
  • Your current result is not what you posted from your query.
    úterý 22. října 2019 16:10
    Moderátor