question

SajidDewan-8221 avatar image
0 Votes"
SajidDewan-8221 asked MelissaMa-msft commented

I need your help to resolve this SQL Server 2008 query

SELECT dbo.MainLedger.AcCode, SUM(dbo.MainLedger.Debit) AS DurDr, SUM(dbo.MainLedger.Credit) AS DurCr
FROM dbo.InputVal CROSS JOIN
dbo.MainLedger

where
Case when Convert(datetime,DateFrm)>convert(datetime,[dateto]) then MainLedger.Transdate between convert(datetime,datefrm) and convert(datetime,[dateto])
else
Transdate Between convert(datetime,[YearFrom]) and convert(datetime,[datefrm])
end



GROUP BY dbo.MainLedger.AcCode

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SajidDewan-8221,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables(dbo.InputVal and dbo.MainLedger) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample and the error message you faced.

Best regards,
Melissa

0 Votes 0 ·

Thanks for your quick reply.

I have complete customer Sales in MainLedger
My Frontend user request to get the Sum of Every Item based on the date range
IUser enter date range in The InputVal table from frontend;

This query is working fine with Access but when we convert to SQL Server its executing with error:

Access Query that working:
SELECT MainLedger.AcCode, Sum(MainLedger.Debit) AS DurDr, Sum(MainLedger.Credit) AS DurCr
FROM InputVal, MainLedger
WHERE (((IIf(CDate([dateto])>=CDate([datefrm]) And CDate([Datefrm])>CDate([Yearfrom]),CDate([transdate]) Between CDate([Datefrm]) And CDate([dateto]),CDate([transdate]) Between DateAdd('d',1,CDate([Yearfrom])) And CDate([dateto])))<>False))
GROUP BY MainLedger.AcCode;


0 Votes 0 ·

106899-image.png


106962-image.png




SELECT dbo.MainLedger.AcCode, SUM(dbo.MainLedger.Debit) AS DurDr, SUM(dbo.MainLedger.Credit) AS DurCr
FROM dbo.InputVal CROSS JOIN
dbo.MainLedger

where

CASE WHEN
Convert(datetime,DateFrm)>convert(datetime,[dateto])
THEN
TransDate Between convert(datetime,datefrm) and convert(datetime,[dateto])
ELSE
TransDate Between convert(datetime,[YearFrom]) and convert(datetime,[datefrm])
END


GROUP BY dbo.MainLedger.AcCode

0 Votes 0 ·
image.png (115.0 KiB)
image.png (120.9 KiB)
SajidDewan-8221 avatar image
0 Votes"
SajidDewan-8221 answered MelissaMa-msft commented

After 48 hours brainstorming I got the accurate Result by the following query:

SELECT dbo.MainLedger.AcCode, SUM(dbo.MainLedger.Debit) AS DurDr, SUM(dbo.MainLedger.Credit) AS DurCr
FROM dbo.InputVal CROSS JOIN
dbo.MainLedger
WHERE (CONVERT(datetime, dbo.MainLedger.TransDate) BETWEEN CONVERT(datetime, dbo.InputVal.DateFrm) AND CONVERT(datetime, dbo.InputVal.DateTo)) AND (CONVERT(datetime,
dbo.InputVal.DateFrm) > CONVERT(datetime, dbo.InputVal.YearFrom)) AND (CONVERT(datetime, dbo.InputVal.DateTo) >= CONVERT(datetime, dbo.InputVal.DateFrm))
GROUP BY dbo.MainLedger.AcCode
ORDER BY dbo.MainLedger.AcCode


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SajidDewan-8221,

Thanks for your update and glad that you already found your solution.

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.

This permits us to copy and paste into a query window to develop a tested query and then we could find a solution instead of guessing your table based on your query provided.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @SajidDewan-8221,

Please have a try with below:

 SELECT dbo.MainLedger.AcCode, SUM(dbo.MainLedger.Debit) AS DurDr, SUM(dbo.MainLedger.Credit) AS DurCr
 FROM dbo.InputVal CROSS JOIN
 dbo.MainLedger
 where
 (Convert(datetime,DateFrm)>convert(datetime,[dateto]) and TransDate Between convert(datetime,datefrm) and convert(datetime,[dateto]))
 or
 (Convert(datetime,DateFrm)<=convert(datetime,[dateto]) and TransDate Between convert(datetime,[YearFrom]) and convert(datetime,[datefrm]))
 GROUP BY dbo.MainLedger.AcCode

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SajidDewan-8221,

I found that you unaccepted this answer.

Could you please let me know whether there is any other issue you face?


Best regards,
Melissa

0 Votes 0 ·
SajidDewan-8221 avatar image
0 Votes"
SajidDewan-8221 answered ErlandSommarskog commented

Query is executing but Results are not accurate...

I request you to please make some possibilities using the CASE statement

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For this type of question, we strongly recommend that you include CREATE TABLE scripts for your table(s), and INSERT statements with sample data, enough to illustrate all angles. We also need to know the expected result given the sample.

If you don't provide this information, people can only post more or less advanced guesses of what you are looking for, but there is no way people can test their solutions.

0 Votes 0 ·

Query is executing but Results are not accurate...

I request you to please make some possibilities using the CASE statement

SELECT MainLedger.AcCode, Sum(MainLedger.Debit) AS DurDr, Sum(MainLedger.Credit) AS DurCr
FROM InputVal, MainLedger
WHERE (((IIf(CDate([dateto])>=CDate([datefrm]) And CDate([Datefrm])>CDate([Yearfrom]),CDate([transdate]) Between CDate([Datefrm]) And CDate([dateto]),CDate([transdate]) Between DateAdd('d',1,CDate([Yearfrom])) And CDate([dateto])))<>False))
GROUP BY MainLedger.AcCode;


IYear from and year to is fixed so user enter data within this limit....
thats the reason I have to compare that IF The Dates Entered by User in INPUTVAL table is > Year Starting From
Then get Data (Between Date from and Date To) that's it




0 Votes 0 ·

II CANNT CREATE TABLE AS MY 20 USER CONNECT TO THIS DATABASE SO FOR EVERY USER I HAVE TO CREATE TABLE. THIS IS NOT A SOLUTION AT ALL.

I REQUEST YOU TO PLEASE LET ME KNOW HOW TO USE CASE STATEMENT WITHIN THE WHERE CLAUSE THATS IT AND I WILL DO THE REST

0 Votes 0 ·
Show more comments