Combining multiple queries from same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining multiple queries from same table

Author

Topic

ard076
Starting Member

2 Posts

Posted - 2013-10-17 : 23:26:09

I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.

I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?

select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;

OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4

Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 03:11:51

[code]
select clientnumber, SUM(cccnt) AS cccnt, SUM(ckcnt) AS ckcnt, SUM(cacnt) AS cacnt
FROM (your current query
)t
GROUP BY clientnumber
[/code]

For the flag part can you show which column will have purchasedate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ard076
Starting Member

2 Posts

Posted - 2013-10-18 : 05:59:17

I have tried doing the sum, but I have to count payment on CC, CA, CK payments.

My three queries that I have are broken down like this:

select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;

select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CK'
group by clientnumber;

select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CA'
group by clientnumber;

When I run this, it is not calculating my sums correctly:

select clientnumber, SUM(cccnt) AS cccnt, SUM(ckcnt) AS ckcnt, SUM(cacnt) AS cacnt
FROM (clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type in ('CC','CK','CA)t
GROUP BY clientnumber

Also, how would I set up a flag when I had a creditcard payment and 30 days later I had a check or cash payment?

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 07:55:27

[code]
select
clientnumber,
count(distinct case when payment_type = 'CC' then clientnumber end) as cccnt,
count(distinct case when payment_type = 'CK' then clientnumber end) as ckcnt,
count(distinct case when payment_type = 'CA' then clientnumber end) as cacnt,
CASE WHEN DATEDIFF(dd,MAX(CASE WHEN payment_type = 'CC' THEN paymentdate END),MAX(CASE WHEN payment_type IN ('CA','CK') THEN paymentdate END)) >=30 THEN 1 ELSE 0 END AS Flag
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231
group by clientnumber;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources