Trouble with Combining Selects with Joined tables 1

[michaela18]

I am trying to fit all queries into 1 query but I am getting stuck. I have trouble with the Case and joining tables.


I am trying to get this query

Select COUNT (CASE WHEN CODEID LIKE '%545456%'
		OR	 CODEID LIKE '%878974%'
		THEN CODEID 
                END) AS 'IBM_5_bundle'
FROM 	history
where MailDate between '20100210' and '20100228'
and OrderStat in ('OKAY', 'GOOD)
and OfferCode in ('105', '199', '122')
and filename like '%NL%'

Select COUNT (CASE WHEN CODEID LIKE '%2312315%'
		    OR	 CODEID LIKE '%48678%'
		    THEN CODEID 
                    END) AS 'a',
       COUNT (CASE WHEN CODEID LIKE '%23156%'
		    OR	 CODEID LIKE '%2315648%'
		    THEN CODEID 
                    END) AS 'b',
       COUNT (CASE WHEN CODEID LIKE '%5457756%'
		    OR	 CODEID LIKE '%2315645%'
		    THEN CODEID 
                    END) AS 'c,
       COUNT (CASE WHEN CODEID LIKE '%2315641%'
OR	 CODEID LIKE '%24421412%'
		    OR	 CODEID LIKE '%24421413%'
		    OR	 CODEID LIKE '%24421414%'
		    OR	 CODEID LIKE '%24421415%'
		    OR	 CODEID LIKE '%2442141%'
		    THEN CODEID 
                    END) AS 'd',
	 COUNT (CASE WHEN CODEID LIKE '%244214123%'
		    OR	 CODEID LIKE '%244214126%'
		    OR	 CODEID LIKE '%73408%'
			THEN CODEID 
                    END) AS 'e',
	COUNT (CASE WHEN CODEID LIKE '%23121212%'
		    OR	 CODEID LIKE '%23121215%'
		    THEN CODEID 
                    END) AS 'f',
	COUNT (CASE WHEN CODEID LIKE '%231212111%'
		    OR	 CODEID LIKE '%2312121112%'
		    THEN CODEID 
                    END) AS 'g'

FROM 	history
WHERE 	 between '20100210' and '20100228'
AND 	OrderStat IN ('OKAY', 'GOOD)
AND OfferCode IN ('105', '199', '122')


select IBM_BPLAN = 
(select count(*)
from history a, Package.dbo.PackageType b
where a.LeadRecordID = b.LeadRecordID 
and a.MailDate = '20100210' 
--and calldate >= '20100220'
and a.OrderStat in ('OKAY', 'GOOD)
and a.Priceplan <> ''
and a.OfferCode in ('105', '199', '122')
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232'))
+
(select count(*)
from history a, Leads.dbo.LeadRecordVW_Inbound b
where a.LeadRecordID = b.LeadRecordID 
and a.MailDate  between '20100210' and '20100228'
--and calldate >= '20100220'
and a.OrderStat in ('OKAY', 'GOOD)
and a.Priceplan <> ''
and a.OfferCode in ('105', '199', '122')
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879')
and b.price_plan not in ('72876','72877','72879') 
)

Select  sum((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) as 'IBM_ACS'
from history
where OrderStat in ('OKAY', 'GOOD)
and MailDate  between '20100210' and '20100228'
--and calldate >= '20100220'
and OfferCode IN ('105', '199', '122')
and PanelList > ' '

Into

select MailDate,

      COUNT (CASE WHEN 	CODEID LIKE '%545456%' OR 	
			CODEID LIKE '%878974%'
			and FileNameExported like '%NL%'
			THEN 1 
                	END) AS [A],

	COUNT (CASE WHEN CODEID LIKE '%2312315%'
		    OR	 CODEID LIKE '%48678%'
		    THEN 1 
                    END) AS [B],

       COUNT (CASE WHEN CODEID LIKE '%23156%'
		    OR	 CODEID LIKE '%2315648%'
		    THEN 1 
                    END) AS [B,

       COUNT (CASE WHEN CODEID LIKE '%5457756%'
		    OR	 CODEID LIKE '%2315645%'
		    THEN 1 
                    END) AS [D],

       COUNT (CASE WHEN CODEID LIKE '%2315641%'
		    OR	 CODEID LIKE '%24421412%'
		    OR	 CODEID LIKE '%24421413%'
		    OR	 CODEID LIKE '%24421414%'
		    OR	 CODEID LIKE '%24421415%'
		    OR	 CODEID LIKE '%2442141%'
		    THEN 1 
                    END) AS [E],
	 COUNT (CASE WHEN CODEID LIKE '%244214123%'
		    OR	 CODEID LIKE '%244214126%'
		    OR	 CODEID LIKE '%73408%'
			THEN 1 
                    END) AS [F],
	COUNT (CASE WHEN CODEID LIKE '%23121212%'
		    OR	 CODEID LIKE '%23121215%'
		    THEN 1 
                    END) AS [G],
	COUNT (CASE WHEN CODEID LIKE '%231212111%'
		    OR	 CODEID LIKE '%2312121112%'
		    THEN 1 
                    END) AS [H],
	sum (case when PanelList > ' '
		and 
		((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) 
		THEN 1 
		end) as[I]

from history
where OrderStat in ('OKAY', 'GOOD)
and MailDate  between '20100210' and '20100228'
and OfferCode IN ('105', '199', '122')

I have no idea how to incorporate this part into a case:

select IBM_BPLAN = 
(select count(*)
from history a, Package.dbo.PackageType b
where a.LeadRecordID = b.LeadRecordID 
and a.MailDate = '20100210' 
--and calldate >= '20100220'
and a.OrderStat in ('OKAY', 'GOOD)
and a.Priceplan <> ''
and a.OfferCode in ('105', '199', '122')
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232'))
+
(select count(*)
from history a, Leads.dbo.LeadRecordVW_Inbound b
where a.LeadRecordID = b.LeadRecordID 
and a.MailDate  between '20100210' and '20100228'
--and calldate >= '20100220'
and a.OrderStat in ('OKAY', 'GOOD)
and a.Priceplan <> ''
and a.OfferCode in ('105', '199', '122')
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879')
and b.price_plan not in ('72876','72877','72879') 
)

and I also get an error here:

sum (case when PanelList > ' '
		and 
		((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) 
		THEN 1 
		end) as[I]

at the Then part

 

[Crowley16]

fistly, why are you trying to incorporate all this into 1 query? It seems easier to do this in multiple steps and wrap a stored procedure around it.

secondly, have you considered using pivot, just a little easier than doing that amount of case statements. Although you'll probably need to build some sort of processing temp table...

--------------------
Procrastinate Now!

 

[michaela18]

Well I want to case them because I want my results all in 1 result pane so then I can simply copy and paste it into excel and do vlookups

 

[Crowley16]

what I was suggesting was something like:

create proc <ProcName> 
   --create temp table

   --insert values into temp table
   insert into <temp table> select ... where ... like '%1111%' and ...
   insert into <temp table> select ... where ... like '%2222%' and ...
   ...

   --extract results
go

later, when calling this stored procedure, you'll still get 1 result set, and if you use PIVOT, you can denormalise the resultset you get...

--------------------
Procrastinate Now!

 

[michaela18]

If I create a procedure then can I change it since I have to run it daily so the date will always change?

 

[markros]

You need to pass your dates to stored procedure or calculate them inside the stored procedure.

PluralSight Learning Library

 

[Crowley16]

maybe use getdate()?
or pass the date into the stored procedure as a input variable?

you can even setup a job to run this proc automatically every day and have the results sent to a csv file, or e-mailed to you...

the possibilities are endless... (almost)

--------------------
Procrastinate Now!

 

[michaela18]

how would one set up this procedure?

something like:

create procedure

@startdate datetime
@enddate datetime

CODE HERE?

 

[Crowley16]

yes exactly that...

I suggest you have a look in Books Online for stored procedures

--------------------
Procrastinate Now!