Consult bosses, how do you write the SQL statement, thank you first-CodePudding

CodePudding user response:

To provide a test data to the great god

 
In the CREATE TABLE # A (VARCHAR (20), initial int, warehousing int, outbound int, balance int) 
# INSERT INTO A values (' 2020-01, 0187, 28619, 5125, 33) 
# INSERT INTO A values (' 2020-02, 12533, 60839-434112) 
# INSERT INTO A values (' 2020-03, 839179, 00156, 05313 (4) 
# INSERT INTO A values (' 2020-04, 3134711, 2192, 9831 (7) 
# INSERT INTO A values (' 2020-05, 8317,0,7509,809) 
# INSERT INTO A values (' 2020-06, 809199, 89202, 12586) 
# INSERT INTO A values (' 2020-07, 586231, 88896, 9148 04) 
# INSERT INTO A values (' 2020-08, 14804124, 04223, 20488, 9) 
# INSERT INTO A values (' 2020-09, 4889410, 84355, 12104, 61) 
# INSERT INTO A values (' 2020-10, 10461336, 84366, 69747 6)

CodePudding user response:

 
# INSERT INTO A values (' 2020-05, 8317,0,7509,809) 
# INSERT INTO A values (' 2020-06, 809199, 89202, 12586) 
# INSERT INTO A values (' 2020-07, 586231, 88896, 9148 04) 
# INSERT INTO A values (' 2020-08, 14804124, 04223, 20488, 9) 
# INSERT INTO A values (' 2020-09, 4889410, 84355, 12104, 61) 
# INSERT INTO A values (' 2020-10, 10461336, 84366, 69747 6) 

Declare @ time1 varchar (20), @ time2 varchar (20) 
The set @ time1='2020-03' 
The set @ time2='the 2020-07' 
Select 
@ time1 + '-' + @ time2 during, 
(select a1. Beginning from # A a1 where the CONVERT (date, a1 + '- 01)=DATEADD (MM, 1, CONVERT (date, @ time1 +' - 01))) at the beginning, 
The SUM (a.) warehousing, 
The SUM (a. outbound).besides, 
(select a1. Balance the from # A a1 where a1.=@ time2) balance 
The from # aa 
Where the CONVERT (date, a. + '- 01) between DATEADD (MM, 1, CONVERT (date, @ time1 +' - 01)) and the CONVERT (date, @ time2 + '- 01)

So '2020-03' and 'the 2020-07' is a variable, but why say three to seven months even after 2 months

CodePudding user response:

 
In the CREATE TABLE # A (VARCHAR (20), initial int, warehousing int, outbound int, balance int) 
# INSERT INTO A values (' 2020-01, 0187, 28619, 5125, 33) 
# INSERT INTO A values (' 2020-02, 12533, 60839-434112) 
# INSERT INTO A values (' 2020-03, 839179, 00156, 05313 (4) 
# INSERT INTO A values (' 2020-04, 3134711, 2192, 9831 (7) 
# INSERT INTO A values (' 2020-05, 8317,0,7509,809) 
# INSERT INTO A values (' 2020-06, 809199, 89202, 12586) 
# INSERT INTO A values (' 2020-07, 586231, 88896, 9148 04) 
# INSERT INTO A values (' 2020-08, 14804124, 04223, 20488, 9) 
# INSERT INTO A values (' 2020-09, 4889410, 84355, 12104, 61) 
# INSERT INTO A values (' 2020-10, 10461336, 84366, 69747 6) 


Select 
Q1. [in] 
Q1. [opening] 
, (select sum ([Treasury]) from # A where cast ([in] + '- 01' as A datetime) between cast (q1. [in] + '- 01' as A datetime) and cast (' 2020-07 '+' - 01 'as A datetime)) as [Treasury] 
, (select sum (/outbound) from # A where cast ([in] + '- 01' as A datetime) between cast (q1. [in] + '- 01' as A datetime) and cast (' 2020-07 '+' - 01 'as A datetime)) as (outbound) 
, (select [inventory] from # A q1 where [in]='the 2020-07) as [inventory] 
The from # A q1 where [in]='2020-02'

CodePudding user response:

reference 1/f, kun jins kao kun jins kao response:

great god the first to provide a test data

 
In the CREATE TABLE # A (VARCHAR (20), initial int, warehousing int, outbound int, balance int) 
# INSERT INTO A values (' 2020-01, 0187, 28619, 5125, 33) 
# INSERT INTO A values (' 2020-02, 12533, 60839-434112) 
# INSERT INTO A values (' 2020-03, 839179, 00156, 05313 (4) 
# INSERT INTO A values (' 2020-04, 3134711, 2192, 9831 (7) 
# INSERT INTO A values (' 2020-05, 8317,0,7509,809) 
# INSERT INTO A values (' 2020-06, 809199, 89202, 12586) 
# INSERT INTO A values (' 2020-07, 586231, 88896, 9148 04) 
# INSERT INTO A values (' 2020-08, 14804124, 04223, 20488, 9) 
# INSERT INTO A values (' 2020-09, 4889410, 84355, 12104, 61) 
# INSERT INTO A values (' 2020-10, 10461336, 84366, 69747 6)

Forgot to micrometer a comma

 
In the select a., 
Case the when a. initial=0 then the '-' the else REPLACE (convert (varchar, cast (a. at the beginning as money), 1), '00', ') end at the beginning, 
Case when a. warehousing=0 then the '-' the else REPLACE (convert (varchar, cast (a. warehousing as money), 1), '00', ') end put in storage, 
Case the when a. outbound=0 then the '-' the else REPLACE (convert (varchar, cast (a. outbound as money), 1), '00', ') end outbound, 
Case the when a. balance=0 then the '-' the else REPLACE (convert (varchar, cast (a. balance as money), 1), '00', ') end the balance 
Into # B 
The from # aa 

In the select a., 
Case the when a. initial="' then 0 else convert (int, REPLACE (a. at the beginning, ', ', ')) end at the beginning, 
Case when a. warehousing="' then 0 else convert (int, REPLACE (a. warehousing, ', ', ')) end put in storage, 
Case the when a. outbound="' then 0 else convert (int, REPLACE (a. outbound, ', ', ')) end outbound, 
Case the when a. balance="' then 0 else convert (int, REPLACE (a. balance, ', ', ')) end the balance 
Into # C 
The from # a, B

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull