英文:
SQL QUERY: IF Customer is NEW then 1 else 0
问题
我想找到一个查询，让我找到新客户何时有发票，然后将该客户标记为'NEW'为1，然后按EOMONTH(date)分组。
数据库中我有以下列：
日期
帐户ID
帐户名称
收入
最终输出可能如下所示：
日期 帐户ID 帐户名称 收入 状态
30/06/2023 123 123有限公司 £123 1
30/06/2023 1234 1234有限公司 £1000 0
任何帮助都将不胜感激。
英文:
I would like to find the query that allows me to find when a new customer has an invoice then mark that client as 1 for 'NEW' and then group it by by EOMONTH(date)
The columns I have in the database
Date
Account_Id
Account_Name
Revenue
Final output would look something like this
Date Account Id Account Name Revenue Status
30/06/2023 123 123 Limited £123 1
30/06/2023 1234 1234 Limited £1000 0
Any help would be really appreciated
答案1
得分: 1
考虑到发票具有以下查询所示的单独表格
select eOmonth(date) Date, Account_Id, Account_Name, sum(Revenue) Revenue
, (case when (select top 1 1 from Invoices where Invoices.DocumentNumber = customer.Account_Id) = 1 then 1 else 0 end) status
from customer
group by eOmonth(date), Account_Id, Account_Name
如果发票在表格内，按照以下查询
select eOmonth(date) Date, Account_Id, Account_Name, sum(Revenue) Revenue
, (case when Invoice is not null then 1 else 0 end) status
from customer
group by eOmonth(date), Account_Id, Account_Name
, (case when Invoice is not null then 1 else 0 end)
根据您的需求使用 CASE 表达式。
英文:
Considered that invoices has separate table follow below query
select eOmonth(date) Date, Account_Id, Account_Name, sum(Revenue) Revenue
, (case when (select top 1 1 from Invoices where Invoices.DocumentNumber = customer.Account_Id) = 1 then 1 else 0 end) status
from customer
group by eOmonth(date), Account_Id, Account_Name
If invoice is with in the table follow below query
select eOmonth(date) Date, Account_Id, Account_Name, sum(Revenue) Revenue
, (case when Invoice is not null then 1 else 0 end) status
from customer
group by eOmonth(date), Account_Id, Account_Name
, (case when Invoice is not null then 1 else 0 end)
Use CASE expression based on your requirements.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论