Thursday, March 28, 2013

[SQL Server] Query join

[SQL Server] Query join

Query join

Posted: 28 Mar 2013 07:05 AM PDT

Hi there, hope in your help.In my DB I've two tables: TABLE_LONG and TABLE_SHORT.The two tables are equal but in TABLE_LONG a recording long events and in TABLE_SHORT a recording short events.If count number of long events, I've this output:[code]SELECT COALESCE (idDGIG, 'Tot') AS sGIG, `NUMBER`FROM ( SELECT LEFT (idDGIG, 2) AS idDGIG, COUNT(idDGIG) AS NUMBER FROM TABLE_LONG WHERE 1 AND ( LEFT (idDGIG, 2) LIKE '%QM%' OR LEFT (idDGIG, 2) LIKE '%QI%' OR LEFT (idDGIG, 2) LIKE '%QO%' OR LEFT (idDGIG, 2) LIKE '%QS%' ) AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) GROUP BY LEFT (idDGIG, 2) WITH ROLLUP ) AS Q;+--------+--------+| sGIG | NUMBER |+--------+--------+| QI | 9 || QM | 2 || QO | 6 || QS | 5 || Tot | 22 |+--------+--------+5 rows in set[/code]If count number of short events, I've this output:[code]SELECT COALESCE (idDGIG, 'Tot') AS sGIG, NUMBERFROM ( SELECT LEFT (idDGIG, 2) AS sGIG, COUNT(idDGIG) AS NUMBER FROM TABLE_SHORT WHERE 1 AND ( LEFT (idDGIG, 2) LIKE '%QM%' OR LEFT (idDGIG, 2) LIKE '%QI%' OR LEFT (idDGIG, 2) LIKE '%QO%' OR LEFT (idDGIG, 2) LIKE '%QS%' ) AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) GROUP BY LEFT (idDGIG, 2) WITH ROLLUP ) AS Z;+--------+-----------+| sGIG | NUMBER |+--------+-----------+| QI | 2 || QM | 2 || QO | 16 || QS | 6 || Tot | 26 |+--------+-----------+5 rows in set[/code]Now I need tried join two tables with this query; I think in output total events ( long + short ): 22+26 = 48.Instead I've this wrong output (1144), why? Can you help me.Thanks in advance.[code]SELECT DATE_START, COALESCE (idDGIG, 'Tot') AS sGIG, `NUMBER`FROM ( SELECT CA.DATE_START AS DATE_START, LEFT (CA.idDGIG, 2) AS sGIG, COUNT(CA.idDGIG) + COUNT(A.EVENT) AS NUMBER FROM TABLE_LONG CA JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START WHERE CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) AND ( LEFT (CA.idDGIG, 2) LIKE '%QM%' OR LEFT (CA.idDGIG, 2) LIKE '%QI%' OR LEFT (CA.idDGIG, 2) LIKE '%QO%' OR LEFT (CA.idDGIG, 2) LIKE '%QS%' ) GROUP BY LEFT (CA.idDGIG, 2) WITH ROLLUP ) AS SSS;+-------------+--------+--------+| DATE_START | sGIG | NUMBER |+-------------+--------+--------+| 2013-03-27 | QI | 468 || 2013-03-27 | QM | 104 || 2013-03-27 | QO | 312 || 2013-03-27 | QS | 260 || 2013-03-27 | Tot | 1144 |+-------------+--------+--------+5 rows in set[/code]

SQL Logical errors

Posted: 27 Mar 2013 04:57 PM PDT

Hi All,I need some one help me identify why am getting logical errors in the output of my query below. Am new to SQL.Thanks. use MSIU;select DISTINCT [dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE AS IssuanceDate,[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE,[dbo].[tbl_SALES_TEAM_MASTER].[str_SALES_TEAM_NAME],[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE As VouchersSold,[dbo].[tbl_VOUCHER_CAPTURE].dt_VOUCHER_ISSUE_DATE AS SalesDate,[dbo].[tbl_SALES_EXECUTIVE_MASTER].str_SALES_EXECUTIVE_NAME AS BCCName,[dbo].[tbl_DISTRIBUTOR_MASTER].[str_DISTRIBUTOR_NAME] AS CBDName,[dbo].[tbl_DISTRICT_MASTER].str_DISTRICT_NAME CBD_DistrictFROM[dbo].[tbl_VOUCHER_ISSUE]Left Join [dbo].[tbl_VOUCHER_ISSUE_DETAIL]ON [dbo].[tbl_VOUCHER_ISSUE].int_VOUCHER_ISSUE_ID = [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_IDLeft Join [dbo].[tbl_SALES_TEAM_MASTER]ON [dbo].[tbl_VOUCHER_ISSUE].int_SALES_TEAM_ID = [dbo].[tbl_SALES_TEAM_MASTER].int_SALES_TEAM_IDLeft Join [dbo].[tbl_VOUCHER_CAPTURE_DETAIL]ON [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID = [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_ISSUE_DETAIL_IDLeft Join [dbo].[tbl_VOUCHER_CAPTURE]ON [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_CAPTURE_ID = [dbo].[tbl_VOUCHER_CAPTURE].int_VOUCHER_CAPTURE_IDLeft Join [dbo].[tbl_SALES_EXECUTIVE_MASTER]ON [dbo].[tbl_VOUCHER_CAPTURE].int_SALES_EXECUTIVE_ID = [dbo].[tbl_SALES_EXECUTIVE_MASTER].int_SALES_EXECUTIVE_IDLeft Join [dbo].[tbl_DISTRIBUTOR_MASTER]ON [dbo].[tbl_VOUCHER_CAPTURE].[int_DISTRIBUTOR_ID] = [dbo].[tbl_DISTRIBUTOR_MASTER].[int_DISTRIBUTOR_ID]Left Join [dbo].[tbl_DISTRICT_MASTER]ON [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRICT_ID = [dbo].[tbl_DISTRICT_MASTER].int_DISTRICT_IDWHERE[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE LIKE 'FPUG%' AND[dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE between '2012-06-01' and '2012-06-30' AND[dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_STATUS !=5

Columns to be included in Index

Posted: 27 Mar 2013 04:26 PM PDT

Dear AllI have one table with millions of rows, with columns as Id, stringcol1,stringcol2,Bigintcol3,numericcol4Primary key is Id.Index created on stringcol1When I query this table ,select sum(numericcol4), Bigintcol3from abcwhere stringcol1 = @stringcol1 and stringcol2 = @stringcol2and Bigintcol3 in not nullgroup by Bigintcol3 It shows me to create index with columns as stringcol1,stringcol3,Bigintcol3 include numericcol4If I exclude any column from the recommended index and check Estimated Plan it shows as index scan on Primary keyDoes it means that in sql 2008 R2 we need to create indexes to include all the column in the "where clause" and also add filed of the select statement in the include column list ? Regards Krishna1

Error authenticating proxy

Posted: 27 Mar 2013 08:52 PM PDT

Hi,I've got a SQL Server 2008 job which ran ok yesterday but has fail today with an error message ' Error authenticating proxy'. The only thing that has changed bewteen the two runs is my Windows AD password. I thought the report had been set up to use a generic password we have that doesn't change but I must be wrong.Question 1. I need to get this report to run but I don't know where to look in SSMS to correct the password.Question 2. How do I amend the job so that it uses the generice password and I don't get this problem next month.Many thanks for any helpStuart.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog