Wednesday, November 27, 2013

Pivot or Crosstab SQL Query (REPOST)

Based from Visual Basic Forums, I learned a tip on using Crosstab/Pivot queries. This is presented with SQL Server Execution Plan. It's better to use the concept of the second one compared with the first one.
 SELECT SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) = 0  THEN 1 ELSE 0 END) AS Today,   
           SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7 THEN 1 ELSE 0 END) AS Last_Week,   
           SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30 THEN 1 ELSE 0 END) AS [30 Days Ago]  
      FROM temp_uploadpackage  
 SELECT (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) = 0)  AS Today,   
     (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7) AS Last_Week,   
     (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30) AS [30 Days Ago]  

Greg

0 comments:

Post a Comment