Archive | SQL RSS feed for this section

Counting records based on Condition

4 Oct

SELECT PCPNO,IPA,[Provider Name], COUNT(DISTINCT MEMBNO) as “Number_Of_Members”
FROM PCP
GROUP BY PCPNO, IPA, [Provider Name]
HAVING COUNT(DISTINCT IPA) = 1
ORDER BY Number_Of_Members desc

SQL Join Alternatives Explained

21 Jul

http://www.mssqltips.com/tip.asp?tip=1667

Multiple tables Left Join Example

21 Jul

SELECT STAFF_ACT.STORE, STAFF_ACT.P8 AS STAFF_ACT_P8,
STAFF_ACT.P7+STAFF_ACT.P8 AS STAFF_ACT_QTD,
STAFF_ACT.P1+STAFF_ACT.P2+STAFF_ACT.P3+STAFF_ACT.P4+STAFF_ACT.P5+STAFF_ACT.P6+
STAFF_ACT.P7+STAFF_ACT.P8 AS STAFF_ACT_YTD,
STAFF_IDEAL.P8 STAFF_IDEAL_P8,
STAFF_IDEAL.P7+STAFF_IDEAL.P8 AS STAFF_IDEAL_QTD,
–YTD
STAFF_IDEAL.P1+STAFF_IDEAL.P2+STAFF_IDEAL.P3+STAFF_IDEAL.P4+STAFF_IDEAL.P5+STAFF_IDEAL.P6+
STAFF_IDEAL.P7+STAFF_IDEAL.P8 AS STAFF_IDEAL_YTD,
RET_ACT.P8 AS RET_ACT_P8,
RET_ACT.P7+RET_ACT.P8 AS RET_ACT_QTD,
–YTD
RET_ACT.P1+RET_ACT.P2+RET_ACT.P3+RET_ACT.P4+RET_ACT.P5+RET_ACT.P6+
RET_ACT.P7+RET_ACT.P8 AS RET_ACT_YTD,
RET_IDEAL.P8 AS RET_IDEAL_P8,
RET_IDEAL.P7+RET_IDEAL.P8 AS RET_IDEAL_QTD,
–YTD
RET_IDEAL.P1+RET_IDEAL.P2+RET_IDEAL.P3+RET_IDEAL.P4+RET_IDEAL.P5+RET_IDEAL.P6+
RET_IDEAL.P7+RET_IDEAL.P8 AS RET_IDEAL_YTD,
SALES_ACT.P8 AS SALES_ACT_P8,
SALES_ACT.P7+SALES_ACT.P8 AS SALES_ACT_QTD,
–YTD
SALES_ACT.P1+SALES_ACT.P2+SALES_ACT.P3+SALES_ACT.P4+SALES_ACT.P5+SALES_ACT.P6+
SALES_ACT.P7+SALES_ACT.P8 AS SALES_ACT_YTD,
FROM STAFF_ACT LEFT JOIN STAFF_IDEAL
ON STAFF_ACT.STORE = STAFF_IDEAL.STORE LEFT JOIN RET_ACT
ON STAFF_ACT.STORE = RET_ACT.STORE LEFT JOIN RET_IDEAL
ON STAFF_ACT.STORE = RET_IDEAL.STORE LEFT JOIN SALES_ACT
ON STAFF_ACT.STORE = SALES_ACT.STORE

Boolean logic with nested case Example in SQL

8 Jul

SELECT ERI_Organization_KEY, ROUND(ROUND(R_2010P06,2),1),ROUND(ROUND(RQ_2010P06,2),1),ROUND(ROUND(RY_2010P06,2),1),
CASE WHEN R_2010P06 >= ‘0’ THEN
CASE WHEN ROUND(ROUND(R_2010P06,2),1) <= ‘0.14’ THEN ‘3.0A’
WHEN ROUND(ROUND(R_2010P06,2),1) <= ‘0.55’ THEN ‘4.0A’
WHEN ROUND(ROUND(R_2010P06,2),1) >= ‘0.56’ THEN ‘5.0A’
END
ELSE
CASE WHEN R_2010P06 < ‘0’ THEN
CASE WHEN ROUND(ROUND(R_2010P06,2),1) <= ‘-0.54’ THEN ‘1.0B’
WHEN ROUND(ROUND(R_2010P06,2),1) <= ‘-0.14’ THEN ‘2.0B’
WHEN ROUND(ROUND(R_2010P06,2),1) <= ‘-0.01’ THEN ‘3.0B’ END
END END AS R_2010P06,
CASE WHEN RQ_2010P06 >= ‘0’ THEN
CASE WHEN ROUND(ROUND(RQ_2010P06,2),1) <= ‘0.14’ THEN ‘3.0A’
WHEN ROUND(ROUND(RQ_2010P06,2),1) <= ‘0.55’ THEN ‘4.0A’
WHEN ROUND(ROUND(RQ_2010P06,2),1) >= ‘0.56’ THEN ‘5.0A’
END
ELSE
CASE WHEN RQ_2010P06 < ‘0’ THEN
CASE WHEN ROUND(ROUND(RQ_2010P06,2),1) <= ‘-0.54’ THEN ‘1.0B’
WHEN ROUND(ROUND(RQ_2010P06,2),1) <= ‘-0.14’ THEN ‘2.0B’
WHEN ROUND(ROUND(RQ_2010P06,2),1) <= ‘-0.01’ THEN ‘3.0B’ END
END END AS RQ_2010P06,
CASE WHEN RY_2010P06 >= ‘0’ THEN
CASE WHEN ROUND(ROUND(RY_2010P06,2),1) <= ‘0.14’ THEN ‘3.0A’
WHEN ROUND(ROUND(RY_2010P06,2),1) <= ‘0.55’ THEN ‘4.0A’
WHEN ROUND(ROUND(RY_2010P06,2),1) >= ‘0.56’ THEN ‘5.0A’
END
ELSE
CASE WHEN RY_2010P06 < ‘0’ THEN
CASE WHEN ROUND(ROUND(RY_2010P06,2),1) <= ‘-0.54’ THEN ‘1.0B’
WHEN ROUND(ROUND(RY_2010P06,2),1) <= ‘-0.14’ THEN ‘2.0B’
WHEN ROUND(ROUND(RY_2010P06,2),1) <= ‘-0.01’ THEN ‘3.0B’ END
END END AS RY_2010P06
FROM BSC_R_COS_2010P06

SQL Functions Fundamentals

6 Jul

Ref:http://www.functionx.com/sqlserver/functions/functions.htm

Good – Truncate (not round) decimal places in SQL Server

25 Jun

Ref:http://stackoverflow.com/questions/44046/truncate-not-round-decimal-places-in-sql-server

REFERENCE:SELECT INTO, INSERT INTO

23 Jun

English

Ref:http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Spanish

Ref:http://www.guillesql.es/Articulos/SELECT_INTO_INSERT_INTO.aspx

This sometimes can be very handy.