IN

Need to remember that when wanting to allow end users to pick multiple parameters to include an IN statment!!!

gotta use IN (@PARAM) to handle multiple

Example:
WHERE a.value IN (@PARAM)

Convert date time in a QUERY! (not changing a table)

CONVERT(datetime,CONVERT(varchar(10),GETDATE(),101))

Example in use
Convert (char(10),bc.sxbc_next_billto_dttm,101) AS sxbc_next_billto_dttm,

Case Statement for Pivots

Case statements I created to show some Row information in Columns.


CASE cpl.ProductLineCode WHEN 'LP' THEN cpl.Multiplier ELSE 0 END as LPMultiplier,
CASE cpl.ProductLineCode WHEN 'DP' THEN cpl.Multiplier ELSE 0 END as DPMultiplier,
CASE cpl.ProductLineCode WHEN 'CQ' THEN cpl.Multiplier ELSE 0 END as CQMultiplier,
Totaling amounts

SUM (CASE cpl.ProductLineCode WHEN 'LP' then uf.Amount ELSE 0 END) as LPUplift,
SUM (CASE cpl.ProductLineCode WHEN 'DP' then uf.Amount ELSE 0 END) as DPUplift,
SUM (CASE cpl.ProductLineCode WHEN 'CQ' then uf.Amount ELSE 0 END) as CQUplift

Case Statement adding description

Case statements I created to add an actual description in place of a integer value Devs used.

CASE b.Status
When 1 then 'Pending'
When 2 then 'Submitted'
When 3 then 'Rejected'
When 4 then 'Posted'
When 5 then 'Processed'
When 6 then 'Confirmed'
Else '0'
End as 'Status',


CASE c.AnniversaryMonth
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
ELSE '0'
END AS 'AnniversaryMonth',

Count() Function

Ive seen it used but never used it myself. I did have the need to use it today!
Input
SELECT COUNT(*) AS num_cust
FROM Customers
Results num_cust ----------- 5

Analysis
In the example above, Count(*) is used to count all roes, regardless of values. The count is returned in num_cus

My select statement:
SELECT count(cus_cd)
FROM dbo.customer
WHERE (cus_status = 'A')

I returned the number of active customers

First Post

First off let me welcome you to this blog. I created this to post techno-babble and interesting thinks and factoids found and sent to me by friends. Im now including a possible wide assortment of SQL codes I find to help my noob brain. Comments, advise, teachings are most welcome. Rudeness will not be tolerated and such comments will be deleted.