Click here to Skip to main content
15,867,750 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
This is my master table :

B J A S B As k M P Ma F
31 32 31 32 31 30 30 30 29 29 30

C
31

I am using query to generate this table :

Year Start Date End Date Month Sum Days
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 32 0
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 31 32
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 63
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 93
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 123
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 152
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 182
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 211
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 241
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 31 272
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 32 303
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 31 335

This is temp table. Here sum days is calculated from query . The query to calculate sum


SELECT '2073' as [YEAR],'2016-04-13 00:00:00' as Start Date, '2016-04-12 00:00:00' as End Date, CONVERT(INT,(S+B+As+K+M+P+Ma+F+C)) as Sum Days FROM MasterTable GROUP BY S+B+As+K+M+P+Ma+F+C

In this query, issue arises for temp table which has sum days = 272. Here sum days is the sum days of 30+241, (the above records month and sum days sum). the result is to be 271 but it is 272 generated for query. I want 271.
Can any one help me out.

What I have tried:

I had used sum to get result but it is still showing same output.
Posted
Updated 20-Jul-16 20:28pm
Comments
Abhipal Singh 19-Jul-16 6:16am    
Your data is unclear to fix the query you provided. However, I am very sure that the SUM() is fine and some data is not correct in your tables.

Hi Friend,

You did mistake in date different. Check you code where you do date different in your Store procedure.
 
Share this answer
 
I found out the cause. It was because proc was taking wrong month value causing the issue.
Thanks for reply..
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900